This is part three 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 walks 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.
As outlined in part two of this series, the first stage of upgrading to SQL Server 2005 and Windows Server 2003 is to point the applications at a second transition server where it would run while the primary cluster is rebuilt. This is a fairly detailed process that is best undertaken 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. I'll walk you through the general concept of minimizing downtime and the considerations you need to take into account on the way.
Stages of the server transition
The process of transferring a database to another server and having a successful cut-over seems rather simple, until you get into the details. Here is a list of items that must be considered during the transition to a secondary server:
- Database backup/restore – You could replace this with replication or log shipping depending on your scenario. In our case, we'll do a simple database restore, then grab the "tail end" of the log and restore that to bring the secondary server up to date.
- Security – Transfer the server logins to the secondary server. Be sure they are in the correct state and that you know the passwords.
- Orphans – What server logins are associated with what database users? Make sure the associations are correct.
- Permissions – Find out what permissions need to be assigned to the server logins.
- Configure Database Mail.
- Set up job operators.
- Job transfer from one server to another.
- Rebuild the source SQL Server.
- Repeat steps 1 through 8 for the newly rebuilt source SQL Server, then use database mirroring to transfer the database back to the source SQL Server.
- Transfer the database back to the source SQL Server. Wait for the databases to become synchronized, shut down your application and point it to the rebuilt source SQL Server that has the mirrored database. At this point you can do a manual failover, which will make the rebuilt source SQL Server the primary server. Then recover the database so your applications come back online.
To fully understand how to tackle each step, let's consider each one in detail.
Database backup/restore
This stage is by far the simplest. A full database backup is done at the source system and restored at the transition server while the application continues running. The application is then shut down and the final tail-end of the log is backed up. The tail-end transaction log backup is then copied to the transition server and restored. This brings the database on the transition server up to date with the last production copy.
Security and orphans
Server logins have to be transferred from the source system to the transitional server and there are two methods for doing this. SQL Server Integration Services has a Transfer Logins task that you can use to transfer logins from one server to another. There's also the option of using T-SQL code, which can be scripted to transfer the logins.
SSIS has a few unfortunate features: The task disables all SQL Server logins on the transition server, scrambles all passwords and leaves the SQL Server logins orphaned. So for any SQL Server logins, you're forced to write scripts to enable the
logins, set all passwords to the correct values and associate server logins with database users (eliminating orphans).Considering the limitations, if you have a lot of SQL Server logins, it's best to simply write T-SQL code for transferring your logins. The only extra step would be the actual creation of the server login.
If you have all or mostly Active Directory logins, SSIS is ideal. Passwords are not scrambled, logins are not disabled and logins are automatically associated with the database users rather than orphaned like SSIS when it transfers SQL Server logins. And SSIS is more flexible as logins are added, updated and deleted – as opposed to scripting.
Transferring permissions
Permissions can be a complicated topic because server roles are server-level permissions, as are some securables. The user mapping link on logins basically correlates to the database-level permissions. When a database is restored to any location, it already contains the database users and all associated roles and permissions within that database. The only thing missing is an associated server login, which can be either an Active Directory (AD) login or SQL Server login. Once a server login is associated with a database user, then the database user is no longer orphaned and the server login inherits the rights associated with the database user.
The Login Transfer task in SSIS is supposed to transfer server-level permissions, but in our transfer from SQL Server 2000 to SQL Server 2005, this function was buggy and only worked occasionally on SQL Server 2005 SP2. So in this scenario, you will need to write scripts for the transfer of server-level permissions.
Database Mail configuration and job operators
The job creation step often requires that Database Mail be configured and job operators be set up. Occasionally you have to transfer job categories if you have custom job categories.
Job Transfer
SSIS has a Job Transfer task that, unfortunately, can only transfer jobs from a SQL Server 2005 box to another 2005 box. That said, we're unable to use this task for upgrading to SQL Server 2005. Instead we need to manually script out each job and run it on the transition server or write a lot of custom SSIS code.
After this stage, you can point the application to the transition server and run your operations normally.
Rebuilding the source system
Since the application is running live on the transition server, you have all the time you need to rebuild the source system fresh -- rather than resorting to an upgrade and hours of downtime.
Transferring back to the source system
When you transfer back to the source system, follow the same steps you used when moving to the secondary server -- with one difference. Since at this point the transition server is SQL Server 2005 and the source system has been rebuilt as a Windows Server 2003/SQL Server 2005 box, database mirroring (SQL Server 2005 feature) is now available as an option to transition back to the source system.
First, take a backup of the transition server database while your application is still running against it and restore that database to the newly rebuilt source system. Do not recover the database when you restore it (select the non-operational option) and keep the database name the same. You do not want to recover the database, since that modifies the LSN (log sequence number) that SQL Server uses to apply transactions coming from the database sitting on the transition server.
To set up database mirroring, you want to create endpoints on both the transition server (principal) and the rebuilt source system (mirror). High-safety mode is best at this point, since it ensures that the database synchronization will catch up and ensure the databases stay synchronized between the transition server and the rebuilt source system. Once the databases are synchronized, you can proceed to the next step.
At this point, the databases should be synchronized. You'll want to start pointing your applications at the rebuilt source system and the mirror database that is still mirrored/synchronized at this point. Applications will now go offline and you need to "manually fail over" the database mirroring. The database on the transition server can be manually failed over since it is now in a synchronized state. Once manually failing over the database, the mirrored database on the rebuilt source system will be recovered and accessible. The old transition server database will become the mirror database at this point.
The pursuit of minimal downtime when upgrading to SQL Server 2005 often leads to complex solutions that are risky. Hopefully this article helps reduce the risk of your efforts. Just be sure you follow the checklist and everything should turn out fine.
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
| ABOUT THE AUTHOR: |
|
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.
|
|
