One of the top SQL Server database troubleshooting skills

All these technologies can be used as a means to maintain a backup server. At the same time, this database can be launched when your original primary database has a problem and serve as a new primary server. However, you must remember that replacing the standby server with the online server only completes half of the troubleshooting work.

To ensure that your application works properly, there are still many precautions outside the database. This includes logon information, database users, scheduling tasks, DTS and SSIS packages, executable files, objects in the system database, databases with the same name, and linked servers.

Sometimes these tiny dependencies are discovered only when you perform a database fault recovery, so that you have to spend a lot of time debugging and evaluating the root cause of the problem. In addition, you must enable the second server and application as soon as possible to reduce downtime. Therefore, setting in advance is very important.

When it comes to disaster recovery planning for high availability and SQL Server, remember one of my favorite Latin sayings-Si vis pacem, para bellum, if you want peace, you have to prepare for the war first." After remembering this, let's take a look at some possible problems. I will also recommend several tasks that can be completed in advance to ensure that the database fault recovery process is completed quickly and effectively.

SQL Server login information and database users

Your fault 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 transmission or database images, your database will be restored, so that you can complete the recovery process only after they are re-launched.

With Windows authentication, you can easily map login information to database users. However, if you are using SQL authentication, You need to manually reestablish the connection between the login information and the database user on the database you obtain from another server. Therefore, when you migrate the database, you will lose the connection between the login information and the database users.

After you restore the database on the second server, run the following code:

USE YourDatabaseName

EXEC sp_change_Users_Login 'Update _ one', YourDBUserName, YourLogin

Another way to keep logon information synchronized is to follow the steps in Microsoft Knowledge Base's article about transferring logon information and passwords between SQL Server instances. This article explains how to use the original SID script for login information. When you create the logon information on the fault recovery database server, the connection between the logon information and the database user is saved, so that you do not have to run the above script to fix isolated users.