Low cost
If you are not familiar with the log shipping mechanism, please refer to the article Log Shipping in SQL Server 2000 - Part 1
In SQL Server 2005, you can build the log shipping architecture to automatically switch to the secondary database in case of a failure in the primary database.
In SQL Server 2000, you should do this manually. This article shows how you can still automate the failover switch when using the log shipping mechanism, in SQL 2000.
Automatic recovery with log shipping:
There is no built-in automatic failover process with log shipping in SQL Server 2000. However, you can build one yourself as described below.
Steps:
The steps that have to be taken for log shipping failover are:
- Identify failure in primary database.
- Send notification email.
- Restore all transaction log backups that have not yet been restored in the secondary database.
- Kill users from secondary database (if there are any) so you can bring the database online.
- Bring secondary database online.
- Change application connection to use the new primary database.
- Disable the existing log shipping jobs (optional).
- Create log shipping from the new primary database to an alternate server (optional).
Description of steps:
- Identify failure in primary database
Check the availability of the primary database to run for both application side and database side:
- In the application side it's needed to change the connection property to the secondary database
- In the secondary site, it's needed to bring the database online and up-to-date
You can choose either to run the check from each of the above scenarios or to have one utility that runs and triggers both changes.
There are several ways to find out if SQL Server is up and running:
How to do it at the database level. Example:
The following SP connects to SQL Server via osql using xp_cmdshell:
The SP returns 0 if the osql command could not connect to the server and 1 if it was connected.
The result seen in the temporary table (#t) may be one of the following:
| In case of failure to connect to SQL Server Instance |
[DBNETLIB]SQL Server does not exist or access denied.
[DBNETLIB]ConnectionOpen (Connect()).
NULL |
| In case of failure to connect to the database |
Msg 911, Level 16, State 1, Server MyServer, Line 1
Could not locate entry in sysdatabases for database 'NonExistingDB'. No entry found
with that name. Make sure that the name is entered correctly.
|
| In case of success |
-----------
1
NULL
(1 row affected)
1>
|
In case of failure to connect to SQL Server instance [DBNETLIB]SQL Server does not exist or
access denied
[DBNETLIB]ConnectionOpen (Connect()).
NULL
In case of failure to connect to the database Msg 911, Level 16, State 1, Server
MyServer, Line 1
Could not locate entry in sysdatabases for database 'NonExistingDB'. No entry found with that name. Make sure the name is entered correctly.
In case of success -----------
1
NULL
(1 row affected)
1>
In SQL Profiler, let's see if the SP is a resource consumer. The first five runs are connecting to an existing SQL Server. The last ones are connecting to a nonexisting server:
The amount of CPU and Reads is "acceptable." Also, monitoring the client machine shows no difference in the amount of CPU used to run this SP and the amount used to run a "select 1" command via SQL Analyzer.
Conclusion: The command is not a resource consumer.
You should run this constantly. The time between the runs depends on the amount of downtime your application can tolerate.
- Send notification e-mail
It is important to send a notification about the primary database's failure, since it must be checked and fixed as soon as possible. You can either send the notification via an application or through the SQL Server instance:
- When creating a job in the SQL Server Agent, you can configure a notification process for that job, sending a notification message to an operator. For more information, please refer to "notifications" section in Books Online (BOL).
- If you configure SQLMAIL in your SQL Server, you can send an email using stored procedures. You'll find information about these SPs in the "Using SQL Mail Stored Procedures" section in BOL.
- If you do not have SQLMail configured, you can use the utility XPSMTP.DLL - SQL Server SMTP Mail XP.
- Restore all transaction log backups
You can just run the job that copies the files from the primary server to the secondary and then run the job that restores them. If the primary server is not available, the first job might fail.
sp_start_job SP can be used for running the jobs.
- Kill users from secondary database
The following script will kill all users in the specific database:
- Bring secondary database online
Run the following command to bring the database online:
RESTORE DATABASE WITH RECOVERY
If there is an existing connection to that database, the command above will fail.
- Change applications connection to use the new primary database
You can either change the connection to the new primary database in the application level, or do it in the SQL Server Client Network properties on the machine where the client runs, as described in my previous article Pros and cons of using merge replication for high availability in SQL Server 2000
- Disable the existing log shipping jobs
Disable the jobs using sp_update_job:
Exec sp_update_job @job_name = , @enabled = 0
Summary: How to run the steps
You can run these steps using an application or throughout the SQL Server Agent.
The best way is to run it from a third server that will check availability of the primary database and will execute the switch. The second choice is to run this from the secondary server. If you choose the latter, and both primary servers fail, there is no switch. With the SQL Server Agent, you can create a job that runs each one of these steps and handles cases of failure.
Don't forget to apply important changes to the secondary SQL Server instance, such as login information, Data Transformation Services and jobs. For example, you can schedule the login information extraction from the primary database on a regular basis and add a step to apply those changes in the secondary when it has to be brought online. One helpful reference is Microsoft's article How to transfer logins and passwords between instances of SQL Server.
Conclusion:
When using Log Shipping in SQL 2000, it is possible to automatically switch to the secondary database in case of a failure in the primary one.
In order to do so, you can constantly monitor availability of the primary database and run a job or application that will do that switch as needed.
About the author: Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services and more.
More information on SearchSQLServer.com
