Why Log Shipping Will Live Forever, or at least another few years

I’ve worked with SQL Server in the service provider industry for at least 5 years now, an environment where we own the server, put it in our data centre, power, feed and water the server, the operating system and some of the apps, before the client installs their data and apps on top.  For a lot of businesses this works well for SQL Server as they don’t need to have 24/7 DBA support for what compared to some other services is quite a high touch application. 

 

Most clients like their database systems to be available, highly available in fact, and to make sure they are they impose tough service level agreements, SLAs, to their service providers to make sure they’re always available.  Therefore, one of the key processes we often implement is something which gets database changes off of a single piece of hardware/server/SAN so we have recently up-to-date copies of their data in more than one place.  When I first started doing this we used our own log shipping system, a robust series of tables and stored procedures which got data off of the primary server, restored to a secondary server and once a day ran DBCC checks on the secondary.  In SQL Server 7 nothing was provided by Microsoft hence we had to make our own.  In SQL Server 2000 there was some log shipping functionality appearing but we liked our own so much that we kept using it.

 

Along came SQL Server 2005 with database mirroring.  A new version of log shipping that worked on a per-transaction basis and which had a nice GUI interface, could perform a type of failover itself and was easier to maintain than our homemade system.  The big problem however was this new term, “Safety On”.  While we wanted to keep getting changes copied from the primary to the secondary database server we didn’t want transactions on the primary to wait until they were committed on the secondary before they completed.  The end user experience could suddenly become dependant on our backup server committing the changes quickly enough! 

 

Microsoft allowed you to get around this by enabling “Safety Off”, the primary would not wait for the secondary to commit the change before reporting back to the user’s application that the transaction was complete.  The BIG problem was that this option was only available in the Enterprise Edition.  So, unless you were in a mission critical environment where you needed essentially a two-phase commit solution you had to buy the expensive version of SQL Server to use the new database mirroring features.  The same happened in SQL Server 2008, Enterprise Edition only.  Kind of odd, the reduced functionality is only available in a premium edition.

 

The challenge comes when the cost difference between Standard and Enterprise editions is as massive as it is, especially when there’s only one piece of Enterprise Edition functionality we want to use.  In the service provider world the monthly cost for our clients of Enterprise Edition is quite a few times more than that of Standard Edition.  As a result, no one buys Enterprise Edition just to use database mirroring, instead we use tired and tested log shopping, in 2010, with SQL Server 2008 R2, and probably in whatever Denali is called when it’s released too.

 

What’s even more disappointing is that the new HADR (High-availability Disaster-Recovery) feature in the next version of SQL Server, Denali, is described in Books Online as an “enterprise-level high availability and disaster recovery solution”.  On that text alone I suspect no one I deal with will pay significantly larger licensing costs just to do something fairly similar to what log shipping has been doing for 10 years, while they accept they’re missing out on potentially useful new additional features.

It seems very odd, especially when you consider you can cluster with SQL Server standard edition these days.

Advertisements

One thought on “Why Log Shipping Will Live Forever, or at least another few years

  1. Pingback: World Spinner

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s