Situation
So it's come time to retire your database server. Maybe the OS it is on is no longer supported, or maybe you just need to get it off some obsolete hardware. Unfortunately your Sharepoint database is still hosted on the old server, so you obviously need to move it off before you can proceed with retiring it.
Okay, so time to fire up Google and search for a way to move the database over. You come up lucky and find a whole bunch of results, but they all seem to just be variants on this TechNet article for Sharepoint 2010 or this one for Sharepoint 2013. Reading through it it seems to all be pretty straight forward until you come to the step labelled "To point the Web application to the new database server by setting up SQL Server connection aliases".
Now if you or your predecessor followed the Microsoft recommended install this isn't a problem, just fire up the SQL network client, change the alias to the new server and your done. But what if your install wasn't done with SQL aliases? Well you could just create a new alias from your old SQL server to the new one. But maybe you don't want references to a retired server hanging around, or maybe (as was the case I ran into) you just can't seem to get the alias to work for you, what do you do then? According to most information you will find it looks like you can't, but it turns out that will a bit of fiddling there is a way to get Sharepoint to talk to a new SQL server that you've copied the DB to.
Solution
Step 1: The very first thing you want to do before you start any steps in your migration is make sure you know your Farm Passphrase for Sharepoint. If you have lost this or just need to reset it anyways you can do that with the Sharepoint PowerShell interface, on the server hosting Sharepoint, using these commands:
- $passphrase = ConvertTo-SecureString -asPlainText -Force (This is prompt you to enter a new passphrase to use
- Set-SPPassPhrase -PassPhrase $passphrase -Confirm (This will reset the passphrase for Sharepoint for you)
Step 2: Make sure you have no pending updates to your Sharepoint databases. This will block you later and you'll be forced to roll back to the old server to do the updates.
Step 3: Now you follow the steps described in the TechNet articles on the Sharepoint migration all the way up to, and including, attaching the databases on the new SQL server.
Step 4: Here's where things start to deviate from the recommended route. You are going to need to go into the Sharepoint DB on the new SQL server. In the Configuration database, under the Objects table there will be an entry where the Name field contains the name\instance of your old SQL server. You will need to change this to be the name\instance of the SQL server you have moved the DB to. A quick Select query should help you find it. It seems that both Netbios and FQDN names are acceptable here, depending on your network/server setup.
Step 5: Now open up the Sharepoint PowerShell interface again. What you are going to do here is tell your Sharepoint server that you are disconnecting if from it's existing farm and connecting it to a new one with an existing configuration database. The commands you need are:
- Disconnect-SPConfigurationDatabase (This will seperate your Sharepoint server from its existing farm, and it does not need to have access to the old database for this)
- Connect-SPConfigurationDatabase - DatabaseServer "ServerName\InstanceName" -DatabasenName "SharepointConfigDatabase" (Replace the server and DB names and appropriate, This should prompt you for the Farm Passphrase from before and will connect your Sharepoint to the new SQL server)
If you get a generic "Cannot Connect to Database" error, check the SQL logs as it is likely an error is the security settings on the databases.
Step 6: Restart Sharepoint services and test that everything is working.
Wrap-Up
Hopefully this helps somebody out there who is banging their head against a wall trying to move a Sharepoint DB, I know there was quite a bit of head banging between me and my colleague trying to figure this out.
References