MS SQL Server Failover

If you were not able to perform database recovery in step 2 in the Standby Server Failover section, or if you are manually failing over to a target, then you will need to recovery the target database to make it usable be performing a database Role Change as detailed below.

Run the following Transact SQL commands below on the Target Database Server in order to "recover" the target standby server and turn it into the new primary server.

If you are planning on using a target standby server as a failover for a primary server you need to ensure that the MS SQL Server login IDs are synchronized between the primary and target servers. This is important because MS SQL Server maintains separate security among servers, and you will be making a backup of a database on one server and restoring it to another. In order for the database user IDs (found in each database) to work on the standby server (where the database is restored), there must be a matching MS SQL Server login ID on that server, otherwise users will not be able to log into the database on the standby server.

Script the login IDs from the primary server and then run the script on the standby server to create the login IDs. You will have to do this periodically as users are added and deleted. While scripting works great for SQL Server login IDs that use NT Authentication, it doesn't work so well for login IDs that use MS SQL Server Authentication. This is because if you have login IDs using MS SQL Server Authentication, you will have to know the user's passwords in order to set them, or figure out some way to get users to set the passwords themselves. Login IDs that use NT Authentication are no problem because passwords aren't used. Take a look at http://support.microsoft.com/kb/918992/ from an example on how to script this task.

Create a Replication Job in Peer DRS to backup the master database on the primary server and copy it to the target server without restoring it into the database server. You will then need to either manually restore it into the standby target server while in single user mode, or enable the restore job and trigger the job manaully. You should do this whenever login credentials are updated, or at the time of failover since the target will already have the most recent copy of the master database on the server. See Replicating MS SQL Server Master Database for more information on the details.

4.	Use MS SQL Server Data Transformation Services to keep the user logins synchronized. See the following article for more information: How to set up and perform a log shipping role change

But if you will be using the standby server for other databases, such as for development databases, you won't be able to use Peer DRS to keep the master database synchronized. Instead, you will have to use scripts, or manually keep the two servers synchronized. If you have to do this, then you will want to use only login IDs that use NT Authentication, not MS SQL Server Authentication, because of the password's issue.

Another problem with login IDs using SQL Server Authentication is the fact that when you restore a database to another SQL Server, you must synchronize the login IDs on the server with the database user IDs in the database. This is not a problem with user IDs that user NT Authentication. To synchronize the logins IDs and the database users IDs, you must run the sp_change_users_login stored procedure.

If you want to make failover easy, then don't use the target standby server for anything other than failover, and always use NT Authentication for your login IDs. While you don't have to do either, it will make things more simple.

Once you have figured out a way to synchronize the SQL Server login IDs, you are ready to begin the next step.

Next, go to Step 5 in the Standby Server Failover section to point your users/application to the new primary server.