How to restore SQL Server database to transition server during an upgrade

Matthew Schroeder, Contributor

This is part two in a series of articles describing the case history of a database upgrade from SQL Server 2000 Active/Active cluster running on Window 2000 Server to a Windows Server 2003/SQL Server 2005 Active/Active cluster. Consultant Matthew Schroeder will walk you through the technical and decision-making process of real-world IT and database management teams. The article is based on two online upgrades: a commercial website and an eBay ordering system. For confidentiality reasons, certain details of the actual project have been changed.

In part one of this series, you learned about the team involved in this case study and about the pros and cons of available upgrade options. Now that we've chosen our upgrade strategy, the next task is to actually transition to a temporary server to run the application while we upgrade the local SQL Server cluster. We'll do this in four stages.

Stage 1: Our application flows to the local Active/Active SQL Server 2000 cluster.

Figure1: Application flows to SQL Server 2000 cluster.

Stage 2: We need to build a second (transition) server and then move the SQL Server 2000 database to it. We can accomplish this step through replication, log shipping or by restoring a database to the transitional server and finally restoring the "tail-end" transaction log to the transitional server while the application is offline. The option we chose was to "restore the database

    Requires Free Membership to View

to the transitional server." That way, our application would only need to be offline during the time required to back up the tail-end of the transaction log, copy it to the transitional server and the time it takes to restore the transaction log to the transitional server (Figure 2). We limit downtime because, while the application is running on the transitional server, the local Active/Active cluster is rebuilt with Windows Server 2003/SQL Server 2005.

Figure 2: Restoring a database to the transitional server.

Stage 3: We'll move from the transitional server back to the local Active/Active cluster rebuilt to Windows Server 2003/SQL Server 2005. This stage is a lot like Stage 1, only we're moving the database back to the local server and we can now access SQL Server 2005 technologies. We can choose from backup/restore, log

More on SQL Server upgrades and availability:

 shipping, replication -- and now that we're on SQL Server 2005 -- database mirroring.

If we used log shipping and replication to move back to the local cluster, there would be a chance for the local boxes to be tampered with during transition – even if we were careful with security. We don't want to risk the database on the transitional server getting out of sync with the local database we're prepping. Backup/restore is also an option, but that requires minimal downtime while the "tail-end" transaction log backs up, copies and restores. Since we now have SQL Server 2005 available, we can use database mirroring to transition to the local cluster without any downtime. Because the mirror is always in recovery mode, we're not concerned with the local database becoming out of sync with the database on the transitional server.

Figure 3: Database mirroring is in recovery mode.

Stage 4: We switch the database mirroring so that the local Active/Active cluster is the primary server and the transitional server is the secondary. At this point the application (assuming we're running VS 2005+ MDAC) will automatically point at the new primary server.

Figure 4: Application traffic points to your new SQL Server.

Note: If you are not using the latest copy of MDAC, you will have to modify the connection strings to point to the new primary server. The applications should run fine at that point.

Using the transitional server for your upgrade to SQL Server 2005 and Windows Server 2003 is a fairly detailed process and is your best option if you simply cannot afford the luxury of being down for several hours. The sequence of steps is absolutely critical and full of small "features" that can trip you up.

In part three of this series, I'll take you through the general concept of minimizing downtime and the considerations you need to take into account.

Upgrading Active/Active cluster to Windows Server 2003/SQL Server 2005

 Part 1: Team composition and upgrade option pros and cons
 Part 2: Restoring a SQL Server database to a transition server
 Part 3: SQL Server high availability when upgrading to SQL Server 2005
 Part 4: Upgrade live applications to SQL Server 2005 for high availability
 Part 5: Monitor database mirroring and replication after upgrade

Matthew Schroeder is a senior software engineer who works on SQL Server database systems ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. He specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. Matthew is a Microsoft certified MCITP, Database Developer, has a master's degree in computer science and more than 12 years of experience in SQL Server/Oracle. He can be reached at cyberstrike@aggressivecoding.com.

This was first published in June 2008

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.