Every SharePoint admin is likely to do one thing at least one time in their career: Move databases to a new SQL Server or instance.
I was lucky enough to get to do it again, most recently, overnight tonight for a customer, so I thought I’d share a few pointers in this week’s SharePoint Pro Update!
I’m actually at my home office on Maui, Hawaii for a whopping six weeks. It’s the first time ever that I’ve been home for five or six weeks straight, and the first time since 2009 that I’ve been home for four weeks. So life is pretty good! Except for those time differences—now that I’m splitting my efforts across the Americas, Europe, and Australia I get to work just about 24 hours a day!
Tonight was a late night moving databases for a customer. Microsoft details the process for moving databases in a TechNet article. Unfortunately, the article tries to cover too many scenarios and has a few errors.
So I’m going to focus on the steps that will help most of us, most of the time.
If you have a particularly complex or fringe configuration, you might need to read the details in the article, but if there are contradictions between the article and what I write here, trust me, because I tested my guidance and they clearly didn’t.
1. Document database security configuration.
You’ll want to know which of your admin and service accounts have logins and permissions to the existing, source SQL Server instance and the databases. You’ll find that your administrative account, the farm account, and your application pool accounts have logins for the SQL server.
Some of these accounts will have server/instance permissions; all of them will have database permissions.
Take note of the database owner (DBO), particularly, of each of your databases. Also document what users exist in the Security \ Logins folder for each database. While it is good practice to document all schemas and role assignments, details beyond the DBO and list of users aren't necessary for this task.
Also note the users in the Security \ Logins folder for the SQL Server instance. Make note of the Server Roles assigned to the accounts.
2. Prepare target SQL server.
Obviously you’ll need your target SQL server (or instance) installed and configured. It should use the same version and patch level of SQL Server as the source SQL server. I’m assuming you are an administrator of both the source and target SQL Server instances.
Make sure you have logins created for admin and service accounts on the target server that match the source server, including Server Role assignments. User Mappings will “come over” with the databases.
3. Prepare the SharePoint servers.
On each SharePoint server, you need to ensure that there is nothing that's connected to the farm’s databases. The easiest way to do this is to shut the servers down completely.
If you can’t do that, you must close any open Windows PowerShell (or SharePoint Management Shell) consoles and Command Prompt windows. And you must shut down all SharePoint-related services.
As you do this step, document which services were actually started and which ones weren’t, so that you restart the correct services later. I’ve included a list of the services for each SharePoint version, using the service name, so you can use the NET STOP command or PowerShell’s Stop-Service cmdlet:
SharePoint 2010: W3SVC, SPSearch4, OSearch14, SPWriterV4, SPUserCodeV4, SPTraceV4, SPTimerV4, SPAdminV4, FIMSynchronizationService, FIMService, DCLoadBalancer14, DCLauncher14'
SharePoint 2013: W3SVC, SPSearchHostController, OSearch15, SPWriterV4, SPUserCodeV4, SPTraceV4, SPTimerV4, SPAdminV4, FIMSynchronizationService, FIMService, DCLoadBalancer15, DCLauncher15
Then, open a command prompt with Run As Administrator, and enter the command
IISRESET /STOP
4. Detach databases on the source SQL server.
In SQL Server Management Studio (SSMS), right-click each SharePoint database, point to Tasks, then click Detach. In the dialog that appears, click OK.
5. Move databases to the target SQL server.
Move databases to the target SQL Server. You’ll need both the .MDF database and .LDF log files.
6. Attach databases to the target SQL server.
In SSMS, right-click the server or instance, then click Attach. In the dialog box, click Add, then select one database. Click OK to finish attaching the database. Repeat for all databases.
7. Reassign the DBO of the databases.
When you attach databases to the target SQL server, your user account becomes the DBO of the databases. You should assign the dbo that was documented on the source SQL instance, which will be your Farm account in most or all cases. To do this, right-click the database,then click New Query. Paste the following query:
sp_dropuser "domain\username" go sp_changedbowner "domain\username" go
then click Execute. The first command deletes the login for the user if it already exists for the database (ignore any error if it does not).
Without this step, the second step would generate an error. The second command changes the DBO property of the database.
8. Point SharePoint servers to the target SQL Server or instance.
You will now use a SQL alias on each SharePoint server to redirect the connections to the original, source SQL server to the new, target SQL server.
This is the step that the TechNet article gets completely wrong. They clearly tested this not on any real farm, but on a single-server farm with SQL Server running on the server. Bad testing!
The correct way to create or modify your SQL Server alias is as follows:
Click Start. Then click Run. Type CLICONFG.EXE, then press RETURN.
On the ALIAS tab, click EDIT to modify your original alias, if you had one, or click ADD to add a new alias, if you didn't have one before.
• If this is a new alias, enter the original, source SQL Server’s name in the SERVER ALIAS box. If you are modifying an existing alias, don’t make any change to the SERVER ALIAS box.
• In the SERVER NAME box, enter the name of the new, target SQL Server.
• In the NETWORK LIBRARIES box, select TCP/IP.
Remember to repeat this on each and every SharePoint server.
9. Restart SharePoint services.
Now, restart the SharePoint services on each SharePoint server.
The easiest way to do this is to start the server, if it’s shut down, or to restart the server, if you hadn't shut it down previously.
If you can’t do that, open a command prompt with Run As Administrator, then enter the command
IISRESET /START
Then start each service that had been started, in the reverse order listed above in Step 3.
Test to make sure everything’s working, and voilà!
