SQL Server Blogapalooza—Disaster Recovery in SQL Server Standard Edition

Spread the love

This blogging concept may or may not have been inspired by a party many of us attended last Monday, while at TechEd in New Orleans. There also may or may not have been a great deal of Red Bull and other beverages consumed at said party. Anyway, when we deferred to talking about databases, we came into the topic of disaster recovery options going forward in SQL Server standard edition. As some of you may know, since SQL 2005 SP1, database mirroring (synchronous, anyway) has been an option in standard edition. This allowed Standard Edition customers, to have some level of DR even if it wasn’t to the same standard as Enterprise Edition. However, since SQL Server 2012 where AlwaysOn Availability Groups were introduced, and database mirroring has been marked for deprecation (which generally means it will go away within two major releases of SQL Server). So our discussion Monday night was around, what should be the DR option for Standard Edition?

In my opinion, log shipping and replications aren’t really full on DR options. You are likely to lose some data, and any switchover process likely involves manual intervention to change a connection string. However, I feel like this is what Microsoft will probably offer customers as a DR option going forward. My reasoning for that is simple and is as follows—Oracle doesn’t offer a DR option in their Standard Edition.

Oracle

SQL Server

Two Node Cluster (No Addition Cost—unlike EE)

Two Node Cluster

No Block Level Recovery

No Online Page and File Restore

No Advanced Compression

No Data Compression

Limited Encryption

No Transparent Data Encryption

No Online Indexing Reorgs/Rebuilds

No Online Indexing/Rebuilds

No Partitioning

No Partitioning

No Fine Grained Auditing

No Fine Grained Auditing

No Parallel Index Rebuilds

No Parallel Index Rebuilds

No Materialized Views Query Rewrite (Index Views in Oracle)

No Automatic use of Indexed Views by Optimizer

Limited Backup Compression

Backup Compression

No DataGuard

Synchronous Database Mirroring* (Currently)

No Transportable Tablespaces

N/A

–source Oracle 11G product family, SQL Server BoL

As you’ll note from the above table—if you aren’t sleeping from discussing licensing minutia yet, Oracle and SQL Server match each other almost feature for feature in Standard Edition. The one glaring difference is that SQL Server currently gives us the option to have synchronous mirroring, where as Oracle has no online DR option in its standard edition. Since MS marketing drives most of these decisions, and nearly all of the enhancements to SQL Server in recent years have been to Enterprise Edition, I don’t see an option coming to replace Database Mirroring as a feature when it goes away.

So, what do I propose as a solution to Standard Edition customers to Microsoft? The ability to create a Hybrid Availability Group (I like this concept so much, I’ll be speaking about it at the PASS Summit in October)—if the customer is using Standard Edition, they are allowed to have one additional node, running SQL Server on a Windows Azure Virtual Machine. Since, the solution will be distant, it will need to be asynchronous, however the other limitation should be that the secondary can’t be used as a read only replica. This is a fair compromise that guarantees Microsoft some level of incremental revenue, and gives their Standard Edition customers some level of DR options. This would be a great win-win solution for MS and their customers.

 

 

3 thoughts on “SQL Server Blogapalooza—Disaster Recovery in SQL Server Standard Edition

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.