|Moving a SQL Server
when Maximum Uptime Matters.
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
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
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
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
select cast('exec sp_addlogin ''' + cast(name as varchar(20))
+ ''',''password'',''' + cast(db_name(dbid) as varchar(20))
+ ''',''' + language + ''',' as varchar(75))
where name is not null
and name <> 'sa'
and password is not null
select 'exec sp_grantlogin [' + rtrim(ltrim(name)) + ']
exec sp_defaultdb [' + rtrim(ltrim(name)) + '], [' + rtrim(ltrim(db_name(dbid))) + ']'
where name is not null
and name <> 'sa'
and password is null
print 'logins added, set passowrd for all SQL Logins now.'
and then adding the password to all SQL Logins from a script I
keep in a safe place. Then you can add logins on the new server
to the appropriate server roles. A quick check of
sp_help_user will help you verify that you have all the
users defined. Try the script:
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
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.