SQL Server 2017: Read Scale-Out Availability Group

SQL Server 2017 continues to improve on what Availability Groups already provide for us. In this blog we are going to have a quick look at Read Scale-Out Availability Groups or Cluster-less Availability Groups. Traditionally since the release of SQL Server 2012, to build an Availability Group we need the underlying technology of a Windows Server Failover Cluster (WSFC). However with this new version, this now changes, giving us the ability to create an Availability group on either workgroup servers or domain connected servers without having created the underlying WSFC. How is this so? Why would we want to create a Cluster-less / Read Scale-Out Availability Group?

One of the principals of Availability Groups from the initial release back in SQL Server 2012 was for the ability to have a readable secondary to spread the workload and utilize the resources of the servers that are required in the built WSFC. Now SQL Server 2017 takes that 1 step further. But there is a massive caveat you need to be aware of before you think this will solve all your problems. This is NOT a High Availability (HA) / Disaster Recovery (DR) solution. This enhancement has been included to allow for the scale out of read-only workloads for environments that have heavy workloads, and could benefit from having a secondary copy of the data to access in real-time.

What do we get?

• No WSFC
• No failure detection to instigate an Automatic Failover (No HA/DR)
• No Availability Group Listener
• Manual Failover
• Readable Secondary
• Direct connection to the Readable Secondary
• Probably an Enterprise Edition feature

If you are looking at building this Cluster-less/Read Scale-out Availability Group, in a workgroup environment you will need to utilize certificates for the instances to be able to communicate. For how to implement certificates on a Workgroup Availability Group configuration have a read of Configuring Availability Groups to use certificates.  With the Create Availability Group statement, we need to use the new command option – “Cluster_Type = NONE”

CREATE AVAILABILITY GROUP [SMC_WG_AG1]
WITH (
 AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
 DB_FAILOVER = ON,
 DTC_SUPPORT = NONE,
 CLUSTER_TYPE = NONE
)
FOR DATABASE [SMC_WG_AG_DB1]
REPLICA ON N'SMC-WG1-SQL01' WITH (
 ENDPOINT_URL = N'TCP://SMC-WG1-SQL01.SMC.local:5022', 
 FAILOVER_MODE = MANUAL, 
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
 BACKUP_PRIORITY = 50, 
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
 N'SMC-WG1-SQL02' WITH (
 ENDPOINT_URL = N'TCP://SMC-WG1-SQL02.SMC.local:5022', 
 FAILOVER_MODE = MANUAL, 
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
 BACKUP_PRIORITY = 50, 
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
);

GO

Congratulations you have now successfully created a Read Scale-Out Availability Group utilizing certificates to allow the instances of SQL Server to communicate with each other. All steps have been put together based on my demonstration environment. Ensure that you undertake this in a non-production environment before going near production.

There are some additional feature enhancements for Availability Groups in SQL Server 2017, and I will look at those in a separate post.