SQL Server Migration (aka The Big Pain)

Almost everyone has had the pleasure of moving a SQL instance from the old server to a new server. Whatever the reason for it (lease replacement, hardware upgrade, consolidation) it is never fun.


The last two places that I have worked, have always renamed the new physical server so that the SQL Server name had to change as well. Our SQL Server naming rules went something like this...

• Location - Purpose - Client - Environment - Numeric ID

So the servers were named...

• STL_SQL_PROD_ABCInc_001

and the replacement server would always be...

• STL_SQL_PROD_ABCInc_095 or some such.

So moving the SQL instance was always a headache. But I did discover some tricks to make it easier. Here are some things that I have found very useful.


1. Use sp_help_revlogins. This is a stored procedure you can find on the Microsoft website or on the web (there is a 2000 and a 2005 version) that will script out all of the users and passwords (encrypted of course) so that you can move them to the new server. The nice thing is that it keeps the original SID so that when you restore your databases from old to new, everything will match up and you won't have any orphaned users.


2. If you are moving a SQL 2000 instance, you might have to move a bunch of DTS packages as well. One instance I migrated had over 1,000 packages! If they are saved at the OS File level, not a big deal, but if they are stored in the MSDB database, it can be a real hassle. You don't want to backup / restore the msdb because who knows what that will break, and there is no good way to script out the packages without messing them up (especially the comments) as far as I know. So here is what I found.


A) Make sure the SIDs for any standard users match on both servers. You can use a stored procedure called sp_help_revlogin (see above) to recreate the user, sid, and password on the new server.


B) Create a linked server from the old server to the new server. Set the security context to be "use login's security context". That way you are yourself (and SA) on both servers.

C) Run this query.
INSERT LinkedServerName.msdb.dbo.sysdtspackages
SELECT * FROM msdb.dbo.sysdtspackages order by name,createdate

The packages are stored in an image column so it copies it over exactly. But it might take a bit. It took me about 10 minutes to copy 189 packages, the last time I did this.


3. If you have to copy file shares yourself (hopefully you storage guys will do it for you but lots of times I have had to do it myself), use RoboCopy. RoboCopy is normally part of the server build and should be on the server already. If not, XCopy (yes the old DOS command) can be used. Both of these have a bunch of flags that can be set so you can copy over the permissions for file shares as well as the data.

There is nothing I enjoy more then having to add 50 users to a share folder by typing each one in. Maybe there is a way to copy and paste or some script that you can write, but I have not found it yet.

Since I have found RoboCopy, I normally do a full copy with permission settings from Server Old to Server New and then set up a bat file just to copy new or changed data after that and run it a couple of times a day. Then when you are ready to do your cut over, it should only take a few minutes to copy the last bit of new and or changed data and your done.


4. I have found it to be normal that these cut overs usually take place about 3 am on Sat or Sun morning. So the last thing I want to do is sit around and try to figure out why the application can't connect to the SQL server. So, I make sure that I strongly "suggest" to the developers that they check all the connectivity to the new server and that they go through and update all their stored procedures and DTS packages that might reference the old server name. And that they test each stored procedure and DTS package before the switch. This makes sure their stuff is working and checks my setup as well.

This means more work for you and maybe a few irked emails, but I have found it to be worth it. I would rather have someone complain that I am bugging them every day than have to spend hours rolling back a failed migration and then explaining that failure to my boss and his boss too. I have done that and it is not fun.

Is it really your job as a DBA to ask the developers to test on the new server? No, not really, at least not in most places. Is it worth it if no one else is stepping up to do it? Yes, I think so.


Well, there are some of my helpful hints on how to manage a SQL Server Migration. I am sure that other people have great tips and ways of doing this and I would love to hear them.

Leave a comment or send me an email with how you handle it.

Thanks!!