Manage Learn to apply best practices and optimize your operations.

Top tips for SQL Server database failover success

Utilizing log shipping or database mirroring for high availability is only half the battle. There are still plenty of steps to take once your standby server is online.

SQL Server 2005 and 2008 offer several options for high availability such as log shipping, replication and database mirroring. All of these technologies provide the option of maintaining a standby server that you can brought online and made the new primary server in the event your old primary one dies. What you must remember, however, is that bringing your database online using the standby server is only half the battle.

There are many dependencies and considerations outside the database that are required to make sure your applications work properly. These can include logins, database users, scheduled jobs, DTS and SSIS packages, executables, objects in system databases, synonyms, linked servers, and so on.

Sometimes these minute dependencies go unnoticed until after you do a database failover, at which point you will have to spend time debugging and evaluating the cause of the issue. Additionally, you might be under the gun to bring the secondary server and application live as quickly as possible in order to minimize downtime. Therefore, it is very important to do as much setup in advance as possible.

When it comes to high availability and  disaster recovery planning for SQL Server, you should always go by my favorite Latin saying – Si vis pacem, para bellum, which translates to "If you want peace, prepare for war." With that in mind, let's take a look at some potential issues that may arise when bringing your applications live on a new database server. I'll also suggest several tasks that can be done in advance to ensure a quick and successful database failover process.

SQL Server logins and database users

Your failover server should mirror all logins and database users right down to the password. Logins can be setup anytime, but if you are using log shipping or database mirroring, your databases will be in recovery state so you'll be unable to complete the setup until they are brought online.

With Windows authentication, it is easy to map the login to the database user. If you are using SQL authentication, however, you will need to manually reestablish the link between the login and database user in the database you brought over from another server. This is because the internal login security identifier (SID) is different on each server. Therefore, you lose the link between the login and the database user when migrating the database.

After you bring the database online on the secondary server, run this code:

USE YourDatabaseName
EXEC sp_change_Users_Login 'UPDATE_ONE', YourDBUserName, YourLogin

Another option for keeping logins synchronized is to follow the steps from the Microsoft Knowledge Base article on transferring logins and passwords between instances of SQL Server. The article describes how to script logins with the original SID. When you create these logins on the failover database server, the link between logins and database users will be preserved so you will not have to run the above script to fix orphaned users.

The msdb database objects

This database holds items like Data Transformation Services (DTS) or SQL Server Integration Services (SSIS) packages depending on the version, maintenance plans and scheduled jobs. When it comes to maintaining a failover server with SQL Server, you have two options:

  1. Do a parallel deployment and deploy jobs and packages on both the primary and secondary servers every time you add or modify these objects.
  2. Restore the msdb database from the primary server, either periodically or when your primary server fails.

If you use parallel deployment, you should keep the jobs on the secondary server disabled and enable them when you failover. Again, if you are using database mirroring or log shipping, you will not be able to create maintenance plans until after the database is live. Additionally, you should document the jobs along with their settings and schedules so you can quickly recreate the information.

If you restore msdb from the primary server, all the jobs, packages and maintenance plans should carry over. You should still keep an eye on the jobs closely, as they will run on a different server now and are more likely to fail if the conditions are not the same. You should also open all maintenance plans and make sure database connections on backup and other plans are not pointing to the old server. If so, change the connection to the new server. You can modify the connection maintenance plans by clicking on the Manage Connections button on the plan toolbar. You will get a dialog similar to this:

Figure 1. Modifying connection maintenance plans in SQL Server

It's important to examine and modify your maintenance plans if you bring the msdb database over from the old server. For example, the above plan was created using the server name rather than referencing the local connection.

Custom objects in the master database

Some DBAs stick code or helper tables in the master database (or a utility database), which are then accessed from user databases or applications. If you do not deploy these objects on the secondary server, you may receive error messages. To avoid this issue, use parallel deployment or maintain a set of scripts that you can quickly execute on the secondary server to create these dependent objects.

Executables and COM components

Similarly, if your databases or scheduled jobs rely on functionality implemented in executables or COM components, remember to keep the secondary server identical with the primary, and always deploy and modify in parallel with the primary server.

SQL CLR objects

When restoring a database, more often than not I run into issues involving SQL CLR objects on another server. I find it easier to maintain scripts that would drop and recreate my CLR objects. The sequence of scripts includes:

  1. Drop procedures and functions that depend on CLR.
  2. Drop CLR assemblies.
  3. Create CLR assemblies again from DLLs (SQL Server Management Studio doesn't handle a huge DLL scripted as binary very well).
  4. Create CLR procedures and functions.

Linked servers

If your applications make cross-server calls, remember to maintain the same linked servers on the failover server. Otherwise, errors will occur if you failover. Similarly, if other SQL Servers access your primary server using linked servers, remember to make modifications. Similarly, the same issue applies to synonyms. Since a failover moves one or more databases to another server, synonyms referencing the old locations stop working and need to be modified. Moving databases around when they are accessed using linked servers might also require code modifications. Many of these issues can be minimized and easier to manage through proper use of SQL Server aliases and synonyms.

For a more detailed discussion of aliasing and synonyms check out my article on configuring aliases for SQL Server databases and servers.

Re-pointing the applications to the new database server

Once your databases are running on another server, you need to modify your applications to point to the new location. If your Web server has many connection strings to modify, you should utilize aliasing as mentioned above. For example, if you failed over from Server1 to Server2, you can create a SQL Server alias and point Server1 to Server2 as shown below:

Figure 2. Creating a SQL Server alias

You don't need to modify any connection strings using this method, and you might save yourself more work in the future by bringing back the primary server and failing back, at which point you can just delete the alias without touching any connection strings.

Roman Rehak is a senior database architect at in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.

Next Steps

Microsoft responds to Spectre and Meltdown chip vulnerabilities 

Dig Deeper on SQL Server High Availability, Scalability and Reliability