Posted by

Vivek Janakiraman

Posted on

March 4, 2022

Posted under

AlwaysON, Azure

Comments

Always ON – Max DOP for Secondary

Environment

-> JBSUB-PRIMARY and JBSUB-DR are Azure IaaS virtual machines provisioned on East US region.

-> Azure virtual machine JBSUB-PRIMARY and JBSUB-DR are part of Windows cluster JBSWikiClust without shared storage. Azure virtual machine JBSUB-PRIMARY and JBSUB-DR will be configured using Synchronous commit with automatic failover.

-> We had a requirement wherein MAXDOP value should be set to 1 in primary replica and MAXDOP value should be 0 on secondary replica.

-> The problem arises on above requirement when a failover is performed and the MAXDOP value for each of these 2 replicas are set at instance level. After the failover New primary will have MAXDOP as 0 and New Secondary with MAXDOP1 and this causes performance issues in our application.

-> Database option “Max DOP for Secondary” came to rescue, where in we were able to specify the MAXDOP value for Primary and secondary replica as below,

Primary Replica JBSUB-PRIMARY

select @@servername
go
sp_configure 'max degree of parallelism'

-> Then execute below user query,

select top 100000 * from [dbo].[Table3] a INNER JOIN Table2 b ON a.sno=a.sno 
order by b.sname

Secondary Replica JBSUB-DR using Read-Routing List

select @@servername
go
sp_configure 'max degree of parallelism'

-> Then execute below user query,

select top 100000 * from [dbo].[Table3] a INNER JOIN Table2 b ON a.sno=a.sno 
order by b.sname

Secondary Replica JBSUB-DR directly using SQL Server Instance

select @@servername
go
sp_configure 'max degree of parallelism'

-> Then execute below user query,

select top 100000 * from [dbo].[Table3] a INNER JOIN Table2 b ON a.sno=a.sno 
order by b.sname

Hope this help!!!

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.

Posted by

Vivek Janakiraman

Posted on

March 3, 2022

Posted under

Uncategorized

Comments

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,