The below is an extract from my presentation at SQL Saturday 252 on high availability and disaster recovery. This post covers the basics of SQL Server log shipping.
Log shipping is the natural extension of relying on backups alone, because it’s a process which automates the restoration of your backups. It’s basically a restore of your full backup on the day you establish log backups followed by a series of log backup restores. So on your primary server you are going to take log backups at a set interval(the default is every 15 minutes) and your secondary has a job which will periodically check for any new log backups and copy them from the primary to the secondary server. A third job will then look at the settings and restore any appropriate transaction logs. So we are achieving a few things here. Not only have we set up a warm standby of the production server which is up to date within 15-30 minutes, we are also actively confirming our log backups are usable, because if they are not log shipping will break.
One of the things that makes log shipping such an attractive DR option is that it is incredibly easy, and in most cases FREE! Why do I say most cases? Well, you basically have a passive SQL license available to you for each licensed instance, provided that it is a purely passive server for disaster recovery purposes. I’ll throw out my standard disclaimer that I’m not Microsoft, and don’t sell their licenses for them, so check with your licensing guys, because there are a few gotchas.
For all it’s simplicity, log shipping gives us a lot of flexibility. Firstly, you can logship to multiple servers. Secondly you have the option to restore the database in standby mode which allows you to report off it. Thirdly you can put in a load delay, so the secondary won’t load logs until, say, 8 hours have passed(You can configure this entirely to your own requirements). And fourthly – you can combine all the above things together to suit both your disaster recovery and business requirements. So you can have one production database taking it’s backups each 15 minutes. Then you can have a pure DR secondary which restores a copy of the database and is the target for a failover. Then add another secondary on your BI server and set that up so it’s only running restores at 8 o’clock in the morning, but doing it in read only so it can act as a data source for your BI reports. So BI is now accessing all the data in your prod database, up to the current day – and it’s not messing up production.
And because you have a load delay, when your rogue developer hits F5 in the wrong window and updates the account balance of everyone to $100(because he forgot he commented the where clause and he thought he was on the dev box anyway) – well, you can bring that standby online and grab that table data and take it back to production.
You can even logship to the same server as the production database, because logshipping allows you to rename the destination database to whatever you want. Why would you do that? Well it allows you to avoid locking issues by reporting off a second database.
There’s a few gotchas though. Firstly log shipping relies on your recovery chain being intact. If someone takes a single log backup that isn’t available to the secondary then logshipping is going to break. Remember that log shipping is just the process of going through a regular restore. If you lose a log backup out of your regular recovery chain then you can’t restore further. Of course, personally I’d prefer find out some rogue 3rd party application is taking random log backups by log shipping falling over than in a live disaster situation. Setting up log shipping has actually revealed exactly this issue on more than one client site I’ve dealt with.
Secondly – if you want to use the secondary server to report off then it is considered a production server for licensing purposes. You can also have only one passive standby license. So while you can theoretically have as many logshipped secondaries as you like, after the first one you are paying for them all.