This question seems to come up from time to time, so I decided to write a blog post on it. Yeah, I know there are other blog posts on the process, but none of them have the finish or flair of a Todd Klindt blog post 
so here we go.
The reasons for doing this are many, but the problem boils down to this, “How do I move the databases SharePoint uses without seriously pissing it off?” This could be because you have SharePoint and SQL on the same machine and you need to split SQL out. It could be because your current SQL server is underpowered and you want to move it to a more capable server. Or, it could be because your existing SQL server is 32 bit, and you need a 64 bit instance for SharePoint 2010, and probably an upgrade to a newer version too. At the core these are all the same tasks. Now, how hard is it?
Turns out, if you have the right tools it’s pretty simple. So simple even I can do it. Here are the basic steps to move from SQL A to SQL B:
- Stop any processes that might be accessing your SharePoint databases
- Back up all your SharePoint databases on SQL A.
- Restore them on SQL B.
- Set up a SQL alias on SharePoint that points SQL A to SQL B.
In a nutshell that’s it. Of course there’s some devil in the details and that’s what I hope to cover in this blog post. I’ll walk you through the process I used to accomplish this exact feat. To demonstrate how powerful this is I tried to make the two environments as different as possible. Hopefully this will show how flexible this all is. The source box, Dusty, is 32 bit SQL 2005 Express, running on 32 bit Windows 2008. It is on the same box as MOSS 2007. It is using the named instance dusty\sqlexpress. All SharePoint and SQL services are running as the domain administrator. I built it this way to mimic how a lot of SharePoint installations start. This is the classic, “This is only a test environment, it will never be used in production” environment that finds its way into production. Under production loads it just can’t perform. The first step is to split SQL out. That’s where Shiny comes in.
Shiny is running 64 bit SQL 2008 R2 Enterprise on Windows 2008 R2. The SQL services are running as contoso\sqluser. This box will be dedicated to SharePoint, so SQL is installed on the default instance. The differences between Dusty and Shiny are the architecture, the SQL version, the Windows version, the service account and the instance name. This picture shows the “before” picture in SQL Server Management Studio (SSMS) on Dusty:
In SharePoint we can verify that the default database instance is Dusty\sqlexpress:
The first step is to stop any and all processes that might be accessing the SharePoint databases in SQL. If SQL is NOT running on one of the SharePoint servers the best thing to do is just shut them all off. That way you know nothing is touching them. If that’s not an option (as it wasn’t in the case of Dusty) then you can shut down all the SharePoint services. Here’s the commands I used:
Net stop w3svc
net stop sptimerv3
net stop spadmin
net stop sptrace
net stop osearch
It looks like this:
Once that’s done, the next stop was to back up all the SharePoint databases on Dusty. In the process I stumbled onto a handy tip on how to change the default backup location in SQL server.
After the backups were completed I stopped the SQL service. That way if I screwed something up with the alias later I could be certain I wasn’t hitting the old databases by mistake.
After I had the database backups I copied them over to Shiny and restored them all with the same names. Since I wasn’t’ changing any service accounts in SharePoint I didn’t have to mess with any of the database permissions in SQL. I did have to verify that Shiny was exposing SQL on TCP/IP and verify the firewall on Shiny was open on port 1433 so that the SharePoint services on Dusty could get to the SQL database engine on Shiny. Make sure you check both of those before you continue.
The next step is to tell SharePoint the good news, that it has a new SQL server. We do that with a SQL alias. Think of a SQL alias as a HOSTS file for SQL connections. It is something you configure on a SQL client to point it to a different SQL server than it is configured to. It is 100% client side. There is nothing to do on the SQL server. I repeat, none of this happens on the SQL server. You create the SQL alias with the command cliconfg.exe. Cliconfg.exe comes with Windows, so there’s nothing to install. Just type cliconfg.exe in Start > Run.
When the utility comes up click the Alias tab. Then click “TCP/IP.” In the “Server Alias” field fill in the connection string the SQL client uses. In our case that’s “dusty\sqlexpress.” In the Server Name field fill in the new connection string. In our case that’s “shiny.” If you have multiple SharePoint servers, you’ll need to create the SQL alias on each and every one of them.
We can see that the instances don’t need to match, so we can also use an alias to connect to a named instance without all the hassle of using the named instance. For instance, imagine some meanie DBAs give you a SQL instance of “sqlserver\sharepointinstance” That’s a lot of typing, and some things *couch* User Profile Service *cough* don’t handle named instances very well. Wielding the power of SQL aliases we could create an alias called “dbasstink” for SharePoint to use and point it at “sqlserver\sharepointinstance”. You can also use aliases to handle SQL instances that run on non-standard ports. Instead of making SharePoint deal with an instance like “sqlserver\sharepointinstance:1026” we just point it at an alias that has the port hard coded. They’re flexible and powerful. They’re worth some investigation.
After you have the alias set then it’s safe to fire SharePoint back up. If it’s convenient to reboot them, you can. If not you can manually start all the services we stopped earlier. For my demo here’s the commands I used:
net start w3svc
net start sptimerv3
net start spadmin
net start sptrace
net start osearch
Here’s what it looked like:
For good measure I also ran an IISRESET. I’m a member of Microsoft’s frequent IISRESETer program and I get cash back for each IISRESET I perform.
That’s all there is to it. To prove it all worked here is a screenshot of http://dusty working with the SQL instance DUSTY\SQLEXPRESS stopped.
Now that you know how to use a SQL alias, let’s talk a bit about some considerations. First, while I like SQL aliases they can be tough to keep track of. For instance, if you set up a SQL alias and someone else is trying to troubleshoot SharePoint until they know about SQL aliases they are going to be very confused when they find that the SQL instance DUSTY\SQLEXPRESS is either shut down or flat out doesn’t exist. To leave the other people a clue to the real location you could dismount your content databases from DUSTY\SQLEXPRESS and mount them from SHINY. Then if people start looking around for where databases are they’ll eventually know to look on SHINY for them.
Second, you’ll notice I did all this with SharePoint 2007. Why not SharePoint 2010, you might ask? I did it mainly so that I could mention that instead of hooking SharePoint 2007 back up after the databases were moved we could have also attached them to a SharePoint 2010 farm as part of a database attach migration or in-place upgrade. You would need this if your SharePoint 2007 databases were on an unsupported SQL platform. Once you get your databases on a SharePoint 2010 approved SQL instance you have a lot of options.
I hope this takes some of the mystery out of SQL aliases.
tk