Talking about SQL Server System Troubleshooting Top Skills

SQL Server 2005 and 2008 have several options for high availability, such as log shipping, copying, and database mirroring. All of these technologies can be used as a means of maintaining an alternate server, and this database can be brought online and used as a new primary server when problems occur with your original primary database. However, you must remember that replacing the standby server with the line is only half the troubleshooting work done.

There are many things to keep in mind outside the database to keep your application working. This includes login information, database users, scheduling tasks, DTS and SSIS packages, executables, objects in the system database, databases of the same name, linked servers, and more.

Sometimes these small dependencies are only discovered when you perform 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 let the second server and application go online as quickly as possible to reduce downtime. Therefore, it is very important to make settings in advance.

When it comes to high availability and SQL Server disaster recovery planning, you should keep in mind a Latin slang I like - Si vis pacem, para bellum, which translates to "If you To get peace, you have to prepare for the war first. After remembering this, let's take a look at some of the problems that may be encountered. I will also suggest a few pre-completed tasks to ensure that the database failure recovery process is completed quickly and efficiently.

SQL Server Login 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 shipping or database mirroring, your database will handle the recovery state so that you can only complete the recovery process after they are back online.

With Windows authentication, login information can be easily mapped to database users. However, if you are using SQL authentication, you will need to manually re-establish the login information to the database user on the database you obtained from another server. Therefore, you lose the connection between the login information and the database user when you migrate 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 login information synchronized is to follow the steps on the Microsoft Knowledge Base for articles about transferring login information and passwords between SQL Server instances. This article explains how to script login information using the original SID. When these logins are created on the failback database server, the connection between the login information and the database user is saved, so you don't have to run the above script to fix the orphaned user.