Minimize Downtime When Moving to a New SQL Server Environment

I am often asked what ways are there to minimize downtime when upgrading from SQL Server 2000 to SQL Server 2005, or when moving databases to a new (probably bigger and stronger) environment.

Well, if you can afford having both–old environment and new environment in parallel–this task can be very easy and straightforward . . .

Options

First, let’s see what are the options to copy a database from one server to the other.

1. Detaching and re-attaching the database.
2. Using backup and restore.
3. Using the database copy wizard.
4. Manually (creating schemas + transferring data), probably by using DTS/SSIS packages.
5. And here is another cute method: use the snapshot creation and delivery part of the replication.

I am not fond of the third method — the wizard never works very well for me. The fourth method? — nah, I am too lazy. Attaching and detaching database files? — the original database goes offline until the files are moved to the new location; it’s not what we want.

If the database is small to medium size, the replication snapshot can be used. If the database is larger, backing and restoring the databases might be the best solution. In these methods, the original database can stay on line while the secondary database is being built.

If you have large databases

What are the tasks that usually take the most time when moving large databases using backup and restore?

Yeah. Backing up the database on the original server, copying the backup file across servers, and restoring the database on the second server.

Can the original database be on line while this happens?

Sure. You just have to replicate the modifications that happened after the backup was taken.

Does SQL Server have tools for that?

Of course. Log-shipping and replication.

Versions and Editions

Versions

Can we replicate between SQL Server 2000 and SQL Server 2005?

Sure we can. See Using Multiple Versions of SQL Server in a Replication Topology. Just note that for transactional replication, all replicated tables should have a Primary Key.

Can we log ship between SQL Server 2000 and SQL Server 2005?

Yes, the backup types are compatible, so you can log ship from SQL Server 2000 to SQL Server 2005.

Life can be beautiful. But, what SQL Server editions would we need?

Editions

In my opinion, if you have less than the Standard Edition, your databases are most likely small and can probably go offline. These articles show the differences between the different versions:

• For SQL Server 2005: SQL Server 2005 Features Comparison
• For SQL Server 2000: Choosing an Edition of SQL Server 2000 [.doc].

In a nutshell

• In SQL Server 2005, you can log ship with Workgroup, Standard, and Enterprise Editions.
• In SQL Server 2000, you can log ship only with Enterprise Edition. Work around: build a customized log-shipping mechanism. (See Custom Log Shipping).
• In both SQL Server 2000 and 2005, you can replicate with Workgroup, Standard, and Enterprise Editions.

What needs to be done

Create the new site

1. Build the new site and install the SQL Server.
2. Copy the log-ins from existing environment to the new one. See: How to transfer logins and passwords between instances of SQL Server.
3. Set up the replication/log shipping as described below.
4. Copy all additional objects from the existing environment, such as: jobs, DTS/SSIS packages, etc.

Set up transactional replication between the existing and the new environment

You can either set up the replication using the snapshot or use a backup. Using snapshot will synchronize the databases immediately, usually without any issue. If the database is big, it might be better to use a backup.

After the replication is set, transactions will be transferred to the new site continuously, so the new site will be up to date to the seconds.

To setup replication using a snapshot, please refer to Books Online.

Create the replication using a backup

1. Create the publication on the exiting server.
2. Create the subscription, but don’t initialize it. Also configure the Agent Schedule to run on demand. This will create the replication but will not run anything. Modification will be accumulated but not transferred.
3. Backup the original database.
4. Restore database on the new server.
5. Start replication. You might get errors related to the transactions that occurred between the replication setup and the completion of the backup. You can solve those manually and restart the replication jobs (if they failed due to the errors).
6. Modify the job schedule of the replication Log Reader job to start automatically when SQL Server Agent starts (this will set it up to continuously replicate transactions) and start it manually (you need to do this once because SQL Server Agent is already running).

When you need to switch to the new environment, you stop activity in existing environment, move users to new environment, wait few seconds until replication synchronizes and then you can drop/disable the replication. Downtime in this case could be seconds.

Set up log-shipping between existing and new environment

To configure log-shipping, follow the instructions in the Books OL. There are also plenty of resources on the net.

When using log-shipping, no downtime is needed while the database is initially backed up, but with the log-shipping running, the databases are synchronized to the minute, not to the second. When the switch between environments is done, activity in the original database is stopped, and the last T-Log backups needs to be restored in the new database (the last one should turn the database on). Then the users can be switched to the new environment, and the log-shipping dropped/disabled. So the downtime required is only minutes.

Conclusion

If you follow the above instructions, the databases in the new environment are synchronized and can be switched in seconds/minutes.

The next step? Go and worry about the application side and how to change the connection strings to the new environment just as quickly.

Need more information? Want us to help you plan or apply this? Contact us.