Denali – Day 2: AlwaysON High Availability (aka Project HADRON )
AlwaysON Availability Group:
is another very important achievement done by development team project called “HADRON”. As the name implies AlwaysON is a High availability feature on Failure Clustering where it has option to make Secondary server as “Read-Only” purpose.
I am not going to discuss how failure cluster works or other high availability and disaster recovery functionality like Mirroring, Log shipping or Replication. But all these are having its own limitations in it.
Failure clustering: Need similar infrastructure of nodes and only one node would be active, we are wasting other nodes resources and cost.
Mirroring: Only one secondary is possible
Log shipping: no automatic failover and data not synchronized completely( data loss).
To overcome these problem now sql server 2012 introduced AlwaysON– Failure clustering Group where you can
- Can group together multiple databases for failover.
- No Shared-Storage required.
- Have up to 4 Secondary’s (2 synchronous-Commit, 2 asynchronous-commit )
- Synchronous-Commit : Transaction commits on both the primary and secondary replicas- supports automatic and manual failover. Should be on same datacenter.
- Asynchronous-Commit : Transaction commits on primary replica without waiting for Secondary-replica to commit – only manual failover is possible. Can be located at geographically different location.
- We can also setup “Disallow connection” at secondary and it will work as old FC
Following are some pre-requests for this:
- It requires Windows server 2008 R2 windows failover clustering installed on the server.
- Server cannot host AD domain services role
- All databases must be in “Full Recovery mode”
Active read Secondary can be used for reporting and Backup and it has setup
Snapshot isolation level is set to allow Reader/writer
Need to configure the following:
- Availability Replica: specify the primary and secondary replica
- Availability Databases : Group the databases.
- Availability Group Listeners: similar to Virtual Sql Server Name.
The status of AlwaysON can be monitor on “Availability group Dashboard“
AlwaysONFailure Cluster Instance(FCI):
on traditional Failure clustering with some additional enhanced features added to it.
No need to have multisubet/VLAN technology required.
Now Tempdb can be located on Local fast SSD, to provide greater performance.
Failover policy: you can add your custom failover policy.
You can combine AlwaysOn FCI and Availability group
Patch rollout is similar to sql server 2008 as first patch passive replica and finally failover and patch primary replica.
*I do not have Windows server 2008 R2 so could not practically configured it.
I will write another detail blog on it.