Background
I had a recent requirement to setup a SQL Server Always On cluster in a multi subnet scenario for
disaster recovery. The setup was on servers not under my control and firewalls between them. Also
from experience any service request to open any ports took way too long (leading to delays in my work) :()
So no real way of trial and error for the setup as I felt let down by the microsoft documentation for the actual setup.
Microsoft always had HA and DR in their scenarios for Azure and our use case only required DR.
(if I had reason to follow the HA/DR entire guide maybe I would have found the ports required earlier.)
So I "found" the required ports to connect my two servers. In my home lab I setup a multi subnet network to track
So no real way of trial and error for the setup as I felt let down by the microsoft documentation for the actual setup.
Microsoft always had HA and DR in their scenarios for Azure and our use case only required DR.
(if I had reason to follow the HA/DR entire guide maybe I would have found the ports required earlier.)
So I "found" the required ports to connect my two servers. In my home lab I setup a multi subnet network to track
the ports required.
TL;DR summary :)
I used windows firewall and its rules order (see blog for details) meant I had a rule to block the following
ports. And kept TCP and UDP the same to keep it straightforward.
Blocked Ports - both TCP and UDP
0-134, 136,140-444,446-1432,1434-3342,3344-3388, 3390-4999, 6001-65535
Which relates to the
Allowed Ports - both TCP and UDP
135, 137-139, 445, 1433, 3343, 3389, 5000-6000
Warnings/Notes
*RDP Port 3389 only used for testing as covered by the rest of this blog
*For Azure I will investigate dynamic ports 5000-6000 and requirements for work as these ports may
change for my work requirements - on my homelab these ports are fine
*Also for my request was in Azure I had to add the Azure load balancing ports to the final work
related setup
Details on how I arrived at the above ports
Home Landscape Setup
Two Servers running Windows 2016
- SQLA 10.10.10.35
- SQLB 10.10.20.35
Port range for these servers TCP/UDP
- netsh int ipv4 show dynamicport tcp
- netsh int ipv4 show dynamicport udp
C:\Users\robert>netsh int ipv4 show dynamicport tcp
Protocol tcp Dynamic Port Range
---------------------------------
Start Port : 49152
Number of Ports : 16384
C:\Users\robert>netsh int ipv4 show dynamicport udp
Protocol udp Dynamic Port Range
---------------------------------
Start Port : 49152
Number of Ports : 16384
C:\Users\robert>
Which means a port range 0-65535 which would form the basis of the firewall rules.
1) Enable Firewall
Usually on my home lab I disable the firewalls but I needed to replicate a firewall and my intention was to just setup the rules between the two database servers
I started out blocking all ports and then adding a rule to allow ports - but the BLOCK is always first.
So only block specific ports was the way to go.
So only block specific ports was the way to go.
My trial and error approach would be use BLOCK but narrow the ports that are blocked.
Microsoft Document
“Firewall rules are applied with the following precedence:
- Allow this firewall rule to override block rules
- Block connection
- Allow connection
- Default profile behavior (allow connection or block connection, as specified on the Profile tab of the Windows Firewall with Advanced Security Properties dialog)”
2) Install network related test tools
a) Telnet
dism /online /Enable-Feature /FeatureName:TelnetClient
b) PortQry
c) WireShark
Installed with all default options, I didn’t select extra or deselect any options
It also installs Npcap - with default options
CheckPoint/Snapshot both servers
3) Setup Firewall to block SQLA & SQLB from connecting
Working on server SQLB -
For testing a connection SQLA with RDP port 3389 (this is default enabled by windows firewall)
Port 3389 listening
Working on Server SQLA
Note SQLB 10.10.20.35 for this rule
On SQLA - setup NEW rule for TCP connections to block
Only for SQLB
New Firewall Created
REPEAT Rules but choose UDP protocol
Both UDP and TCP ports blocked
REPEAT PORTQRY test on SQLB
3389 is now filitered / blocked by the firewall
NOW allow 3389 by adpating the blocking rules.
0-3388,3390-65535
Repeat for UDP
Test with PORTQRY on SQLB again
3389 is again listening.
ALIGN SQLB firewall ports - but block SQLA ;()
Noting SQLA IP address 10.10.10.35 for this rule
Install Failover cluster on both servers
Powershell Admin account
Test cluster
PS C:\Windows\system32> import-module failoverclusters
PS C:\Windows\system32> test-cluster sqla,sqlb
test-cluster : Unable to connect to sqlb via WMI. This may be due to networking issues or firewall configuration on
sqlb.
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At line:1 char:1
+ test-cluster sqla,sqlb
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Test-Cluster], ClusterCmdletException
+ FullyQualifiedErrorId : Test-Cluster,Microsoft.FailoverClusters.PowerShell.TestClusterCommand
Failed with RPC error and indicating firewall issues ;) well lets see.
Googling this RPC issue found the following but server 2016 was not listed in the “applies to” section
Above link has no mention of version and indicates the registry fix is appropriate for 2016
Regedit on BOTH sqla and SQLb
Name = Ports
Add another REG_SZ and name as per the document
Now set the values
5000-6000
I ignored this message for ports
As the value was set
REBOOT BOTH SERVERS
Now we have defined ports time to adapt FIREWALL rules
We need to allow the following for RPC - default port 135 and the dynamic range
135
5000-6000
So change from
0-3388, 3390-65535
To
0-134,136-3388,3390-4999,6001-65535
Both UDP and TCP
& BOTH SERVERS
Test cluster
PS C:\Windows\system32> import-module failoverclusters
PS C:\Windows\system32> test-cluster sqla,sqlb
PS C:\Windows\system32> import-module failoverclusters
PS C:\Windows\system32> test-cluster sqla,sqlb
test-cluster : Failed to access remote registry on 'sqlb.rjruss.org'. Ensure that the remote registry service is
running, and have remote administration enabled.
The network path was not found.
At line:1 char:1
+ test-cluster sqla,sqlb
+ ~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Test-Cluster], ClusterCmdletException
+ FullyQualifiedErrorId : Test-Cluster,Microsoft.FailoverClusters.PowerShell.TestClusterCommand
Still Fails :(
SQLA Open Fireshark
Start scan on Ethernet port (active adapter)
Filter for sqlb IP address
ip.dst==10.10.20.35
Add dest port column via edit->preferences->Columns
Start the test-cluster command again - look for failures
Seems to fail on 445 & 137
NetBIOS name service: port 137 TCP, UDP
SMB over IP (Microsoft-DS): port 445 TCP, UDP
Also DOH I realised I would need the windows Cluster and also add SQL server ports
Review of ports now need to add
TCP
1433, 3343, 139, 445
UDP
3343, 137, 138
To keep it simple adding 137 & 138 as TCP ports as well.
So new ports for rules for the block rule adapted to allow these required ports
0-134, 136,140-444,446-1432,1434-3342,3344-3388, 3390-4999, 6001-65535
ADDED ALLOW RULES FOR BOTH
135, 137-139, 445, 1433, 3343, 3389, 5000-6000
PS C:\Windows\system32> import-module failoverclusters
PS C:\Windows\system32> test-cluster sqla,sqlb
Starts to work
“ClusterConditionallyApproved” is good to proceed
Warning - is acceptable as my setup only has one network and the servers are on different subnets
I decided on the following for my Always On setup
SQLA
10.10.10.35
SQLB
10.10.20.35
cluster name sqlcluster
10.10.10.86
10.10.20.86
Listener name sqlagl
10.10.10.87
10.10.20.87
Create the Cluster
New-Cluster -Name sqlcluster -Node sqla,sqlb -NoStorage -StaticAddress 10.10.10.86,10.10.20.86
PS C:\Windows\system32> New-Cluster -Name sqlcluster -Node sqla,sqlb -NoStorage -StaticAddress 10.10.10.86,10.10.20.86
WARNING: There were issues while creating the clustered role that may prevent it from starting. For more information
view the report file below.
WARNING: Report file location: C:\Windows\cluster\Reports\Create Cluster Wizard sqlcluster on 2020.02.01 At
13.27.17.htm
Name
----
Sqlcluster
Warning only.
My final destination was Azure and my setup does not need a cluster disk - as I will switch off the quorum - see later blog for Always On setup
Check Cluster Manager
The Cluster Core Resource IP details had SQLA interface offline.
I rebooted SQLB to test
SQLA IP was now online
READY NOW for SQL ALWAYS ON setup
No comments:
Post a Comment