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.
[IMAGE]
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 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.
[IMAGE]
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 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 ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
[IMAGE]
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.
[IMAGE]
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.
[IMAGE]
[IMAGE]Upgrading Active/Active cluster to Windows Server 2003/SQL Server 2005
[IMAGE]
[IMAGE] Part 1: Team composition and upgrade option pros and cons
[IMAGE] Part 2: Restoring a SQL Server database to a transition server
[IMAGE] Part 3: SQL Server high availability when upgrading to SQL Server 2005
[IMAGE] Part 4: Upgrade live applications to SQL Server 2005 for high availability
[IMAGE] Part 5: Monitor database mirroring and replication after upgrade
ABOUT THE AUTHOR:
[IMAGE]Matthew Schroeder is a senior software engineer working on SQL Server database systems, ranging in size from 2 GB to 3+ TB, with between 2k and 40+k trans/sec. Matt currently works for the gaming vendor, IGT, providing services to gaming companies. He also works as an independent consultant, specializing in SQL Server, Oracle and .NET for industries such as gaming, automotive, e-commerce, entertainment, banking and non-profit. Matt specializes in OLTP/OLAP DBMS systems as well as highly scalable processing systems written in .NET. He is a Microsoft certified MCITP, Database Developer, has a master's degree in Ccomputer Sscience and more than 12 years of experience in SQL Server/Oracle. He can be reached at cyberstrike@aggressivecoding.com.
