There have been lots of requests from community in Delhi to talk about HADR with SQL Server. Finally, we managed to get the event organized based on the theme of HADR using SQL 2016 AlwaysON. AlwaysON is in the market since SQL server 2012 but the recent enhancement of supporting the distributed/cross databases transactions has made this feature even more usable. Agenda of the event was as follows –
1. Basics of Windows Cluster and the quorum models – Yogesh Arora
2. Basics of AlwaysON FCI (SQL Cluster) and flexible failover policy using Sp_server_diagnostics – Satya Prakash
3. AlwaysON architecture and Internals – Udhay Bhanu Pathania
4. AlwaysON enhancements in SQL 2016 – Sumit Sarabhai
5. How Split-Brain Scenario is avoided by AlwaysON – Sumit Sarabhai
6. Setup a Geo Cluster – Harsh Chawla | Twitter , Blog
To cater to the entire audience, we started the session with the Windows clustering concepts. In that session, the discussion was around:
1. Purpose of windows cluster
2. Various Quorum models and how it helps with automatic failover :
- Node and File Share majority
- Node and Disk Majority
- Majority Node Cluster
- No Majority Cluster – Disk only
3. purpose of votes in the quorum. Refer this article for quorum models/votes.
After a brief discussion on Windows Cluster, there was a discussion on SQL Cluster and changes in SQL 2016 (post SQL 2008):
1. Flexible Failover Policy – Before SQL 2012, the failover used to be dependant on Isalive and Looksalive checks done by resource DLL and there was very less control with the administrator except changing the timeout values of the above two parameters. Starting SQL 2012, the administrators have more control as there’s an option of flexible failover policy. The resource availability is now being checked using sp_server_diagnostics and failover can be customized depending on the level selected.
For more information on flexible failover policy,please check – https://msdn.microsoft.com/en-us/library/hh710061.aspx
2. Starting SQL 2012 , we have AlwaysON (integrated solution for HADR) which has two parts:
1. AlwaysON FCI (Failover Cluster Instance) formerly known as SQL server failover Cluster
2. AlwaysON AG(Availability Group)
Just to setup the base, AlwaysON FCI is a SQL Cluster as HA solution which needs shared storage to function. The disk ownership stays with the primary node and as soon as the failover happens, the storage ownership changes to the current active node.
3. Another enhancement in AlwaysON FCI is, Multi-subnet cluster. With SQL server 2008 R2 FCI – there was no option for cluster based on IPs from the different subnets. To build Multi-site cluster, stretched V-LAN was required. All the nodes from the cluster had to be mapped to this V-LAN. After the failover the same IP address was taken over by the node in the different subnet. However, SQL 2012 onwards, multi-subnet cluster is supported with multiple IPs and the resource dependency for the virtual network name can use ‘OR’ relationship on these IPs.
4. Clustering on SMB fileshare and CSV storage – SAN is not the only option for shared storage for AlwaysON FCI now, SMB fileshare and CSV storage can also be used to build the SQL cluster.
5. Domain independent availability groups – Starting SQL 2016, SQL cluster nodes can reside on different domains or even no domains at all using Windows 2016.Imagine the cluster in workgroups that’s really cool.
Please find the PPTs for this session attached. I will write about AlwaysON in the next post.