One of the top tips for SQL Server database failover

All of these technologies can be used as a means of maintaining a standby server, and the database can be online as a new primary server when problems arise in your original primary database. However, you have to keep in mind that replacing the standby server on the line only completes half of the troubleshooting work.

To ensure that your application is working properly, there are many caveats outside the database. This includes logon information, database users, scheduling tasks, DTS and SSIS packages, executables, objects in the system database, databases with the same name, linked servers, and so on.

Sometimes these small dependencies will only be discovered when you have a database failure recovery, so you have to spend a lot of time debugging and evaluating the root cause of the problem. In addition, you must have the second server and application online as quickly as possible to reduce downtime. Therefore, it is very important to make the settings in advance.

When it comes to high availability and SQL Server disaster recovery planning, you should keep in mind that I like a Latin proverb--si vis pacem, para bellum, which translates as "If you want peace, you have to prepare for war first." "With this in mind, let's look at some of the problems that might be encountered." I will also recommend several tasks that can be completed in advance to ensure that the database recovery process is done quickly and efficiently.

SQL Server logon information and database users

Your recovery server should back up all login information and database users, including passwords. Login information can be created at any time, but if you use log transfer or database mirroring, your database will process the recovery status so that you can complete the recovery process only when they are back online.

With Windows authentication, you can easily map login information to database users. However, if you are using SQL authentication, then you need to manually re-establish the login information with the database user on the database you obtained from another server. Therefore, you will lose the connection between the login information and the database user when migrating the database.

When you restore the database on the second server, run the code:

Use YourDatabaseName

EXEC sp_change_users_login ' Update_One ', Yourdbusername, Yourlogin

Another way to keep your logon information synchronized is to follow the steps of the Microsoft knowledge Base on the article on transferring logon information and passwords between instances of SQL Server. This article explains how to use the original SID to script the login information. When these logon information is created on the recovery database server, the connection between the login information and the database user is saved so that you do not have to run the above script to fix the orphaned user.