During the past six months or so I have had to manage quite a lot of SQL Server migration/consolidation, and with each effort I’ve refined a checklist to try to make things go more smoothly. I just hate getting that call after a migration where something unexpected is broken :-). What follows is imperfect, but I thought I’d throw it up here anyway, if only as a starting point for other people.
Some basic assumptions: I have been moving SQL Server 2005 Standard Edition and SQL Server 2000 data, so some of the more advanced functions of current versions aren’t available to me (async mirroring, especially) so some of what follows is pretty “old school.” There are better ways to do some of this if you are on 2005+ Enterprise. Encryption and keys are outside the scope of this, as I don’t current have to work with those features and consequently don’t know much about them. Lastly, this process assumes you can’t migrate using shared storage, for example with a SAN.
That said, the theme here is: more preparation = less perspiration.
Way Way Ahead of Time
First, of course, you need a new server if you plan to migrate something. You should, absolutely, build the new server with a good build checklist, and you need to know with certainty that it’ll perform the way you need it to. The only safe way to get to that point is by collecting performance counter data for a decent period of time against the existing server(s), including especially the disk performance counters. I won’t beat that dead horse – let’s assume you have a nice, fancy new box and confidence that it’ll work. File under “Duh.”
First migration question, then: are the security/surface area configuration settings on the new server/instance compatible (xp_cmdshell, clr, etc.) with the existing server? Is there any chance that when you bring the data over the security configuration at the server level will break some function of one of the databases? On the other hand, can you safely take advantage of the migration opportunity to tighten security, fix those bad behaving, SA account-using, old time xp_cmdshell-abusing problem apps?
Verify that all the instance- and server-level settings are compatible with your old instance. In 2005, this is mainly the Surface Area Configuration Tool, in 2008 the same settings are present, but in the SQL Server Configuration Manager. Carefully investigate whether it’s safe to tighten security by disabling dangerous features like xp_cmdshell in your environment.
Next, it’s very helpful to know “who” is using the old system. Run a query in SQL Agent to poll the sessions on the old system so you understand what hosts are connecting to what database with what logins. If this changes a lot, for example if you have a high or unpredictable number of users, consider a more formal audit that uses a rolling trace and sucks the trace data into a table. In either case try to develop a distinct list of host_name, login_name, database_name for all machines that connect to the server, so that you at least know what you are dealing with in terms of logins.
On older / unknown boxes I manage I have a SQL Agent job that makes a rolling trace to files of login events. I then have a simple trace-read script that will pump that data into a summary table showing distinct values for host, application and login, and last login time. If you try this, be careful about filling a disk with a huge trace or performance impact.
Poll or trace for the machines, applications and logins that are using the old server, and put those in a summary table for reference. This will help tremendously with troubleshooting later.
Way Ahead of Time
Well ahead of the real migration, I generally mock the databases up by scripting creates out at the source and running those scripts at the target. This works around a basic catch-22, where you can’t create logins with default databases that do not exist, and you can’t set database owners to logins that don’t exist. So I will usually make empty DBs using a script on the target server, move the logins over, set ownership for the databases, then restore over those blank DBs with the REPLACE option.
The basic process:
- Set the scripting options in Management Studio to be compatible with the version of SQL Server on the target server. This is an important “gotcha” – if you generate scripts in Management Studio 2008 with this pref set to 2008, but your target server is 2005, then you may have some ugly surprises.
- Script out all the databases as CREATE from the old server.
- If necessary, edit the script to change file paths (if, for example, the new server has a different disk layout or folder structure).
- Connect to the new server and run the create script to make “shell” databases.
To speed things along, I typically use a regex replace to reduce the file sizes that get created by the script. Example:
Replace SIZE = [0-9]*[KM]B with SIZE = 4096KB
These “shell” databases will be overwritten by restore WITH REPLACE later, but will enable setting the paths and owners for the databases ahead of the migration.
Mock the databases with empty files on the new server. Use the mock databases to set file locations and ownership.
Next, bring the logins over from the old server. Note: orphaned users are a preventable annoyance. There’s no reason to get burned by orphaned users when moving from server to server. Copy the logins over using sp_help_revlogin. The process is simple:
- From the MS KB, select the right script for the version of SQL Server you have.
- Run the proc-create script at the source server
- Run the sp_help_revlogin procedure at the source server
- Copy the resulting text out and paste in the editor
- Important: edit the script down to the logins you need, avoiding server-specific logins like BUILTIN\Administrators, cluster groups, NT Authority\SYSTEM, and so on. Cross reference this list with the summary table of logins actually in use, created in Item 2 above.
- Use the resulting script to create the logins at the target server. This preserves the SIDs and passwords for SQL Server logins. And you don’t even need to know the passwords.
Bring all the needed logins over with sp_help_revlogin to prevent having to resolve orphaned logins at the destination server. Validate server roles if needed.
Gotcha: not all versions of the script from MS bring over server roles, and that can burn you. However, in my environment, I strictly reserve server roles to IT staff (people like operations and DBAs) and avoid allowing applications to connect using server roles. For example: if an app connects with a login that gains access to the app data by virtue of being sysadmin (ack!), then there are two problems: 1. it has too many permissions, so it’s not safe; 2. There is no user object in the database, so the “real” permissions needed are not stored there, or in backups of that database. If you have logins with server roles, and legitimately need to keep those, watch out for this.
In order to keep the downtime sane when cutting over from the old server, I generally use log shipping to get “most” of the data to the new server ahead of time, then apply a last log backup / restore at cutover to be sure I capture all changes to the databases. For small stuff, it might be practical to set up a full backup script and a restore script ahead of time, and then run those at cutover time. I don’t like detach / attach generally, because I do like to use backup compression, and I don’t like to wait while copying huge amounts of empty space in data files across the network. But, to each his own – if you like detach/attach, and not log shipping, go for it!
Establish log shipping from the source server to the target server for each database. Use RESTORE … WITH REPLACE to preserve file locations and ownership of databases.
Ahead of Time
Next, it’s vital to port over all the ancillary stuff that isn’t in a database and isn’t a login. And there can be a lot of this other stuff in some environments.
Migrate SQL Agent Jobs
On the source server, script out the SQL Agent jobs that need to come over to the new machine. (Gotcha again: check the version of script output in Management Studio to be sure it matches the version of SQL Server on the target server.) Examine/edit the job scripts for things like file paths, if they are different at the target server. Watch out for SQL Agent proxies, and handle those as needed.
Script out and recreate any SQL Server Agent jobs at the target server. Check carefully for changes to file paths, target server, and so on. Make sure the jobs are disabled at the target until you cut over.
Migrate SSIS Packages if Needed
Copy DTS and SSIS packages if needed. Be very careful to identify how the data connections are configured in the packages, to prevent them running against the wrong servers (this, unfortunately, depends a lot on the specifics of your environment).
Investigate and Recreate Any Other Server-level Objects
Depending on your environment, you might have some other, more esoteric features that need to be recreated at the target server – best do what you can well ahead of time. I once had an application break because it required two backup devices with specific names, because the app unexpectedly used them to batch export data(!) to a network share. Bizarre is the norm in real businesses.
Find and duplicate any other server-level objects.
Linked Servers and Linked Server Logins
SQL Server Agent Proxies
Impersonation rights – if one user has permissions to impersonate another user at the source server, that impersonation grant might not come across with the steps outlined above.
Maintenance plans, especially with literal server names instead of (local). A maintenance plan on one server can act against another server if the server name is hard-coded in the plan.
Luckily most of these items can be duplicated at your target server with a few scripts.
At this point we should have everything staged for cut-over. We have all the data handled, and the logins to access that data. We have the required SQL Agent jobs staged and disabled. All the other server settings/objects should be set at the target server.
In the next installment I will run down the actual cutover checklist I use to switch servers.