Achieve high availability with log shipping in SQL Server 2000

by Tim Chapman | Nov 06, 2006 7:04:00 PM

Tags: Databases, Storage, Tim Chapman, Microsoft SQL Server, Log Shipping, standby database, high availability solution, database, clustering, standby server, Microsoft SQL Server 2000, SQL Server Newsletter

Takeaway: Log shipping is a technology in SQL Server that database administrators can use to provide a standby database to failover to in the event of a primary database failure. Tim Chapman outlines this useful technology.

In a recent article, I discussed some of the advantages and drawbacks of using SQL Server 2000 clustering as a high availability solution. While clustering is a useful high availability solution, sometimes it may be overkill, and it is very expensive. Database administrators can use log shipping in place of cluster to provide high availability.

Log shipping is the process in which the transaction log files from one database are sequentially restored to a standby database to create an "almost" hot spare of that database. This is a process that is set up and handled in the SQL Server 2000 database engine, so the restore process on the standby server is done automatically rather than manually by the database administrator. In the event that your production server suffers a failure, you can manually restore processing to this standby server. (Note: Although log shipping is available in SQL Server 7.0 and 2005, this article is more tailored to SQL Server 2000.)

Why use log shipping?

Log shipping is a type of high availability solution, and it works rather effectively. One of the biggest benefits of log shipping is that it is a much cheaper high availability solution than clustering. This is because the hardware requirements that are necessary for clustering are not required for log shipping.

Log shipping occurs at the database level rather than the server level; so, it is possible to place your standby database on a server that you are already using for other purposes. This may present problems if a failure occurs and you need to switch to using your standby database, but the option is available.

Log shipping is relatively easy to set up. SQL Server provides a very nice wizard that guides you through the set up process.

Log shipping allows you to maintain redundancy in physically dispersed locations, which is very difficult to accomplish with SQL Server clustering. This is a great feature because, if a disaster occurs in your data center, you can still failover to your standby server, whereas with clustering in the same data center, you could be in a lot of trouble.

Another advantage of log shipping is that you are able to use the standby database as a reporting database. This is a very nice option for many companies. However, if you decide to use this standby database for reporting, you must be aware of the limitations. SQL Server requires exclusive access to the database while applying the transaction logs from the primary database, so reporting will not be possible while the transaction log file is being applied.

Log shipping considerations

Before implementing log shipping as a high availability solution, there are certain things to consider. Due to the latency involved from the primary to standby database, it may not be a viable high availability solution for your company. The latency period is set by the database administrator and can be reduced as necessary, but it can never be avoided.

There is no built in failover capabilities for log shipping, which means that some downtime has to be incurred in the switch to the standby server. The database administrator must complete a couple of actions to bring the standby database online. These steps include:

• Restoring all transaction log backups from the primary database that have yet to be restored on the standby server. Once all logs are restored, the database will need to be recovered to bring it online.
• Once the database has been brought online, all applications that will need to access that database will need to change their connections. This is a step that needs to be thoroughly planned. If you are not able to quickly point your applications to the newly recovered database, your efforts will be futile.

An instance of SQL Server can be used to monitor log shipping. This instance can be on the primary server, standby server, or a separate server. Any version of SQL Server can be used for SQL Server monitoring.

Note: The database logins will need to be synchronized from the primary database to the standby database.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Print/View all Posts Comments on this article

This is useless edmicman1 | 11/06/07

Wow..relax Shellbot | 11/06/07

Sorry edmicman1 | 11/06/07

Me too Shellbot | 11/07/07

I'll play mommy! kahren@... | 11/06/07

RE: Wow..relax chapman.tim@... | 11/06/07

No worries Shellbot | 11/06/07

SQL 2000 Enterprise Only bandys@... | 11/06/07