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

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.