Log Shipping and Operational Reporting

  • Article

In my last post I introduced the first phase of the Airframe RA project called Operational Reporting.  The gist of this phase is to augment an existing production system, in this case Airframe, with some quick and dirty reports to help everyday users of the system within Microsoft get their jobs done a bit easier.

We need these reports yesterday, so I don't have a lot of time to massage the data into a more useable form.  The existing production database schema we use in Airframe will have to do.  While it was tempting to build the reports right on top of the production database, I didn't want to do anything to compromise performance for users of the production system.

What I needed was a quick and dirty way to create a copy of the production database that is reasonably up-to-date, and host that copy on a completely different server to offload the overhead associated with the Operational Reports to a separate system.  I decided to use a feature in SQL Server 2005 called Log Shipping to accomplish this.

Basically log shipping takes regular transaction log backups from a primary database and applies them to a secondary database.  There's a handy wizard in SQL Server Management Studio for setting it up which creates a couple of SQL Agent jobs on the primary and a couple on the mirror, and you can have email sent to an operator if things get out of whack. 

The secondary database can optionally be left in Standby mode, which makes it available for read-only operations.  This killed two birds with one stone for me.  We can use the secondary database both as a data source for our operational reports, and as a hot standby in case something catastrophic happens to the primary database.

The problem with wizards, especially complex ones that work with distributed systems, is that sometimes they don't work right.  Unfortunately I had a problem with the log shipping wizard which required some manual intervention.  The wizard failed when it tried to initialize the secondary database and put it in Standby mode.  Basically it was trying to create a standby file in a system directory that SQL Server didn't have access to, and I couldn't find a way to redirect it to a folder where SQL Server had the right priveleges.  The specific error I got was:

During startup of warm standby database 'AirframeHotStandby' (database ID 6), its standby file ('C:\WINDOWS\system32\AirframeHotStandby_20060630063016.TUF') was inaccessible to the RESTORE statement. The operating system error was '5(Access is denied.)'. Diagnose the operating system error, correct the problem, and retry startup. (Microsoft SQL Server, Error: 3441)

Fortunately most of the wizards in SQL Server Management Studio support a scripting option, so I was able to save off the wizard actions as a T-SQL script and execute it in stages to get around the problem.  Basically I initialized the secondary database myself by doing a restore and put the database into standby mode.  The UI for RESTORE DATABASE did allow me to specify the location of the standby file.

I wound up with the following:

  • An initialized secondary copy of my primary database on a different server in standby mode
  • A job on the primary server which backs up the primary databases transaction log every hour to a file share on the primary
  • A job on the secondary server which copies new backups from the primary to the secondary every hour
  • A job on the secondary which restores new transaction log backups to the secondary database every hour

The jobs clean up old backup files to conserve space, I set the threshold to 72 hours.  They also send alerts if things don't happen in a timely fashion.  I set the alert threshold to 2 hours.

So in a couple of hours I was able to create a hot standby of my production database on a separate server that gets refreshed hourly.  I can start building my operational reports against the secondary database without slowing down the production system, and from an operations perspective we have an up-to-date version of our production database staged on a separate server for disaster recovery.

My next task will be to start building my first operational reports, but that's not going to happen tonight because my flight is here.  Whew!