Build a SQL Cluster Lab Part 3

You are going to create a multi-subnet Availability Group in Part 3 of our series on how to build a SQL Cluster Lab. First you give the Cluster Name Object rights in Active Directory and then install your AG. You will also ensure the AG endpoints are using the dedicated network created for them in Part 1. Here are links to the other articles in the series.

Part 1 – Building the environment. Hyper-v VMs, Active Directory, DNS, and Routing and Remote Access Services
Part 2 – Windows Failover Clustering. Cluster validation, networks, and quorum
Part 3 – Availability Group. Dedicated network and AD permissions
Part 4 – Failover Cluster Instance. Creating shared storage using iSCSI and adding the storage to the cluster.

Create Databases Code

USE [master];
GO
 
DROP DATABASE IF EXISTS App1AG_DB1;
GO
 
DROP DATABASE IF EXISTS App1AG_DB2;
GO
 
DROP DATABASE IF EXISTS App1AG_DB3;
GO
 
CREATE DATABASE [App1AG_DB1]
 ON  PRIMARY 
( NAME = N'App1AG_DB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB1.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'App1AG_DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB1_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO
 
CREATE DATABASE [App1AG_DB2]
 ON  PRIMARY 
( NAME = N'App1AG_DB2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB2.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'App1AG_DB2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB2_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO
 
CREATE DATABASE [App1AG_DB3]
 ON  PRIMARY 
( NAME = N'App1AG_DB3', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB3.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'App1AG_DB3_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\App1AG_DB3_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB)
GO
 
 
--Full backup required to create an AG
BACKUP DATABASE [App1AG_DB1] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\App1AG_DB1.bak' WITH FORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
 
BACKUP DATABASE [App1AG_DB2] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\App1AG_DB2.bak' WITH FORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
 
BACKUP DATABASE [App1AG_DB3] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\App1AG_DB3.bak' WITH FORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO

Create Availability Group Code

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

/*
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 */
:Connect NODE1
 
USE [master]
GO
 
CREATE LOGIN [contoso\svc-sql2] FROM WINDOWS
GO
 
CREATE LOGIN [contoso\svc-sql3] FROM WINDOWS
GO
 
 
:Connect NODE2
 
USE [master]
GO
 
CREATE LOGIN [contoso\svc-sql1] FROM WINDOWS
GO
 
CREATE LOGIN [contoso\svc-sql3] FROM WINDOWS
GO
 
 
:Connect NODE3
 
USE [master];
GO
 
CREATE LOGIN [contoso\svc-sql1] FROM WINDOWS;
GO
 
CREATE LOGIN [contoso\svc-sql2] FROM WINDOWS;
GO
 
/* 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. */
:Connect NODE1
 
USE [master];
GO
 
CREATE ENDPOINT [AlwaysOn_EP]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.0.1.1)) /* Specify an IP value for LISTENER_IP to dedicate AlwaysOn traffic to its own NIC card */
    FOR DATABASE_MIRRORING (ROLE = ALL /* Cannot use Witness as that is for mirroring only.  Partner will work but that's really just for mirroring terminology */
        , AUTHENTICATION = WINDOWS NEGOTIATE /* Authentication can be Windows or Certificate of a combination in preference order.  Authorization can force NTLM or Kerberos. These are default values */
        , ENCRYPTION = REQUIRED ALGORITHM AES); /* Default algorithm is RC4, however RC4 is marked depracated. Use AES (GUI uses AES by default, but TSQL does not) */
GO
 
GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql2];
GO
 
GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql3];
GO
 
:Connect NODE2
 
USE [master];
GO
 
CREATE ENDPOINT [AlwaysOn_EP]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.0.1.2)) /* Specify an IP value for LISTENER_IP to dedicate AlwaysOn traffic to it's own NIC card */
    FOR DATABASE_MIRRORING (ROLE = ALL /* Cannot use Witness as that is for mirroring only.  Partner will work but that's really just for mirroring terminology */
        , AUTHENTICATION = WINDOWS NEGOTIATE /* Authentication can be Windows or Certificate of a combination in preference order.  Authorization can force NTLM or Kerberos. These are default values */
        , ENCRYPTION = REQUIRED ALGORITHM AES); /* Default algorithm is RC4, however RC4 is marked depracated. Use AES (GUI uses AES by default, but TSQL does not) */
GO
 
GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql1];
GO
 
GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql3];
GO
 
 
:Connect NODE3
 
USE [master];
GO
 
CREATE ENDPOINT [AlwaysOn_EP]
    STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (172.18.0.3)) /* Specify an IP value for LISTENER_IP to dedicate AlwaysOn traffic to it's own NIC card */
    FOR DATABASE_MIRRORING (ROLE = ALL /* Cannot use Witness as that is for mirroring only.  Partner will work but that's really just for mirroring terminology */
        , AUTHENTICATION = WINDOWS NEGOTIATE /* Authentication can be Windows or Certificate of a combination in preference order.  Authorization can force NTLM or Kerberos. These are default values */
        , ENCRYPTION = REQUIRED ALGORITHM AES); /* Default algorithm is RC4, however RC4 is marked depracated. Use AES (GUI uses AES by default, but TSQL does not) */
GO
 
GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql1];
GO
 
GRANT CONNECT ON ENDPOINT::[AlwaysOn_EP] TO [contoso\svc-sql2];
GO
 
/* 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 NODE1
 
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 NODE2
 
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 NODE3
 
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 3 databases included in our AG and they currently exist on NODE1.
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+ we can use Direct Seeding instead.
*/
 
/* Here we connect to our primary replica (NODE1) and create our AG. */
:Connect NODE1
 
/* 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 [App1AG]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, FAILURE_CONDITION_LEVEL = 3, HEALTH_CHECK_TIMEOUT = 30000, DB_FAILOVER = ON, DTC_SUPPORT = PER_DB, CLUSTER_TYPE = WSFC) /* You can also use SECONDARY_ONLY, PRIMARY, or NONE
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 [App1AG_DB1], [App1AG_DB2], [App1AG_DB3]
REPLICA ON N'NODE1' WITH (ENDPOINT_URL = N'TCP://NODE1.contoso.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC),
    N'NODE2' WITH (ENDPOINT_URL = N'TCP://NODE2.contoso.com:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 60, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC),
    N'NODE3' WITH (ENDPOINT_URL = N'TCP://NODE3.contoso.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 70, SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY), SESSION_TIMEOUT = 10, SEEDING_MODE = AUTOMATIC);
GO
 
/* Here we create a listener for our AG. If you have issues creating the listener check permissions in AD.
    You might also have to turn the AG networks to client and cluster and then turn them back to none post-listener creation*/
ALTER AVAILABILITY GROUP [App1AG]
ADD LISTENER N'App1AG' (
WITH IP
((N'10.0.0.11', N'255.255.255.0'),('172.16.0.11','255.240.0.0'))
, PORT=1433);
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 */
:Connect NODE2
 
ALTER AVAILABILITY GROUP [App1AG] JOIN;
GO
ALTER AVAILABILITY GROUP [App1AG] GRANT CREATE ANY DATABASE;
GO
 
:Connect NODE3
 
ALTER AVAILABILITY GROUP [App1AG] JOIN;
GO
ALTER AVAILABILITY GROUP [App1AG] GRANT CREATE ANY DATABASE;
GO
 
--This query allows us to view seeding performance statistics
SELECT * FROM sys.dm_hadr_physical_seeding_stats;
 
/* Now we need to turn our trace flag back off */
:Connect NODE1
DBCC TRACEOFF (9567, -1);
GO

Network Configuration

Here is the list of IP addresses from Part 1 that you will need to configure the AG.

	Data Center 1 IP	Data Center 2 IP
Cluster	10.0.0.10	172.16.0.10
AG Listener	10.0.0.11	172.16.0.11
FCI	10.0.0.15	N/A

Architecture

As a refresher, here is the diagram of the lab you are building.

Build a SQL Cluster Lab Part 3

Create Databases Code

Create Availability Group Code

Network Configuration

Architecture

Build Availability Group Video

Like this:

Related

Create Databases Code

Create Availability Group Code

Network Configuration

Architecture

Build Availability Group Video

Share this:

Like this:

Related