SQL Server high availability when upgrading to SQL Server 2005

Matthew Schroeder, Contributor

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at [email protected].

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
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

This was first published in July 2008

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.

  1. Security – Transfer the server logins to the secondary server. Be sure they are in the correct state and that you know the passwords.
  2. Orphans – What server logins are associated with what database users? Make sure the associations are correct.
  3. Permissions – Find out what permissions need to be assigned to the server logins.
  4. Configure Database Mail.
  5. Set up job operators.
  6. Job transfer from one server to another.
  7. Rebuild the source SQL Server.
  8. 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.
  9. 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

More on upgrading to SQL Server 2005:

 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 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 [email protected].

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    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.

    Back to top