SQL Server “Denali” HADR

Continuing with the SQL Server Denali blogs , I thought I’d take a look at the new high availability disaster recover, or HADR, functionality.

With SQL Server 2005, Microsoft introduced Database Mirroring. Prior to this we had a system called Log Shipping which was essentially an automatic backup and recovery system. Log Shipping did supply a failover instance of SQL Server, but, by default, it replicated every 15 minutes, which could result in the loss of important data. Furthermore, Log Shipping had no automatic failover. With Database Mirroring we had a system which was easier to setup, allowed us to choose the level of availability against performance, and with SQL Native Client, had automatic failover. The only limitation was that you couldn’t query the mirror. This could be remedied by taking periodic snapshots and using these for reporting, however if you wanted a real-time reporting database to take the load off your principal instance, then database mirroring wasn’t the answer. The other limitation was that you only had one mirror for each database.

Five years on and what has changed? Well, we now have High Availability Disaster Recovery (HADR). HADR is mirroring on steroids. You can have multiple mirrors (or secondary databases) to provide improved availability and ,furthermore, we can query these mirrors. We also have availability groups. Each availability group can have multiple databases and we can now failover these databases together in their group rather than on database at time.

In the CTP version of Denali there are some limitations which will not exist in the final product. For now, we can only have one secondary database, but in the release version, we can have four. We also only have asynchronous mode which is the equivalent to high performance mode, but again this is expected to be supplemented in the release version. Failovers can only be forced, which has the potential for data loss and are not currently automatic, but again, this will change.

There are some caveats. Obviously, there is a load on the server to transfer the logs. The CTP release uses an asynchronous mode whereby the primary server does not wait until the secondary server confirms that it has written the log records to disk before the transaction is committed. This reduces load on the primary server, but does have the potential that an update could be lost if the primary server fails just after a transaction is committed, but before it has been written to the secondary server. With the final release there will be a synchronous mode, but you will need to decide between protection and performance.

Currently, there are not many tools beyond setup and Force Failover available in Management Studio, although there are the following PowerShell cmdlets allowing you to also suspend and resume availability group databases:

Add-SqlAvailabilityGroupDatabase
Join-SqlAvailabilityGroup
New-SqlAvailabilityGroup
New-SqlAvailabilityReplica
Remove-SqlAvailabilityGroup
Resume-SqlAvailabilityGroupDatabase
Suspend-SqlAvailabilityGroupDatabase
Switch-SqlAvailabilityGroup

Although this is much more complex to setup than database mirroring, it provides a genuine high availability solution that also improves performance by offloading query operations to a mirror server. It will be very interesting to see how this evolves in the final product, but from these beginnings, SQL Server HADR looks to be an excellent availability solution.