Log Shipping in MS SQL Server 2008

Your users expect your systems will stay up as much as possible. This would be in the face of unexpected events such as system failures as well as planned downtimes such as maintenance or upgrades. A high-availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized. The recent versions of Microsoft SQL Server include several high-availability solutions like failover clustering, database mirroring, log shipping and replication. In this article I will describe one solution which DBAs have always been interested in my training programs — log shipping.

You may be already comfortable with taking backups of your database and restoring that in the same or a different system. You keep backing up the transaction logs with some periodicity and during restore, you restore the logs. In log shipping, you restore the full backup once into a secondary database, and periodically keep backing up the log and restoring it on the secondary database. In effect, the secondary database becomes your disaster recovery site. What is more, you need not do all these tasks manually; the system will take care of these things for you. When you are applying the transaction logs, the secondary database can be in standby or recovery mode. In the standby mode, users can query the secondary database in a read-only fashion. In the case of a disaster, switching from primary to secondary is a manual process.

I will run you through the configuration on an SQL Server 2008 instance, but the options remain quite similar for SQL Server 2005 and the Denali CTP (which in all likelihood would be SQL Server 2011 when released). And though the screens look different, the principle of log shipping is the same even for SQL Server 2000. I will be using the AdventureWorks2008 database on my instance as the primary database; you can choose any database you want when you are following these steps.