Multiple Listener Creation

We all know that only ONE Availability Group Listener can be created for Availability Group , if we tried to create another Listener using SSMS we will get below error :

As per the above error we can’t create another Listener in an Existing Availability Group.

But sometimes having Multiple Listeners will help when performing Migrations. So, my todays post is how to create Another Listener on Existing Availability Group .

Creating another Listener is not supported by Microsoft that’s the reason we are not able create using SSMS and its undocumented. This can be approached through Failover Cluster Manager.

open your WSFC–> expand your Cluster –>click on Roles–>on Right Pane select the Availability Group –> down at User Setting click on RESOURCES TAB to see existing Listener we have

To create new Listener on the existing Availability Group go t hrough below steps :

Now in Roles on Right pane–>Select your Availability Group for which you want to create another Listener–>Right click on the Availability Group–>go to Add Resources –> click on ‘ Client Access Point

After clicking on Client Access Point–> A Pop Window ‘ New Resource Wizard ‘ will be opened–> Provide your New Listener Name in Name Field–>Also Provide the New Listener IP in ADDRESS Field–> click on NEXT

Your New Listener AGL1 will get created and it will be in OFFLINE Status , right click on AGL1 and click BRING ONLINE as shown below :

As I configured Multisubnet Failover Cluster seeing two IP’s for Listener.

Once new Listener was created –>Right click on Availability Group under User Settings–> AG Properties Pop window opens–> select DEPENDENCIES TAB–>Enter your New Listener Name on Resource Field and Select OR option as shown like below :

Once above all steps are completed we can see your New Listener in SSMS now :

But if you try connecting with New Listener Name AGL1 it will fail as we have not provided the PORT info in above steps .So, go to your New Listener AGL1 Properties in SSMS –> enter the PORT Number –>click OK

Now try connecting with New Listener Name AGL1 and it will get successfully connected.

Availability Group Listener Creation Failed because of CNO Permission

The most common issue we face when creating Availability Group Listner in SQL Server using SQL Server Management Studio(SSMS)

Below Screenshot shows the error details :

To conclude that the above error is for CNO Permission Issue, please generate the cluster logs on your Primary replica and go through the logs , once it confirms the issue is with Lack of CNO Permission in Active Directory to read and create computer objects for Listener

CNO is nothing but Cluster Name Object which is a Cluster Name in my case it is AWON

Follow the steps below to resolve the CNO Permission Issue:

To remedy this, we must GRANT CNO the following permissions:

  1. Read all Properties
  2. Create Computer Objects

Follow the steps below to grant the aforementioned permissions:

  • Open Active Directory Users and Computers ( shortcut to open from powershell : dsa.msc )
  • Click on VIEW and select ‘ Advanced Features
  • Locate the container in which the listener will be formed; in my case, this will be COMPUTER, as seen below.
  • select computer–> right click –>Properties
  • Now click on ADD button and enter your CNO name. In my case it is AWON$
  • Now click on object types and enable check box COMPUTERS then click on OK ->OK
  • And now you will be back to Computer Properties TAB, click on ADVANCED TAB –>Advanced Security Settings for computers Pop window will be opened–>Select your CNO ( Mine is AWON ) from Permission Entries –> Click on EDIT button
  • A pop window ‘ Permission entry for Computers ‘ will be opened , now Enable check boxes for ‘ Read all Properties ‘ and ‘ Create Computer Objects ‘ –>click OK –> Again click OK –> click Apply –> click OK as shown below :

Now try again to create Listener and it will get created without any issue.

Powershell Script to Retrieve Firewall Rules And Ports

When we perform Side-by-Side Migrations there are times where we need to have a look at Windows Firewall Rules for troubleshooting the Issues.

Also if we want to check what FW rules are currenlty in Placed or Enabled

By using Get-NetFirewallRule command we can get list of FW rules which are already enabled

Almost a year back I saw the script online and saved in my folder but I used this Script now in my current orgnaisation and helped this script today for me .So, wanted to share it online and can have others if they don’t have it.. Sorry for not providing the Author Name here as I was not noted at that time

Note : Used this script and working fine on Windows server 2016 , I haven’t tried it in other Windows Version ..If you are trying lower version please do TEST it and then use it .

Retrieve a list of Firewall Rules

Get-NetFirewallRule
Get-NetFirewallRule -Direction Inbound | Select-Object -Property DisplayName,Profile,Enabled

Filter the options for Inbound Rule and select a few properties and sort the list

$InboundRules = Get-NetFirewallRule -Direction Inbound | Select-Object -Property DisplayName,Profile,Enabled
$InboundRules | Sort-Object -Property DisplayName

Filter the options a bit more – Inbound Rules for the Domain profile

$InboundRulesDomain = Get-NetFirewallRule -Direction Inbound | Where-Object {$_.Profile -EQ “Domain” -or $_.Profile -EQ “Any”}
$InboundRulesDomain | Select-Object -Property DisplayName,Profile,Enabled | Sort-Object -Property DisplayName
$InboundRulesDomain | Select-Object -Property DisplayName,Profile,Enabled | Export-Csv -Path “C:\FireWallReport.csv”
Import-Csv -Path “C:\FireWallReport.csv” | Out-GridView

Retrieve details about protocol and port information from the same data

$InboundDomainPorts = $InboundRulesDomain | Get-NetFirewallPortFilter | Select-Object -Property InstanceID,Protocol,LocalPort,RemotePort
$InboundDomainPorts | Sort-Object -Property InstanceID
$InboundDomainPorts | Export-Csv -Path “C:\FireWallPortReport.csv”
Import-Csv -Path “C:\FireWallPortReport.csv” | Out-GridView

Apply SQL Patch for AlwaysON Availability Group Replicas

In Alwayson Availability Group replicas the SQL server is installed on all the participating replicas and the SQL services will also be in running state on all replicas
Where we have One PRIMARY and multiple SECONDARY replicas depending on your environment.

Here in our environment we have 3 Node HADR Setup , the SQL Servies runs on all 3 replicas and one Replicas is PRIMARY and the other two replicas are SECONDARY ( 1 is Local replica with SYNCHRONOUS Mode and 1 is DR replica with ASYNCHRONOUS Mode).

Pre-requisites:

1)First need to check the current patch Level and target patch Level( N-1 where N is Latest Patch).
For Latest Patch Level which was available in market we can check on SQL server blogs.
2)Don’t apply patches directly to your production environment. First apply it to your lower environments, wait for atleast a week and so then schedule it to apply for Production environment.
3) In that one week the patch will be TESTED in lower environments by Application team
once validation is completed successfully , we can apply it on production environment.

Before applying the Patch to the SQL Instance, follow these steps::

1) Check that we have the most recent full backup for all SYSTEM and USER databases on the PRIMARY Replica. If the size of the user databases is small, take a fresh Full backup for all databases; if the database size is larger, check for the most recent valid FULL backup and then take at least a Differential or Transactional Log backup before beginning the SQL Patches.
2) On Secondary replicas take SYSTEM Databases Backup.
3) Check the AG Dashboard, make sure it shows all GREEN and HEALTHY.

Applying Patch to SQL Instances:

As previously stated, we have a three-node HADR setup ( 2 local replicas with Synchronous Mode and 1 DR with Asynchronous Mode )

1) First we will apply patch to LOCAL replica of Secondary Node
2) To do that we need to change Failover Mode from AUTOMATIC to MANUAL because just to be sure that no automatic failover happens to secondary when we are applying patch incase of any issues on PRIMARY .
3) Also SUSPEND the DATA movement on currently patching secondary replica as if you do it on PRIAMRY it will SUSPEND moving the DATA for all the secondary replicas.
4) Now start applying the Patch on Secondary replica and restart the Server after applying the Patch.
5) Once the secondary replica comes ONLINE then do below check :
. Verify the SQL Services are UP and RUNNING
. Verify the SQL Version and see newly applied patch is reflecting .
. verify the SQL Error Logs for any error messages.
. Check all USER Databases are HEALTHY and ONLINE.
6) Now RESUME the DATA movement on Secondary replica and wait till the Database gets SYNCHRONIZED.
7) Now check th AG Dashboard , make sure it shows all GREEN and HEALTHY.
8) Now perform MANUAL failover from PRIMARY to newly patched Secondary replica and make sure the AG dashboard showing all GREEN and HEALTHY on newly patched replica which is currently PRIMARY.
9) Now follow above steps to proceed Patching .
10) once it is completed, Failback the AG to previous PRIMARY replica and check Dashboard is showing as all GREEN and HEALTHY.
11) Follow the procedures above to apply the patch on the DR replica.

Validate the following after patching all three replicas:

. Verify the SQL Verison was updated on all 3 replicas which are participating in AlwaysON configuration.
. Verify the AG Dashboard, make sure all shows GREEN and HEALTHY.
. check the SQL Error Logs for any Error messages on all 3 replicas.
. Inform to APPLICATION Team for App Validation and functionality.

Alwayson Secondary replica DISCONNECTED State Issue

We had a strange issue with the Always on 3-Node replica today.

I got onto the server to Implement a change, which was to apply a SQL Patch to SQL Server 2016 SP2.

I’ve discovered that my AG’s health isn’t looking well though, and the databases in that secondary replica’s state are in RESTORING mode.

I also don’t notice any folders inside the AVAILABILITY GROUPS Folder in the problematic secondary replica, as indicated in the clip below.

When I RDP to the problematic Secondary replica and connect all three replica sql instances from SSMS, I am unable to access the PRIMARY AG, as demonstrated in the screenshot below:

For a brief moment, I was concerned about what had happened to PRIMARY, where the Primary AG is located, but then I opened cluadmin and noticed that the AG’s Owner was present on the DR server… So, I connected the SQL Instance to the DR server to see the PRIMARY AG, and it was there. The PRIMARY replica is in good health, as is one of the secondary replicas, however the other problematic secondary replica is in the DISCONNECTED state.

I looked through the SQL Error Logs to see if there were any problems linked to our problem. I discovered the following error.

Besides from that, I couldn’t find any additional errors that would help me troubleshoot the problem.

So, googled for below two errors :

Error Message 35201:

When attempting to connect to availability replica ‘Myreplica’ with id [availability group id], a connection timeout occurred. Either a network or firewall problem, or the replica’s endpoint address is not the DB mirroring endpoint of the host server instance.
There is no connection between this secondary replica and the primary replica. DISCONNECTED is the connected state.
I found the following KB article on support.miscrosoft.com.

https://support.microsoft.com/en-us/topic/kb3213703-fix-an-always-on-secondary-replica-goes-into-a-disconnecting-state-10131118-b63a-f49f-b140-907f77774dc2

However, because the SQL instances version is SQL 2016 SP2, it was not useful for my present organisation.
We also noticed that some Cluster difficulties were logged in Cluster Events on Windows Server Failover Cluster Manager, thus we notified the Windows Team, who are looking into it.
Meanwhile, we’re getting disc space alerts since the data isn’t being migrated to one of the secondary replicas.
So, as indicated by the Microsoft page above, we obtained authorization from the Client to reboot the problematic secondary replica and see whether the problem is resolved.
Because the secondary replica isn’t used in any way, the customer gave his approval, and we rebooted the replica, but the problem persists.

However, because the SQL instances version is SQL 2016 SP2, it was not useful for my present organization.
We also noticed that some Cluster difficulties were logged in Cluster Events on Windows Server Failover Cluster Manager, thus we notified the Windows Team, who are looking into it.
Meanwhile, we’re getting disc space alerts since the data isn’t being migrated to one of the secondary replicas.
So, as indicated by the Microsoft page above, we obtained authorization from the Client to reboot the problematic secondary replica and see whether the problem is resolved.
Because the secondary replica isn’t used in any way, the customer gave his approval, and we rebooted the replica, but the problem persists.