AlwaysOn Part 7 – AAG with dedicated Replication Network

SQL Server 2012/2014 AlwaysOn Availability Groups:

• Article Summary
• Part 1 – AlwaysOn Introduction
• Part 2 – AlwaysOn Design
• Part 3 – Install and Configure Windows Server 2012 R2 in Core mode
• Part 4 – WSFC Cluster Creation
• Part 5 – Install SQL Core on Windows Core Server
• Part 6 – AlwaysOn Availability Groups Creation
• Part 7 – AlwaysOn Availability Groups Creation (Advanced, with dedicated Replication Network)
• Part 8 – Methods to add Database on Availability Groups (SCOM Example)
• Part 9 – AlwaysOn Availability Groups – PowerShell Monitoring
• ANNEX (Part 6/7) – Manage SQL Endpoint

 

---

In Part 6, the AAG1 and AAG2 Availability Groups was created from wizard. In this part, I will do an advanced creation of AAG: AAG-3 and AAG-4. Instances members of these AAG will be configured to communicate over a replication Network. I will do the configuration with Transact-SQL and I will write later an article on how to configure AAG through PowerShell.

Now we have to create the Availability Groups: AAG-3SCOM and AAG-4SCOM on instances AOI2 and AOI4:

AAG	Members (Instance)	Default Role	AAG Listener			Databases
			Name	IP	Port
AAG-1	m-sqla1\aoi1	Primary	AAG-1L	10.0.1.41	1764	DBTest01
	m-sqla3\aoi3	Secondary
AAG-2	m-sqla1\aoi1	Secondary	AAG-2L	10.0.1.42	1764	DBTest02
	m-sqla3\aoi3	Primary
AAG-3SCOM	m-sqla2\aoi2	Primary	AAG-3L	10.0.1.43	1764	SCOM OP
	m-sqla4\aoi4	Secondary
AAG-4SCOM	m-sqla2\aoi2	Secondary	AAG-4L	10.0.1.44	1764	SCOM DW DB Orchestrator
	m-sqla4\aoi4	Primary

IP use for Instances Endpoints (subnet 10.0.20.0/24):

For people who don’t know SCOM, this product require two Databases: one DB “Operation” (for live monitoring) and one DB “Data warehouse” (for historical monitoring). These Databases require performances, so with this configuration, in nominal mode, each DB is hosted on a different Instance and have a replica on the other.

 

Create Availability Group: AAG-3SCOM

This AAG will host the SCOM “OperationsManager” database.

1 – Create Instances Endpoint

Network Configuration: The only difference with the Endpoints created for the first two AAG (with the default configuration) is that we add an IP Address of the dedicated Replication network:

–  LISTENER_IP = (10.0.20.22) – for the Instance AOI2

–  LISTENER_IP = (10.0.20.22) – for the Instance AOI4

Endpoints rights: Note the Grant Connect command that it gives rights to the other Instances account (MSA):

–  GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [lab1\svc-sqldbe4$] – on the Instance AOI2

–  GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [lab1\svc-sqldbe2$] – on the Instance AOI4

Also, the script checks if the Extended Event session “AlwaysOn_health” is started.

Script “AAG-3SCOM-Creation-1-Endpoint.sql“:

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
-- Create Login for both Instances -------------------------------------

:Connect M-SQLA2\AOI2,1764
USE [master]
GO
CREATE LOGIN [lab1\svc-sqldbe4$] FROM WINDOWS
GO

:Connect M-SQLA4\AOI4,1764
USE [master]
GO
CREATE LOGIN [lab1\svc-sqldbe2$] FROM WINDOWS
GO

- Create ENDPOINT for Instance: AOI2 -----------------------------------
:Connect M-SQLA2\AOI2,1764
USE [master]
GO

CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.0.20.22))
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO

use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [lab1\svc-sqldbe4$]
GO

-- Create ENDPOINT for Instance: AOI4 ----------------------------------
:Connect M-SQLA4\AOI4,1764
USE [master]
GO

CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.0.20.24))
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [lab1\svc-sqldbe2$]
GO

-- Start Extended Event session: "AlwaysOn_health" ---------------------
:Connect M-SQLA2\AOI2,1764
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 M-SQLA4\AOI4,1764
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

You cannot execute this script from the Management Studio (the command “Connect” is not recognized). You have to use the “sqlcmd” utility.

For more information, see TechNet “sqlcmd How-to Topics“: https://technet.microsoft.com/en-us/library/hh213540.aspx

From the M-SQLA2 server, start a CMD and execute:

sqlcmd -S M-SQLA2\AOI2 -i c:\tools\AAG-3SCOM-Creation-1-Endpoint.sql

Check Endpoint creation, use script “SQL_Endpoint-Get-List.ps1”

.\SQL_Endpoint-Get-List.ps1 -SQLServer “M-SQLA2” -InstanceName “AOI2,1764”

From SQL, you can check the TCP Listener:

— Get TCP Listener list

SELECT * FROM sys.dm_tcp_listener_states;

Or via netstat:

netstat -ano | findstr 5022

 

2 – Create Availability Group

Network Configuration: So now we can configure the Endpoint URL on the replication network (same IP as the Endpoint):

–  ENDPOINT_URL = N’TCP://10.0.20.22:5022′) – for the Instance AOI2

–  ENDPOINT_URL = N’TCP://10.0.20.24:5022′) – for the Instance AOI4

AG Listener: The script create the listener with the DNS name and the VIP:

–  ADD LISTENER N’AAG-3L’ ( WITH IP ((N’10.0.1.43′, N’255.255.255.0′)), PORT=1764 )

Script “AAG-3SCOM-Creation-2-AG”:

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
-- CREATE AAG ----------------------------------------------------------
:Connect M-SQLA2\AOI2,1764
USE [master]
GO

CREATE AVAILABILITY GROUP [AAG-3SCOM]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR
REPLICA ON
N'M-SQLA2\AOI2' WITH (
ENDPOINT_URL = N'TCP://10.0.20.22:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'M-SQLA4\AOI4' WITH (
ENDPOINT_URL = N'TCP://10.0.20.24:5022',
FAILOVER_MODE = AUTOMATIC,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO

-- Create Listener -----------------------------------------------------
:Connect M-SQLA2\AOI2,1764
USE [master]
GO

ALTER AVAILABILITY GROUP [AAG-3SCOM]
ADD LISTENER N'AAG-3L' ( WITH IP ((N'10.0.1.43', N'255.255.255.0')), PORT=1764 );
GO

-- JOIN Other Instances ------------------------------------------------
:Connect M-SQLA4\AOI4,1764
ALTER AVAILABILITY GROUP [AAG-3SCOM] JOIN;
GO

 

From the M-SQLA2 server, start a CMD and execute:

sqlcmd -S M-SQLA2\AOI2 -i c:\tools\AAG-3SCOM-Creation-2-AG.sql

Now from netstat we can see that instances communicate over the replication network (10.0.20.0):

Check the Availability Group status from the Dashboard:

Status if failed because there is no Database in the AG:

 

Create Availability Group: AAG-4SCOM

Now we have to create the last AG:

This AAG will host the SCOM “OperationsManagerDW” database.

Instance Endpoints are already created (previously with the AAG-3). So we just have to create the Availability Group.

Network Configuration: The same Endpoint URL as the AAG-3 will be used:

–  ENDPOINT_URL = N’TCP://10.0.20.22:5022′) – for the Instance AOI2
–  ENDPOINT_URL = N’TCP://10.0.20.24:5022′) – for the Instance AOI4

AG Listener IP: 10.0.1.44

–  ADD LISTENER N’AAG-3L’ ( WITH IP ((N’10.0.1.44′, N’255.255.255.0′)), PORT=1764 );

Script “AAG-4SCOM-Creation-1-AG”:

-- CREATE AAG ----------------------------------------------------------
:Connect M-SQLA4\AOI4,1764
USE [master]
GO

CREATE AVAILABILITY GROUP [AAG-4SCOM]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR REPLICA ON
N'M-SQLA4\AOI4' WITH (
  ENDPOINT_URL = N'TCP://10.0.20.24:5022',
  FAILOVER_MODE = AUTOMATIC,
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
  BACKUP_PRIORITY = 50,
  SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'M-SQLA2\AOI2' WITH (
  ENDPOINT_URL = N'TCP://10.0.20.22:5022',
  FAILOVER_MODE = AUTOMATIC,
  AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
  BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
GO

-- Create Listener -----------------------------------------------------
:Connect M-SQLA4\AOI4,1764
USE [master]
GO
ALTER AVAILABILITY GROUP [AAG-4SCOM]
ADD LISTENER N'AAG-4L' ( WITH IP ((N'10.0.1.44', N'255.255.255.0')), PORT=1764 );
GO

-- JOIN Other Instances ------------------------------------------------
:Connect M-SQLA2\AOI2,1764
ALTER AVAILABILITY GROUP [AAG-4SCOM] JOIN;
GO

 

From the M-SQLA2 server, start a CMD and execute:

sqlcmd -S M-SQLA4\AOI4 -i c:\tools\AAG-4SCOM-Creation-1-AG.sql

Now the configuration is done, we can use the AAG.

 

Next PART: Installation of SCOM with AlwaysOn Availability Groups.