Detailed Migration Steps for SQL Server Upgrades, Part II

Preparation / Seeding Databases for Migration

The key to minimizing downtime during migrations is to use a technique that's roughly akin to poor man's log shipping—where you spin up a restored (but non-recovered) copy of your production database over on the new server, and then keep it 'synchronized' via T-Log backups. Only, manually applying large numbers of T-Log backups can be a pain—so the use of DIFF backups plus T-Log backups can help to streamline things quite well.

Related: Detailed Migration Steps for SQL Server Upgrades, Part I

Using this approach, however, it's possible to move even 100 GB and larger databases across entire continents with just a few minutes of actual downtime during the demotion/promotion operation.

To put this into more concrete detail, let's assume that we're moving a 60 GB database called 'WidgetCo' from an old server to a new one—and that we're upgrading from SQL Server 2008 to 2012. Assume, too, that all security information (logins, firewalls, etc.) has already been migrated, and that acceptance testing has verified that everything will work as expected on the new hardware.

Then, assume too, that you've communicated a migration time of, oh, say, 10 p.m. on a Friday night. Then, for fun, let's also assume that the old server is in California, and the new server is in New York (but, for the sake of simplicity, we'll just be dealing with all times in Pacific time).

To start your migration, you, as the DBA, will need to kick off the migration hours in advance of your 10 p.m. 'switch.' If I were making this change, I'd test backup, file-copy, and restore times a couple of times and a couple of days in advance—to get a better idea of the times involved. But, for the sake of argument, let's say that at 3 p.m. on Friday, you move a copy of a FULL backup of the WidgetCo database to your New York server, and restore it—without recovering it (i.e., you need to leave it able to have T-Logs and/or DIFF + T-Log backups applied). Then, depending upon transaction rates/volume on this system, you can either wait until around, say, 8 p.m. and apply all T-log backups since your last FULL backup of the WidgetCo database, or (my preference in most cases): take a DIFF backup at, say, 8 or 9 p.m. (depending upon how long it'll take to generate, push over the wire, and stand-up) and then skip huge numbers of T-Log backups. The idea here, though, is that by around 9:40-9:50 p.m. you want to have your database on the new server synchronized or caught up to effectively what's been happening on the old server—by applying backups and transaction log backups via what amounts to a manual or "poor man's" log shipping.

Migration

When the time for the actual migration comes (i.e., when it's time to demote the old server and promote the new server), you'll want to take (at least) the following steps after double-verifying that everyone else participating with you in this migration is ready to go.

1. IMPORTANT: Read this entire list of instructions before even attempting to run your own migration. And, for better results, see the notes at the bottom of this post about customizing these instructions and details for your OWN environment and for each specific migration.

2. Spin-up any Maintenance Pages/Notifications for your applications if possible (to let end-users know that your site or apps are down for maintenance).

3. Set the WidgetCo database to SINGLE_USER mode with the following syntax:

USE master;GOALTER DATABASE WidgetCo SET SINGLE_USER WITH ROLLBACK AFTER 20 SECONDS;GO

See Books Online for more info. But the idea here is that you'll give any currently running operation/transaction 20 seconds (for example) to complete before being forcibly rolled back.

4. Kick off a final transaction log backup. Think of it kind of like a tail-of-the-log backup as this backup should provide a backup of all transactions that have occurred on the old server and which haven't yet been replicated to the new server.

5. IMPORTANT: Take the WidgetCo database OFFLINE (to prevent any applications that developers/operations folks might have missed from EVER accidentally connecting to this database in the future—i.e., favor Highlander over Spartacus). To do this:

ALTER DATABASE WIdgetCo SET OFFLINE;GO

6. Let developers/operations folks know that it's now safe for them to start making config and connection string changes to point to the NEW server instead of the OLD. (They'll be working concurrently with the changes you'll be making on the database server—no need to make these changes serially if downtime is a big concern. That said, this only goes quickly if they know exactly what they're doing.)

7. Copy your last/final T-Log backup over to the new server and apply it (along with any other T-Log backups necessary).

8. Restore the WidgetCo database on the new server, and recover it, i.e.,

RESTORE DATABASE WidgetCo WITH RECOVERY;GO

9. Verify connectivity, etc., from within SQL Server Management Studio (SSMS).

10. VERY IMPORTANT: If you're upgrading to a newer version of SQL Server, make sure to repeat any of the steps you did during acceptance testing, such as upgrading db compatibility mode, updating statistics, and the like.

11. As application connection strings and config settings are changed, applications should be able to see and connect to the new database. (Make sure to let devs/ops know when the database is open/available for use—anyone/anything not able to connect AFTER this is an issue that needs to be reviewed and escalated—or which might cause a 'no go.')

12. IMPORTANT: Depending upon data sensitivity, you'll probably want to PREVENT individual applications or application servers from being returned to service after they've been able to connect to the new server—just to prevent any ugliness should a rollback or no-go be required. (In other words, make sure that devs or operations personnel can see/test connections into the new database—but don't assume that once they can that it's safe to return said application/server back to full service until ALL applications/servers are successfully connected to the new database server. Otherwise, their apps/users might write changes to the database on the new server—which will be REALLY ugly if you find an app or server that can't connect for some reason and end up needing to 'roll back' to the old SQL Server.)

13. Once everything is connected to the new database, the migration is effectively complete. Or, if something isn't able to connect and you're not able to troubleshoot or remedy the situation within the times agreed-upon as part of your go/no-go strategy, then you'll need to rollback.

Failback

If you have to rollback, doing so is really just a question of two primary concerns. First, application connection strings and config details will need to be reverted (or re-configured to point BACK to the old server). That part should be more or less trivial (even if it might be painful in some environments).

The next part, however, is critical because you'll have to make sure you continue to favor the Highlander model over a tear-jerking Spartacus model. Or, in other words, the order of operations that you'll need to undertake as the database administrator (DBA) is as follows:

1. Switch the WidgetCo database to SINGLE_USER on the NEW database server.

/* run on the NEW server */ALTER DATABASE WidgetCo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;GO

2. Take the WidgetCo database on the new server offline.

3. Then, bring the WidgetCo database on the OLD server back ONLINE and switch it back to MULTI_USER mode.

/* Run on the OLD server */ALTER DATABASE WidgetCo SET ONLINE;GOALTER DATABASE WidgetCo SET MULTI_USER;GO

Failure to execute in the exact order above can mean that applications (which are in an inconsistent state—i.e., some pointed at the old server, others pointed at the new) could end up writing data to two different databases.

Next: Detailed Migration Steps for SQL Server Upgrades, Part III