Configure Always On Availability Group with Listener – Part6

Prepare Database Server JBSAG1, JBSAG2 and JBSAG3

-> Make sure below ports are opened for JBSAG1, JBSAG2 and JBSAG3.

5022 (Always On Endpoint)

1433 (SQL Server PortListener Port)

58888 (Backend Pool Port)

Configure Cluster

-> Login to Database Server JBSAG1, JBSAG2 and JBSAG3 and execute below Powershell command. Refer this article for more details on Powershell command related to cluster configuration,

Install-WindowsFeature -Name "Failover-Clustering" -IncludeManagementTools

-> Once installed on both JBSAG1, JBSAG2 and JBSAG3. Restart the database servers.

-> Execute below powershell command that runs all cluster validation tests on Database Server JBASAG1, JBSAG2 and JBSAG3.

Test-Cluster –Node JBSAG1,JBSAG2,JBSAG3

-> View the report and make sure all looks fine.

-> Below hostname and IP Address will be utilized for Cluster and Always On Listener Configuration,

East US

West US

-> Execute below Powershell code to create a cluster,

New-Cluster -Name JBSWikiClust -Node JBSAG1,JBSAG2,JBSAG3 -StaticAddress 172.20.1.19,192.168.1.19 -NoStorage

Configure Always On Availability Group

-> Make sure SQL services are started with a proper Service account. In my case I have used account jbswiki\jbswiki as the SQL Service account.

-> Make sure [NT AUTHORITYSYSTEM] has appropriate access to SQL Server Instances.

-> Enable Always On feature for JBSAG1, JBSAG2 and JBSAG3 from Configuration Manager and restart SQL Server,

-> Configure Always On,

Configure Always On Availability Group Listener

-> Below hostname and IP Address will be utilized for Cluster and Always On Listener Configuration,

East US

West US

-> Open Cluster Administrator, Start -> Run -> cluadmin.msc on Primary Replica JBSAG1,

-> Right click “IP Address 192.168.1.0” and click Properties,

-> Once created, Stop the Role as below,

-> Right click AG resource and click properties,

-> Click dependencies and add the IP Address resource as a dependency,

-> Execute below powershell command on JBSAG1, JBSAG2 and JBSAG3 to set the cluster parameters,

Import-Module FailoverClusters
# Availability Group Name
$AGName = "JBSAG"
$ClusterNetworkName = (Get-ClusterNetwork).Name
$IPResourceName = (Get-ClusterResource | Where-Object { $_.ResourceType -eq "IP Address" -and $_.OwnerGroup -eq $AGName}).Name
$ListenerILBIP = (Get-ClusterResource $IPResourceName | Get-ClusterParameter | Where-Object {$_.Name -eq "Address"}).Value
#Load Balancer Listener endpoint port
$ListenerProbePort = 58888
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ListenerILBIP";"ProbePort"=$ListenerProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
Get-ClusterResource $IPResourceName

-> Start the AG role,

-> Connect to the Primary replica using SQL Server Management Studio. Open the properties of Listener JBSAPP and enter the port number as appropriate,

-> Failover and failback Availability group from SQL Server Management Studio and check if Listener connection can be made.

More articles on this Series,

-> Always On Availability Group Configuration on Azure IaaS Virtual Machine utilizing Multi Subnet
-> Always On Availability Group Environment on Azure IaaS Virtual Machine Introduction – Part1
-> Configure appropriate Virtual Network and Subnet for Azure IaaS Virtual Machine – Part2
-> Deploy required Azure IaaS Virtual Machine – Part3
-> Configure Domain Controller and Windows Cluster on appropriate Azure IaaS Virtual Machine – Part4
-> Configure Azure Load Balancer – Part5

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Configure appropriate Virtual Network and Subnet for Azure IaaS Virtual Machine – Part2

Provision Appropriate Virtual Network and associated Subnet

Virtual Network and Subnet – East US

-> Search for “Virtual Network” in Azure portal and provision it as per below guidance,

-> Click on ” Add subnet” on “IP Address” Tab and fill it up with appropriate details as shown in the screenshot,

-> I will go with the default values for “Security” and “Tags” tab.

-> Click Create and wait for the Virtual Network creation.

Virtual Network and Subnet – West US

-> Search for “Virtual Network” in Azure portal and provision it as per below guidance,

-> I will go with the default values for “Security” and “Tags” tab.

Virtual Network jbswiki_VNET_eastus and jbswiki_VNET_westus Peering

-> Open Virtual Network jbswiki_VNET_eastus and click on “Peerings”.

More articles on this Series,

-> Always On Availability Group Configuration on Azure IaaS Virtual Machine utilizing Multi Subnet
-> Always On Availability Group Environment on Azure IaaS Virtual Machine Introduction – Part1
-> Deploy required Azure IaaS Virtual Machine – Part3
-> Configure Domain Controller and Windows Cluster on appropriate Azure IaaS Virtual Machine – Part4
-> Configure Azure Load Balancer – Part5
-> Configure Always On Availability Group with Listener – Part6

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Configure Always On Availability Group with Listener – Part6

Prepare Database Server JBSAG1 and JBSAG2

-> Login to Database Server JBSAG1. Open Start-> Run -> ncpa.cpl.

-> Right Click appropriate Network Adaptor and click properties. Double click “Internet protocol Version 4 (TCP/IPv4)” and make it static,

-> Login to Database Server JBSAG2. Open Start-> Run -> ncpa.cpl.

-> Right Click appropriate Network Adaptor and click properties. Double click “Internet protocol Version 4 (TCP/IPv4)” and make it static,

-> Make sure below ports are opened for JBSAG1 and JBSAG2.

5022 (Always On Endpoint)

1433 (SQL Server Port\Listener Port)

58888 (Backend Pool Port)

Configure Cluster

-> Login to Database Server JBSAG1 and JBSAG2 and execute below Powershell command. Refer this article for more details on Powershell command related to cluster configuration,

Install-WindowsFeature -Name "Failover-Clustering" -IncludeManagementTools

-> Once installed on both JBSAG1, JBSAG2. Restart both database servers.

-> Execute below powershell command that runs all cluster validation tests on Database Server JBASAG1 and JBSAG2.

Test-Cluster –Node JBSAG1,JBSAG2

-> View the report and make sure all looks fine.

-> Below hostname and IP Address will be utilized for Cluster and Always On Listener Configuration,

-> Execute below Powershell code to create a cluster,

New-Cluster -Name JBSWikiClust -Node JBSAG1,JBSAG2 -StaticAddress 172.20.1.19 -NoStorage

Configure Always On Availability Group

-> Make sure SQL services are started with a proper Service account. In my case I have used account jbswiki\jbswiki as the SQL Service account.

-> Make sure [NT AUTHORITY\SYSTEM] has appropriate access to SQL Server Instances.

-> Enable Always On feature for JBSAG1 and JBSAG2 from Configuration Manager and restart SQL Server,

-> Configure Always On,

Configure Always On Availability Group Listener

-> Below hostname and IP Address will be utilized for Cluster and Always On Listener Configuration,

-> Open Cluster Administrator, Start -> Run -> cluadmin.msc on Primary Replica JBSAG1,

-> Once created, Stop the Role as below,

-> Below is the property of IP Address object. You can make any changes if required mostly when this is created dynamically. In my case it is created properly, so not going to change anything,

-> Note down the Name and Address from above screenshot.

-> Right click AG resource and click properties,

-> Click dependencies and add the IP Address resource as a dependency,

-> Execute below powershell command on JBSAG1 and JBSAG2 to set the cluster parameters,

Import-Module FailoverClusters
# Availability Group Name
$AGName = "JBSAG"
$ClusterNetworkName = (Get-ClusterNetwork).Name
$IPResourceName = (Get-ClusterResource | Where-Object { $_.ResourceType -eq "IP Address" -and $_.OwnerGroup -eq $AGName}).Name
$ListenerILBIP = (Get-ClusterResource $IPResourceName | Get-ClusterParameter | Where-Object {$_.Name -eq "Address"}).Value
#Load Balancer Listener endpoint port
$ListenerProbePort = 58888
Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ListenerILBIP";"ProbePort"=$ListenerProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
Get-ClusterResource $IPResourceName

-> Start the AG role,

-> Connect to the Primary replica using SQL Server Management Studio. Open the properties of Listener JBSAPP and enter the port number as appropriate,

-> Failover and failback Availability group from SQL Server Management Studio and check if Listener connection can be made.

More articles on this Series,

-> Always On Availability Group Configuration on Azure IaaS Virtual Machine utilizing same subnet
-> Always On Availability Group Environment on Azure IaaS Virtual Machine Introduction – Part1
-> Configure appropriate Virtual Network and Subnet for Azure IaaS Virtual Machine – Part2
-> Deploy required Azure IaaS Virtual Machine – Part3
-> Configure Domain Controller and Windows Cluster on appropriate Azure IaaS Virtual Machine – Part4
-> Configure Azure Load Balancer – Part5

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.