By Bill Wunder

I had another wild Saturday night glued to the PC in my basement this weekend. And no I’m not talking about a chat room or foray into the world of illicit web sites. I had to move the busiest SQL Server in the shop to new hardware. Our shop is a 24x7 operation and we get a whopping 2 hour maintenance window per month to do all the things necessary to pull off all the things necessary to keep a multitude of high volume web sites up and running. Now 2 hours sounds like a lot, but you have to keep in mind that we’re not actually supposed to be down even during the maintenance window.

We pulled it off with the server being unavailable to the web for less than 15 minutes! Not too shabby considering we actually had to migrate the server name and IP address to the new hardware in order to minimize the effects of the server change across our myriad of applications – some AS from the last century and still using Db-Lib to get to the database. Fact is the broad spectrum of applications we use is the reason we have to use such drastic measures as a complete DNS and domain swap out when replacing this server.

In this article I’d like to describe how we made the change. Here we go again, you may be thinking, another this-is-the-way-I-did-it narrative. And I understand your concern. Usually that sort of article is something less than useful. You get a check list that is highly specific to the author’s environment and, if you’re lucky, a few tips that you might be able to translate into something useful for your environment. Well, I intend to break that mold. I won’t give you a check list that has no meaning in your environment when the day comes to change the hardware under a critical SQL Server. Instead I want to give you a general planning guide that will help you think about the full spectrum of issues you want to consider during a hardware migration and offer up a few scripts that are useful to move any SQL Server to new hardware.

Generally speaking there are four areas of consideration when migrating to new hardware: hardware, operating system, SQL Server, and application requirements. I don’t want to give the false impression that these areas are steps in a migration process. You must think about and plan for all four areas before you begin the hardware migration. The first two really are quite specific to your needs so it is difficult to get into details. It is possible to make a few general statements. Make sure you are working with adequate and proper hardware. If you don’t no amount of additional planning will bring you success. In most cases, a main reason for a hardware migration is to upgrade currently inadequate hardware. Getting the right new hardware ought to be an obvious requirement.

Second, you must have a good installation of the correct operating system. I suggest that you - as the DBA - work closely with your hardware folks to meet these first two fundamental migration objectives. Make sure that the operation system for all your SQL Servers are installed using a documented build script. And make sure it is corrected as necessary and always kept up to date. Once the proper operating system is installed for the version of SQL Server you will be installing, duplicate the domain permissions of the current box and recreated the logical file system of the current box or at least have a sound plan for moving from the current logical drive configuration to an intentionally selected new configuration. For direct attached storage, drive configuration should match the RAID configuration of the existing installation or at a minimum meet the RAID configuration outlined in the next step. Be sure to see the SQL Server Books Online “Setting up Windows Services Accounts” and add the correct domain accounts to achieve a desired level of rights and permissions for the SQL Server and SQL Agent service accounts at this time. Also assure that any network protocols necessary are installed on the server. In my case I had to get NETBEUI installed this weekend because I have an old 16 bit application that still needs to connect to the SQL Server. Note that I would not have been able to make the right decision here if application requirements were not considered simultaneously to operation system needs. Consider running the SQLIOStress tool to verify the basic hardware and OS installation at this point. This is a good way to build confidence that your new hardware is ready for the production environment. Review and reconcile all errors and warning in the System and Security Event Logs before moving on to the third area: installing SQL Server.

The SQL Server installation should be straight forward. The biggest trick in getting the bits from CD to server will be selecting the correct logical drives for the program files and the data files. After the install you may want to move the tempdb, msdb and possibly master database transaction log files from the default (Data) location to a properly configured logical drive where all application database transaction logs will also be placed. Moving transaction log files is explained nicely in the KB article, “Moving SQL Server databases to a new location with Detach/Attach”. The transaction logs on direct attached storage will perform best placed on a RAID 1 - RAID 0+1 or RAID 1+0 are variations on RAID 1.The data devices should be place on a RAID 5 for satisfactory redundancy and performance, or the possibly on a RAID 1 variation if performance considerations justify the additional costs. Another possible affordable performance gain may be realized by placing the indexes on their own file groups on RAID sets isolated from the data and log devices. Match the index filegroup’s RAID level to the data filegroups. It’s possible that you have developed an even more refined configuration so don’t make the mistake of using these or any other recommendations unless you are certain the result will be better than your current configuration.

Service packs and all post service pack patches on the original SQL Server must also be installed along with the database.

Once the SQL Server is fully installed you are ready to configure the SQL Server for your application. I suggest as a next step to restore the most recent backup of each user database to the new server at this time. Once all the restores are complete you can migrate the logins using DTS or my preferred method of scripting the logins

set nocount on
--exec sp_addlogin 'joeuser', '', 'tempdb', 'us_english', 0x8BB4145DA75DED43BF4E96440AA3438C

--sql logins
select cast('exec sp_addlogin ''' + cast(name as varchar(20)) 
 + ''',''password'',''' + cast(db_name(dbid) as varchar(20)) 
 + ''',''' + language + ''',' as varchar(75))
 , sid 
from sysxlogins 
where name is not null
and name <> 'sa'
and password is not null

--windows logins
select 'exec sp_grantlogin [' + rtrim(ltrim(name)) + ']
	exec sp_defaultdb [' + rtrim(ltrim(name)) + '], [' + rtrim(ltrim(db_name(dbid))) + ']' 
from sysxlogins 
where name is not null
and name <> 'sa'
and password is null

print 'logins added, set passowrd for all SQL Logins now.'

     sp_msforeachdb 'use ? select db_name() exec sp_helpuser'

Scan the output of this query for the word NULL. If you find any NULLS you’ll probably have identified a user that has no login with a matching SID. Correct them as necessary.

Now script all the jobs from the current server and create them on the new server. You’ll also want to add all linked servers used on the old hardware to the new hardware.

At this point the new hardware should be fully configured for your application. If the application is third party you may need to run the installation or setup utility for the application now. If it’s an in house application all that’s left is to determine a how you will make sure the most current data is at the new server at the moment you fail over to the new hardware and how you will get your application to start using the new server.

If you have a large enough window of time when the data is not changing it’s possible that a simple backup and restore is good to get the data in sync: the simpler the sync process the better. In my case this weekend I had data changing at a pretty good clip right up to the time of fail over. I chose to use Heavy Duty SQL Litespeed Log Shipping to keep the data accurate right up to the moment of failover. As soon as I shipped the last log I took each database offline to prevent any application from changing the data. Once all user databases had that last log shipped I stopped the SQL Server on the old server, renamed it, gave it a new IP address, gave the new server it’s name and IP and away we went.

The rename and re-IP is a bit drastic. If you can avoid it, do. We went with the rename because we have several applications hitting this server that use a DB-LIB connection with the connection information stored in the registry and in a few cases hard wired into the application code. For us it made sense to rename and re-IP the SQL Server. For your there may be a better way. As I said, this is intended to give you a good starting point for your hardware migration plan. I hope you find it useful.

Bill