AlwaysOn Part 7 – AAG with dedicated Replication Network

SQL Server 2012/2014 AlwaysOn Availability Groups:

 


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):

Hostname

IP Public Network

IP Cluster Network

IP Replication Network

M-SQLA1 10.0.1.21 10.0.10.21 n/a
M-SQLA2 10.0.1.22 10.0.10.22 10.0.20.22
M-SQLA3 10.0.1.23 10.0.10.23 n/a
M-SQLA4 10.0.1.24 10.0.10.24 10.0.20.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.

AlwaysOn Availability Group - SCOM Operational DB

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.

AlwaysOn Availability Group - SCOM DataWarehouse DB

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.

About Gilles Monville

Gilles Monville is a passionate of IT for many years; he works for over 10 years on Microsoft and VMware environments. In addition to Virtualization and Cloud computing, he loves automation (Orchestrator, PowerShell, Cloud deployment …)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

x

Check Also

SQL AlwaysOn FCI in Azure IaaS Cloud with StarWind Virtual SAN Solution

1 – Introduction This article explains how to deploy an SQL AlwaysOn FCI in Azure ...

AlwaysOn Part 8 – Methods to add Database (SCOM)

SQL Server 2012/2014 AlwaysOn Availability Groups: Article Summary Part 1 – AlwaysOn Introduction Part 2 ...

AlwaysOn Availability Groups Creation

SQL Server 2012/2014 AlwaysOn Availability Groups: Article Summary Part 1 – AlwaysOn Introduction Part 2 ...