A couple of days ago, Microsoft has rolled out the SQL Server vNext CTP 1.3 which includes very nice features and the one that interested me in the supportability of AlwaysOn availability groups on Linux. That is definitely a good news because we may benefit from new ways of architecting high-availability. There are a lot of new cool enhancements, which we’ll try to study when writing new blog posts. In this first blog, let’s learn new available configuration options.
First of all, let’s say that configuring availability groups on Linux is not different than configuring them on Windows in workgroup mode (domainless configuration) from SQL Server perspective.
Basically, the same steps remain as it is (please refer to the Microsoft installation documentation):
- Creating login and users on each replica
- Creating certificate and grant authorization to the corresponding user on each replica
- Creating endpoint for data mirroring and grant permission connected to the corresponding certificate
So let’s just set the scene before moving forward on the other installation steps. I used for my demo two virtual machines on Hyper-V which run on Linux CentOS 7 (LINUX02 and LINUX04) . I also installed two SQL Server instances (CTP 1.3) on each machine which will run on the top of the cluster infrastructure with Pacemaker and Corosync.
Obviously this time the NFS server is not part of this infrastructure and this time I used a symmetric storage on each virtual machine which includes two mount points and two ext4 partitions (respectively /SQL_DATA and /SQL_LOG to host my AdventureWorks2012 database files).
[[email protected] ~]$ cat /etc/fstab /dev/mapper/cl-root / xfs defaults 0 0 UUID=d6eb8d27-35c7-4f0f-b0c1-42e380ab2eca /boot xfs defaults 0 0 /dev/mapper/cl-swap swap swap defaults 0 0 /dev/sdb1 /sql_data ext4 defaults 0 0 /dev/sdc1 /sql_log ext4 defaults 0 0
SELECT name AS logical_name, physical_name FROM sys.master_files WHERE database_id = DB_ID('AdventureWorks2012')
First step to enable the AlwaysOn feature: we have to use the mssql-conf tool (which replaces the famous SQL Server configuration manager) with the new option set hadrenabled 1
Then if we use the Linux firewall on each machine, we have to open the corresponding TCP endpoint port on the Linux firewall on each Linux machine.
[[email protected] data]firewall-cmd --zone=public --add-port=5022/tcp --permanent success [[email protected] data]firewall-cmd –reload success [[email protected] data]firewall-cmd --permanent --zone=public --list-all public target: default icmp-block-inversion: no interfaces: sources: services: dhcpv6-client high-availability mountd nfs rpc-bind ssh ports: 1433/tcp 5022/tcp protocols: masquerade: no forward-ports: sourceports: icmp-blocks: rich rules: [[email protected] ~]firewall-cmd --zone=public --add-port=5022/tcp --permanent success [[email protected] ~]firewall-cmd --reload
No really new stuff so far … The most interesting part comes now. After installing SQL Server on Linux and achieving endpoint configurations, it’s time to create the availability group. But wait, at this stage we didn’t install any clustering part right? And in fact, we don’t have to do this. We are now able to create an availability group without any cluster dependencies by using a new T-SQL parameter CLUSTER_TYPE = NONE as follows. Very interesting because we may think about new scenarios where only read-scalability capabilities are considered on DR site. In this case we don’t have to setup additional cluster nodes which may lead to manageability overhead in this case.
We may also use the direct seeding mode feature available since SQL Server 2016 to simplify the process of adding a database in the corresponding availability group (AdventureWorks2012 database in my case).
:CONNECT LINUX02 -U sa -P Xxxxx CREATE AVAILABILITY GROUP [agLinux] WITH ( DB_FAILOVER = ON, --> Trigger the failover of the entire AG if one DB fails CLUSTER_TYPE = NONE --> SQL Server is not a member of a Windows Server Failover Cluster ) FOR REPLICA ON N'LINUX02' WITH ( ENDPOINT_URL = N'tcp://192.168.5.18:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC, SEEDING_MODE = AUTOMATIC, --> Use direct seeding mode SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL) ), N'LINUX04' WITH ( ENDPOINT_URL = N'tcp://192.168.5.20:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC, SEEDING_MODE = AUTOMATIC, --> Use direct seeding mode SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL) ); GO ALTER AVAILABILITY GROUP [agLinux] GRANT CREATE ANY DATABASE; GO :CONNECT LINUX04 -U sa -P Xxxxx ALTER AVAILABILITY GROUP [agLinux] JOIN WITH (CLUSTER_TYPE = NONE) ALTER AVAILABILITY GROUP [agLinux] GRANT CREATE ANY DATABASE; GO :CONNECT LINUX02 -U sa -P Xxxxx ALTER DATABASE [AdventureWorks2012] SET RECOVERY FULL; GO BACKUP DATABASE [AdventureWorks2012] TO DISK = 'NUL'; GO -- Add the AdventureWorks2012 database to the agLinux AG ALTER AVAILABILITY GROUP [agLinux] ADD DATABASE [AdventureWorks2012]; GO
After configuring the agLinux availability group let’s have a look at the different DMVs I usually use in this case:
-- groups info SELECT g.name as ag_name, rgs.primary_replica, rgs.primary_recovery_health_desc as recovery_health, rgs.synchronization_health_desc as sync_health FROM sys.dm_hadr_availability_group_states as rgs JOIN sys.availability_groups AS g ON rgs.group_id = g.group_id -- replicas info SELECT g.name as ag_name, r.replica_server_name, rs.is_local, rs.role_desc as role, rs.operational_state_desc as op_state, rs.connected_state_desc as connect_state, rs.synchronization_health_desc as sync_state, rs.last_connect_error_number, rs.last_connect_error_description FROM sys.dm_hadr_availability_replica_states AS rs JOIN sys.availability_replicas AS r ON rs.replica_id = r.replica_id JOIN sys.availability_groups AS g ON g.group_id = r.group_id -- DB level SELECT g.name as ag_name, r.replica_server_name, DB_NAME(drs.database_id) as [database_name], drs.is_local, drs.is_primary_replica, synchronization_state_desc as sync_state, synchronization_health_desc as sync_health, database_state_desc as db_state FROM sys.dm_hadr_database_replica_states AS drs JOIN sys.availability_replicas AS r ON r.replica_id = drs.replica_id JOIN sys.availability_groups AS g ON g.group_id = drs.group_id ORDER BY g.name, drs.is_primary_replica DESC; GO
Here the corresponding output:
Ok everything seems to be ok. We have configured an availability group which includes two replicas and synchronous replication so far. But maybe you have already noticed we didn’t create any listener and the reason is pretty obvious: if we refer to previous versions of SQL Server, creating a listener requires to create a corresponding CAP on the WSFC side and in our context, no cluster exists at this stage. So go ahead and let’s do it. You may refer to the Microsoft documentation to add the pacemaker cluster to the existing infrastructure.
I didn’t expect a big change compared to my last installation for SQL Server FCI on Linux for the basics but for the integration of the AG resource it will probably be another story. Indeed, the declaration of the resource (ocf:mssql:ag) is completely different compared to the SQL Server FCI (ocf::sql:fci). In this case, we have to create a multi-state resource. Multi-state resources are specialized clone resources which may be in one of two operating modes – master and slave. We run exactly on the same concept with availability groups because we consider to use one primary replica (master) and at least one secondary (slave). According to the Microsoft documentation we have to setup meta-properties which limit the number of resource copies (on replicas) as well as the number of master resources (only one primary replica at time in our case).
Update 20.04.2017 – Removing of pcs command because it keeps changing with new CTPs. Please refer to the Microsoft documentation to get the last update of the command.
Creating a multi-state resource generates two separate resources regarding their role. In my case, I will get respectively sqllinuxaag-master (for the resource enrolled as Master resource) and sqllinuxaag-slave (for resource(s) enrolled as Slave resource(s)).
Update 20.04.2017 – Removing of pcs command because it keeps changing with new CTPs. Please refer to the Microsoft documentation to get the last update of the command
Finally, we have to create and associate a virtual IP address as follows. The virtual IP must run at the same location than the master resource in our case (INFINITY is mandatory here)
[[email protected] ~]$ sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=192.168.5.30 [[email protected] ~]$ sudo pcs constraint colocation add virtualip sqllinuxaag-master INFINITY with-rsc-role=Master
Here a recap of the existing constraints which concern my availability group resource:
[[email protected] ~]$ sudo pcs constraint Location Constraints: Ordering Constraints: promote sqllinuxaag-master then start virtualip (kind:Mandatory) Colocation Constraints: virtualip with sqllinuxaag-master (score:INFINITY) (with-rsc-role:Master) Ticket Constraints:
We may notice the global state of the resources and their roles (Master/Slave)
[[email protected] ~]$ sudo pcs status Cluster name: clustlinuxag Stack: corosync Current DC: linux04.dbi-services.test (version 1.1.15-11.el7_3.2-e174ec8) - partition with quorum Last updated: Mon Feb 27 06:16:03 2017 Last change: Mon Feb 27 06:14:11 2017 by root via cibadmin on linux02.dbi-services.test … Full list of resources: Master/Slave Set: sqllinuxaag-master [sqllinuxaag] Masters: [ linux02.dbi-services.test ] Slaves: [ linux04.dbi-services.test ] virtualip (ocf::heartbeat:IPaddr2): Started linux02.dbi-services.test
So, now let’s perform some failover tests. I used a basic PowerShell script to connect to my availability group by using the sqllinuxaag resource and return a response (OK and the server name of the concerned replica or KO). But let’s say the resource is not considered as a listener from the availability group. No listener exists at this stage.
- First test:
The first test consisted in switching manually over the sqllinuxaag (master) to the next available node (LINUX02). At this stage, we can’t use neither the wizard nor T-SQL statement to trigger a failover event. This is a limitation (explained by Microsoft) and I expect to see it to disappear in the future. Keeping the control of such action from SQL Server side will make more sense for DBAs.
[[email protected] ~]$ sudo pcs resource move sqllinuxaag-master linux02.dbi-services.test --master
During the failover event, no way to reach out the resource but the situation went back into normal as expected.
- Second test
The second test consisted in simulating “soft” failure by changing the state of the new active node (LINUX02) to standby in order to trigger a switch over the next available cluster node (LINUX04). But before going on this way, let’s configure stickiness to avoid unexpected failback of the sqllinuxaag-master resource when the situation will go back to normal.
[[email protected] ~]$ sudo pcs resource defaults resource-stickiness=INFINITY [[email protected] ~]$ sudo pcs cluster standby linux02.dbi-services.test [[email protected] ~]$ sudo pcs status Cluster name: clustlinuxag Stack: corosync Current DC: linux02.dbi-services.test (version 1.1.15-11.el7_3.2-e174ec8) - partition with quorum Last updated: Sun Feb 26 18:55:01 2017 Last change: Sun Feb 26 18:54:42 2017 by root via crm_attribute on linux04.dbi-services.test 2 nodes and 3 resources configured Node linux02.dbi-services.test: standby Online: [ linux04.dbi-services.test ] Full list of resources: Master/Slave Set: sqllinuxaag-master [sqllinuxaag] Masters: [ linux04.dbi-services.test ] Stopped: [ linux02.dbi-services.test ] virtualip (ocf::heartbeat:IPaddr2): Started linux04.dbi-services.test
Same result than previously. During the failover event, the resource was unreachable but after the situation went back to normal, the application was able to connect again.
We may also confirm the state of the availability group by using usual DMVs. The replica role has switched between replicas as expected and the synchronization state remained in healthy state.
- Third test
My third test consisted in simulating a network outage between my two cluster nodes and the infrastructure responded well and performed the necessary tasks to recover the situation.
It was a quick introduction to the new capabilities offered by the SQL Server vNext in terms of HA and availability groups. Other scenarios and tests as well will come soon. In any event, availability groups feature is very popular as well as Linux in my area and getting the way to mix the both will probably be a good argument for customer adoption. We will see in a near future!
By David Barbarin