Read-Scale Availability Group Setup
What is a Read-Scale Availability Group?
A Read-Scale Availability Group is a Clusterless Availability Group. It's sole purpose and design is to scale out a read workload. More importantly is what it is not. It is NOT a High Availability solution. Since this design has no cluster under it, you lose things like automatic failover and database level health detection. An example use case is maybe you have reports that run for customers that are in your DMZ that is fire-walled off from your internal network. Opening up ports for Active Directory so that you can have a cluster means opening a ton of ephemeral ports and ports with high attack vectors. Remember the Slammer worm? This solution removes those dependencies.
Requirements
What you need to create this solution is pretty simple. You need Windows Server 2016 or greater. You also need SQL Server 2017 or greater.
Pre-requisites
We do have a few pre-requisites that must be met.
- We need local accounts on each node that have identical names and passwords
- The local accounts need to be members of the local administrators group
- We need to set the LocalAccountTokenFilterPolicy in the registry
- This only needs to be done when using a non-builtin local administrator account
- Primary DNS Suffix must be configured
Setup
Here is a video where I walk through creating a Read-Scale or Clusterless Availability Group.
https://www.youtube.com/watch?v=UdB8G9YCxgQ
Code
All of the code below is what I used in the above video in my test environment. You should test this code prior to using it and you assume all responsibility as it is only provided for demonstration purposes. You will need to run this on each replica you plan to add to your Availability Group.
[powershell]
#First we have to create a local user with the same name and password on each node.
#The account is required to be in the local Adminstrators group
$Password = Read-Host -AsSecureString
New-LocalUser "WSFC" -Password $Password -FullName "WSFC" `
-Description "Used for Domain Independent Cluster" `
-PasswordNeverExpires -UserMayNotChangePassword
Add-LocalGroupMember -Group "Users" -Member "WSFC"
Add-LocalGroupMember -Group "Administrators" -Member "WSFC"
#Next we have to set the LocalAccountTokenFilterPolicy in the registry since we are not using a built-in account
new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
#Next we need to make sure the machine has a primary DNS Suffix
#This should NOT be required on domain joined machines since the domain name is automatically set as the PDS
#Only run this on workgroup "Non-Domain joined machines"
$DNSSuffix = "independent.com"
Set-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\" -Name Domain -Value $DNSSuffix
Set-ItemProperty "HKLM:\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\" -Name "NV Domain" -Value $DNSSuffix
#We changed the primary DNS Suffix so we need to reboot for the change to take affect
Restart-Computer -ComputerName $env:computername -Force
[/powershell]
Run this on each replica to create an endpoint that is protected by a certificate and backs the certificate up to a share. The original code came from Kevin Farlee and can be found in THIS POST.
[sql]
/*
— This procedure automates the creation of a local certificate and the endpoints required for a domainless AG.
— Parameters are the strong password for the cert, and the location of a share which receives the backup of the cert.
— The share should be accessible to all nodes in the AG, as they will need to read the certs for each other.
— The procedure also creates the endpoint based upon the newly created cert.
— https://blogs.technet.microsoft.com/dataplatforminsider/2015/12/15/enhanced-always-on-availability-groups-in-sql-server-2016/
EXEC CreateEndpointCert '\\demo2\temp', '1R3@llyStr0ngP@ssw0rd!'
*/
DROP PROCEDURE IF EXISTS CreateEndpointCert;
GO
CREATE PROCEDURE CreateEndpointCert
@ShareName SYSNAME ,
@StrongPassword SYSNAME
AS BEGIN
--This must be executed in the context of Master
IF (DB_NAME() <> 'master')
BEGIN
PRINT N'This SP must be executed in master. USE master and then retry.'
RETURN (-1)
END
DECLARE @DynamicSQL varchar(1000);
DECLARE @CompName varchar(250);
DECLARE @HasMasterKey INT;
SELECT @CompName = CONVERT(SysName, SERVERPROPERTY('MachineName'));
-- Only create a master key if it doesn't already exist
SELECT @HasMasterKey = is_master_key_encrypted_by_server from sys.databases where name = 'master'
IF (@HasMasterKey = 0)
BEGIN
--Create a MASTER KEY to encrypt the certificate.
SET @DynamicSQL = CONCAT('CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' , QUOTENAME(@StrongPassword, ''''));
EXEC (@DynamicSQL)
END
--Create the certificate to authenticate the endpoint
IF EXISTS (SELECT name from sys.certificates WHERE name = QUOTENAME(@CompName + '-Cert'))
BEGIN
SET @DynamicSQL = CONCAT('DROP CERTIFICATE ', QUOTENAME(@CompName + '-Cert'));
EXEC (@DynamicSQL);
END
SET @DynamicSQL = CONCAT('CREATE CERTIFICATE ', QUOTENAME(@CompName + '-Cert'), ' WITH SUBJECT = ', QUOTENAME(@CompName, '''')) ;
EXEC (@DynamicSQL);
--Create the database mirroring endpoint authenticated by the certificate.
SET @DynamicSQL =
CONCAT('CREATE ENDPOINT AlwaysOn_EP
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE ',QUOTENAME(@CompName + '-Cert'), ' , ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL)');
EXEC (@DynamicSQL);
--Back up the certificate to a common network share for import into other nodes in the cluster
SET @DynamicSQL = CONCAT('BACKUP CERTIFICATE ',QUOTENAME(@CompName + '-Cert'),' To FILE = ', QUOTENAME( @ShareName + '\SQL-' + @CompName + '.cer', ''''));
EXEC (@DynamicSQL);
END
GO
[/sql]
This will install the certificate of the other replicas on the replica where you run it. This will need to be run on each replica and run once for every other replica in the Availability Group. If we have Node 1 and Node 2 then we are installing the Node 1 certificate on Node 2 and vice versa.
[sql]
/*
— This procedure assumes that a certificate has been created on another node in the AG, and backed up to a common network share.
— Parameters:
— @CompName – The name of the computer whose certificate needs to be installed here. i.e. the other replica that this node needs to communicate with.
— @ShareName – A common network share to which certificates were backed up from each machine in the cluster/AG.
— @StrongPassword – A strong password to be used for the login created to log in on behalf of the endpoint on the other node.
—
— This procedure assumes that each node has run CreateEndpointCert and that all cert backup files reside on the share pointed to by the second parameter.
— The procedure creates a login and a user for the remote machine, and then created a certificate to authorize the user when the certificate is used as authentication from the remote endpoint.
-- https://blogs.technet.microsoft.com/dataplatforminsider/2015/12/15/enhanced-always-on-availability-groups-in-sql-server-2016/
EXEC dbo.InstallEndpointCert 'Server1', '\\demo2\temp', '1R3@llyStr0ngP@ssw0rd!'
EXEC dbo.InstallEndpointCert 'Demo2', '\\demo2\temp', '1R3@llyStr0ngP@ssw0rd!'
*/
DROP PROCEDURE IF EXISTS [dbo].[InstallEndpointCert];
GO
CREATE PROCEDURE [dbo].[InstallEndpointCert]
@CompName SYSNAME,
@ShareName SYSNAME,
@StrongPassword SYSNAME
AS BEGIN
DECLARE @DynamicSQL varchar(1000);
DECLARE @MyCompName varchar(250);
SELECT @MyCompName = CONVERT(SysName, SERVERPROPERTY('MachineName'));
--Don't need to create LOGINs for the local system
IF (@MyCompName <> @CompName)
BEGIN
IF EXISTS (SELECT name from sys.certificates WHERE name = QUOTENAME(@CompName + '-Cert'))
BEGIN
SET @DynamicSQL = CONCAT('DROP CERTIFICATE ', QUOTENAME(@CompName +'-Cert'));
EXEC (@DynamicSQL);
END
SET @DynamicSQL = CONCAT('DROP USER ', QUOTENAME( @CompName + '-User'));
EXEC (@DynamicSQL);
--SET @DynamicSQL = CONCAT('DROP LOGIN ', QUOTENAME (@CompName + '-Login'));
--EXEC (@DynamicSQL);
SET @DynamicSQL = CONCAT('CREATE LOGIN ', QUOTENAME (@CompName + '-Login'), ' WITH PASSWORD= ', QUOTENAME( @StrongPassword, ''''));
EXEC (@DynamicSQL);
SET @DynamicSQL = CONCAT('CREATE USER ', QUOTENAME( @CompName + '-User'), ' FOR LOGIN ', QUOTENAME(@CompName + '-Login'));
EXEC (@DynamicSQL);
SET @DynamicSQL = CONCAT('CREATE CERTIFICATE ', QUOTENAME(@CompName +'-Cert'), ' AUTHORIZATION ', QUOTENAME(@CompName +'-User'), ' FROM FILE = ', QUOTENAME(@ShareName + '\SQL-' + @CompName + '.cer' , ''''));
EXEC (@DynamicSQL);
SET @DynamicSQL = CONCAT('GRANT CONNECT ON ENDPOINT::AlwaysON_EP TO ', QUOTENAME(@CompName +'-Login'));
EXEC (@DynamicSQL);
END
END
GO
[/sql]
This code will create the Availability Group. It must be run in SQLCMD mode.
[sql]
/*
Author: Ryan Adams
Website: https://www.ryanjadams.com
Twitter: @ryanjadams
This script must be executed in SQLCMD mode. This script was designed to setup an AlwaysOn Availability Group
in a custom lab environment. Use at your own risk and DO NOT run this in production. Make sure you read and understand
it thoroughly.
*/
/*
First we connect to each replica in order and create logins for the account running the SQL Server Service on the other replicas
Next we connect to each replica and create the endpoint.
We also grant connect permission on the endpoint to all of the other replica service accounts.
In a clusterless AG this must be done using certificates. Use the following scripts to do that.
CreateEndpointCert.sql
InstallEndpointCert.sql
*/
/* Here we connect to each replica and ensure that the AlwaysOn extended events session is set to run at startup and that it is also started */
:Connect DEMO2
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect SERVER1 -U MyAdmin -P NotGettingThisPassword
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
/*
There will be 1 database included in our AG and it currently exists on DEMO2.
Prior to SQL 2016 we would have to do the following:
Backup each database data file to a share and then restore it on each replica making sure to use the "NORECOVERY" clause
Backup each database log file to a share and then restore it on each replica making sure to use the "NORECOVERY" clause
With SQL 2016 and above we can use Direct Seeding instead.
*/
/* Here we connect to our primary replica (DEMO2) and create our AG.
Note the CLUSTER_TYPE = None and the FAILOVER_MODE = MANUAL */
:Connect DEMO2
/* We can use trace flag 9567 to enable compression for the VDI backup for the seeding process */
DBCC TRACEON (9567, -1);
GO
USE [master];
GO
CREATE AVAILABILITY GROUP [MyAG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, FAILURE_CONDITION_LEVEL = 3, HEALTH_CHECK_TIMEOUT = 30000
, DB_FAILOVER = ON, DTC_SUPPORT = NONE, CLUSTER_TYPE = None) /* You can also use SECONDARY_ONLY, PRIMARY, or NONE for backup preference
Failure condition levels are from the least restrcitive of 1(SQL Service is down) to 5(Exhaustion of worker threads). 3 is the default.
Health check default is 30 seconds, represented in milliseconds. This is how long we wait for sp_server_diagnostics to return. */
FOR DATABASE [DomainIndependent]
REPLICA ON N'DEMO2' WITH (ENDPOINT_URL = N'TCP://DEMO2.independent.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = Read_Only), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC),
N'SERVER1' WITH (ENDPOINT_URL = N'TCP://SERVER1.americans.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 60, SECONDARY_ROLE(ALLOW_CONNECTIONS = Read_Only), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC);
GO
/* Now that the AG exists we connect to each secondary replica and join it to the group
We also have to grant the CREATE ANY DATABASE permission so Seeding can create the DBs
Notice the "WITH(CLUSTER_TYPE = NONE)" clause that is new */
:Connect SERVER1 -U MyAdmin -P NotGettingThisPassword
ALTER AVAILABILITY GROUP [MyAG] JOIN WITH (CLUSTER_TYPE = NONE);
GO
ALTER AVAILABILITY GROUP [MyAG] GRANT CREATE ANY DATABASE;
GO
/* This query let's us view the progress of the seeding operation
SELECT start_time,
ag.name,
db.database_name,
current_state,
performed_seeding,
failure_state,
failure_state_desc
FROM sys.dm_hadr_automatic_seeding autos
JOIN sys.availability_databases_cluster db ON autos.ag_db_id = db.group_database_id
JOIN sys.availability_groups ag ON autos.ag_id = ag.group_id;
GO
SELECT * FROM sys.dm_hadr_physical_seeding_stats;
GO
*/
/* Prior to SQL 2016 we had to connect to each secondary replica and add our DBs to the group.
With seeding we no longer have to do this as it is joined during the seeding process
:Connect SERVER1 -U MyAdmin -P NotGettingThisPassword
ALTER DATABASE [MyAG_DB1] SET HADR AVAILABILITY GROUP = [MyAG];
ALTER DATABASE [MyAG_DB2] SET HADR AVAILABILITY GROUP = [MyAG];
ALTER DATABASE [MyAG_DB3] SET HADR AVAILABILITY GROUP = [MyAG];
GO
*/
/* Now we need to turn our trace flag back off */
:Connect DEMO2
DBCC TRACEOFF (9567, -1);
GO
[/sql]
Resources
This solution is designed to scale out reads using an Availability Group without the requirement of a cluster. As discussed at the beginning of the article, you lose some things. What about Read-Only Routing, though? You can still use this, but the configuration is a bit different. To learn how to configure Read-Only Routing in a Read-Scale Availability Group read THIS POST by Sean Gallardy.
You might also be interested in Domain Independent Availability Groups. That architecture retains the use of a cluster, but the nodes no longer have to be in a domain or they can even be in different domains. Read Domain Independent Clusters for more information.
The other thing most folks find challenging with Availability Groups are how to sync users and server level objects. Check out Sync SQL Logins for more information.