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:
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.
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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.
[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
[TABLE]
