Way back in 2004, I attended a presentation by Greg Low on the shiny new features in SQL Server 2005. Star of the show was database mirroring. Like a 4 year old that had just eaten a box of juji-fruits and drank a pint of red cordial, I was running around in circles talking in tongues for the rest of the afternoon. Later that night, I started penning log shipping's obituary.
Of course, like all new features, it takes a little while for the marketing haze to clear and the limitations to emerge. As we all know, database mirroring has some important limitations when compared with log shipping, the primary ones being the inability to use the mirror for read purposes (without creating a snapshot) and the instant (or near instant) replication of unintentional modifications to the principal database. For these reasons, log shipping remains not only relevant, but necessary in enabling a number of important high availability architectures, not to mention its constant validation of the transaction log backup chain.
With this background in mind, I look with interest on how (and why) people choose mirroring over log shipping (and vice versa). In this context, I was interested to see a technical note released by the SQLCAT team recently. In it, they address some of the considerations for using the new log shipping
feature in SQL Server 2008 which enables sub minute log shipping. Using this feature, we can schedule the transaction log backup/copy/restore process to execute as frequently as every 10 seconds (In SQL Server 2005, the minimum was 1 minute, with the default being 15 minutes). 10 seconds? Are you serious?
Unfortunately, the SQLCAT team didn't elaborate on situations in which this would be useful, so I was forced to think for myself. Dammit...
If you need transaction logs shipped to the destination every 10 seconds, you're obviously trying to minimize potential data loss. In synchronous (high safety) mode database mirroring, the transaction latency is zero, so that's clearly meeting this objective, so why not just use database mirroring? Let's think this through in a little more detail.
In high safety mode mirroring, transactions will not commit on the principal until they're hardened to the transaction log on the mirror. As such, high throughput environments, or those with bursts of activity such as bulk loads or index rebuilds, may notice reduced transaction performance in this mode. This is a particular problem when the principal and mirror are connected over low speed and/or unreliable network connections. In addressing this problem, high performance mode mirroring is frequently used, either permanently, or during bursts of activity where performance is of prime concern. In this mode, transactions are sent to the mirror asynchronously, increasing performance at the risk of possible data loss. In some ways, this can be considered in a similar manner to a log shipping solution.
The "problem" with high performance mode mirroring is that it's only supported in the enterprise edition of SQL Server. For those running standard edition, the only option is high safety mode, bringing with it the problems described above. So, for environments with low speed or unreliable links, standard edition mirroring may not be an option, hence log shipping comes into play, and the understandable need to increase shipping frequency.
As the SQLCAT blog post points out, there are some important considerations when using sub minute log shipping, the most important ones being the CPU impact of backup compression, and the massive amount of SQL Server log messages; each log backup writes an entry to the SQL Server log. Every 10 seconds? That's 8,640 messages per day. Trace flag 3226 helps out in this regard.
I'm very interested in your opinions on other possible reasons for high frequency log shipping in place of database mirroring.
Cheers
PS: This post is dedicated to my gorgeous spunk of a wife, Jodster. 10 years married today. Thanks for being consistently beautiful and irresistibly delicious.
Source: www.rodcolledge.com
Subscribe to this blog!
Comments