Skip Ribbon Commands
Skip to main content

Quick Launch

Todd Klindt's home page > Todd Klindt's SharePoint Admin Blog > Posts > Moving SharePoint to a different SQL server
February 15
Moving SharePoint to a different SQL server

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 Smile 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:

  1. Stop any processes that might be accessing your SharePoint databases
  2. Back up all your SharePoint databases on SQL A.
  3. Restore them on SQL B.
  4. 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:

2-11-2011 8-41-16 PM

In SharePoint we can verify that the default database instance is Dusty\sqlexpress:

2-11-2011 8-44-43 PM

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:

2-13-2011 8-18-59 PM

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.

2-13-2011 9-43-53 PM

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.

2-13-2011 9-45-05 PM

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:

2-13-2011 9-48-21 PM

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.

2-13-2011 9-54-15 PM

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

Comments

Moving databases from 2010 to 2010

I had to do this recently on a SharePoint 2010 farm to another 2010 farm and I found this TechNet article really useful:

Move all databases (SharePoint Foundation 2010)
http://technet.microsoft.com/en-us/library/cc512723.aspx

Its essentially the same steps you outline here but has some good links to SQL queries that may need to be run :)
 on 2/16/2011 1:16 AM

Re: Moving databases from 2010 to 2010

It's just never enough for some people. :)

That article isn't bad but I don't like a couple things in it. First, they have you move the DB files instead of use a backup. The DBAs I've talked to frown on manaully attaching MDFs and LDFs unless it's absolutely necessary. The backup also has some security to it in case something goes wrong. Second, that article erroneously says you need to install the SQL tools to configure an alias. That is just not true. It's built in to the modern OSes and has been for years.

Other than that, it's great. :)

tk
Todd O. KlindtNo presence information on 2/16/2011 10:26 AM

Moving databases is tricky

I tried to do this just last week in my dev environment. The results where disastrous. The DB files came over just fine, but when it came time to attach them to the farm, the content database would constantly throw a 404 error. nothing i did could fix it. there might have been a corrupt webpart in it somewhere but I could not recover.
The only thing that worked for me was to take a backup of the PRODUCTION sitecollection and restore it to the dev environment.
 on 2/16/2011 11:47 AM

Why not use Active Directory "C Name Alias"?

Todd,
Why not use Active Directory "C Name Alias" for your main build of your farm for your SQL server? This way SharePoint never cares where your DB's are? Move SQL and then just change the IP of your C Name Alias? Is there something wrong with this approach?
 on 2/16/2011 5:08 PM

Database permissions

Nice article Mr. Klindt. Perhaps as a follow up in the podcast or for another post, some additional information about also having to change service accounts/permissions would be handy.

Michhes
 on 2/16/2011 7:22 PM

Preparetomove?

Did you run the stsadm -o preparetomove after you shut down SharePoint? Or is that not necessary in this case?
 on 2/17/2011 9:54 AM

CNAME aliases

As for the person asking about CNAME aliases - that really only works if you start out with SharePoint on one box, and SQL on the other, which is where I'm starting. I already have SharePoint set up to use 'sharepointsql' as the database server name, though that is not the real SQL host name.

When I move the databases, I really don't have to do anything but change the DNS alias (cname) record, then log in to the SharePoint servers with the farm admin account and run ipconfig /flushdns to make sure the system picks up that change before starting SharePoint.

In Todd's example here he starts with SharePoint and SQL on the same box, so changing a DNS alias would be fine for SQL, but not so great for SharePoint.

Greg Walrath
 on 2/17/2011 9:57 AM

Re: Why not use Active Directory "C Name Alias"?

Good question. First, I would never, ever, not even on a dare, let a CNAME within 10 foot of my SharePoint or SQL machines. For one thing it breaks Kerberos to pieces. Though let's pretend you said "A record" instead. :) Here are the reasons aliases are better:
1) SQL aliases handle named instances. I cannot create an A record that points to dusty\sqlexpress, only one that points to dusty. If I would to mask an instance name The A record won't work.
2) SQL aliases handle ports. If my SQL instance is using a non standard port I can hide that from SharePoint with a SQL alias. I can't point an A record at 192.168.1.1:4655, only at 192.168.1.1.
3) SQL aliases only pertain to SQL. In my example above dusty is the SharePoint server and the SQL server. There's no way I can point dusty to the new SQL server without affecting how the end users resolve dusty or impacting how SharePoint resolves dusty when it tries to do a crawl of http://dusty.
4) SQL aliases are client side. If I have both SharePoint and PeopleSoft databases on one SQL server and I decide to split them up, how do I change DNS to reflect that? Both SharePoint and PeopleSoft will get the same A record and always go to the same SQL server. With SQL aliases I can set one alias on the SharePoint boxes and a different (or not) alias on the PeopleSoft servers, allowing them to access different SQL servers.

Hope that helps.
tk
Todd O. KlindtNo presence information on 2/17/2011 10:32 AM

Re: Database permissions

Michhes,
Thanks. The permissions discussion is a good one. I'll give it some attention.

tk
Todd O. KlindtNo presence information on 2/17/2011 10:32 AM

Re: Preparetomove?

I did not run preparetomove. That is really only for moving between SharePoint farms, and we didn't in this example. Plus I'm mostly sure Preparetomove isn't necessary after SharePoint 2007 SP, but I'm not positive.

tk
Todd O. KlindtNo presence information on 2/17/2011 10:34 AM
1 - 10Next

Add Comment

Items on this list require content approval. Your submission will not appear in public views until approved by someone with proper rights. More information on content approval.

Title


Body *


Today's date *

Select a date from the calendar.
Please enter today's date so I know you are a real person

Twitter


Want a message when I reply to your comment? Put your Twitter handle here.

Attachments

 

 Please Support my Sponsors