Example setting up SQL Server Always On on a multi subnet network for disaster recovery scenario.
Following standard Microsoft documentation I found it specific to having Always On with 3 databases,
two an HA setup in one subnet and in addition a third server for DR in the other subnet.
two an HA setup in one subnet and in addition a third server for DR in the other subnet.
My situation was a pure DR and only two database each in its own subnet. This is how
I tested the setup before implementing
I tested the setup before implementing
Pre-Req for the following is my setup for the cluster and firewall rules I setup in this blog
Also not covered is the initial installation of SQL Server 17
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
Domain
RJRUSS.ORG
Users
Install - robert
Services - sqlserver
Setup Users
Users - robert, sqlserver - local admin group
Computer Management - Admin Group
*BOTH SQLA and SQLB updated
Enable HA & Update Service User for SQL Server 17
SQL Server Service
YES
SQL Server Agent User
BOTH SQLA & SQLB Services UPDATED
Backed UP Database on SQLA
Add Operating System Users to SQL Server SQLA & SQLB
Add robert,sqlserver logins to sysadmin and serveradmin in SQL server
Grant permissions to SYSTEM
USE master;
GO
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO
RAN ON BOTH SQLA & SQLB
PREPARE SQLB for RESTORE OF DATABASE
mkdir P3DDATA1/8 and LOG1 on sqlb
TEST CONNECTIONS with Databases SQL Server Management studio on SQLA connect to SQLB
Successful connection
****************************************
**********I had connection errors before I was able to get a successful connection
**********only follow this SPN steps if the same error occurs
**********ERROR with SPN --- maybe specific to my home lab - no issue with work env --
SQLA delete & recreate
C:\Users\robert>setspn -D MSSQLSvc/sqla.rjruss.org:1433 sqla
Unregistering ServicePrincipalNames for CN=SQLA,CN=Computers,DC=rjruss,DC=org
MSSQLSvc/sqla.rjruss.org:1433
Updated object
C:\Users\robert>setspn -D MSSQLSvc/sqla.rjruss.org sqla
Unregistering ServicePrincipalNames for CN=SQLA,CN=Computers,DC=rjruss,DC=org
MSSQLSvc/sqla.rjruss.org
Updated object
C:\Users\robert>setspn -A MSSQLSvc/sqla.rjruss.org:1433 RJRUSS\sqlserver
Checking domain DC=rjruss,DC=org
Registering ServicePrincipalNames for CN=sqlserver,CN=Users,DC=rjruss,DC=org
MSSQLSvc/sqla.rjruss.org:1433
Updated object
C:\Users\robert>setspn -A MSSQLSvc/sqla.rjruss.org RJRUSS\sqlserver
Checking domain DC=rjruss,DC=org
Registering ServicePrincipalNames for CN=sqlserver,CN=Users,DC=rjruss,DC=org
MSSQLSvc/sqla.rjruss.org
SQLB delete & recreate
C:\Users\robert>setspn -D MSSQLSvc/sqlb.rjruss.org:1433 sqlb
Unregistering ServicePrincipalNames for CN=sqlb,CN=Computers,DC=rjruss,DC=org
MSSQLSvc/sqlb.rjruss.org:1433
Updated object
C:\Users\robert>setspn -D MSSQLSvc/sqlb.rjruss.org sqlb
Unregistering ServicePrincipalNames for CN=sqlb,CN=Computers,DC=rjruss,DC=org
MSSQLSvc/sqlb.rjruss.org
Updated object
C:\Users\robert>setspn -A MSSQLSvc/sqlb.rjruss.org:1433 RJRUSS\sqlserver
Checking domain DC=rjruss,DC=org
Registering ServicePrincipalNames for CN=sqlserver,CN=Users,DC=rjruss,DC=org
MSSQLSvc/sqlb.rjruss.org:1433
Updated object
C:\Users\robert>setspn -A MSSQLSvc/sqlb.rjruss.org RJRUSS\sqlserver
Checking domain DC=rjruss,DC=org
Registering ServicePrincipalNames for CN=sqlserver,CN=Users,DC=rjruss,DC=org
MSSQLSvc/sqlb.rjruss.org
Setup CLUSTER
Rebooted SQLB - to get Cluster IP back on .10. subnet
Waited for SQLB - to be back online
Setup Quorum on Cluster - as this is DR it will always be a manual FAILOVER scenario on no
votes for SQLB in the primary setup - only SQLA will have the vote
votes for SQLB in the primary setup - only SQLA will have the vote
Change the assigned votes - More Actions - Configure Cluster Quorum Settings
Skipped welcome page
No witness required for DR
SETUP SQL SERVER AVAILABILITY GROUP
Add Replicate SQLB
SETUP BACKUP OPTION
Show DASHBOARD
Automatic seeding was setup - so waited and HEALTHY status arrived later
SETUP Listener
Add .10.87
ADD 20.87
TEST FAILOVER IN DISASTER RECOVERY SETUP
Shutdown SQLA
Working in SQLB - Check Cluster node status in Cluster Manager
After a short time the cluster status on SQLB is down
Open ADMIN Powershell session
Import-Module FailoverClusters
$node = "sqlb";
Stop-ClusterNode -Name $node
Start-ClusterNode -Name $node -FixQuorum
(Get-ClusterNode $node).NodeWeight = 1
Failed
Start Cluster from Services.msc with /fixquorum option
Enter /fixquorum and hit Start from “Cluster Service” properties
Back to powershell
(Get-ClusterNode $node).NodeWeight = 1
$nodes = Get-ClusterNode -Cluster $node
$nodes | Format-Table -property NodeName, State, NodeWeight
Fix Quorum
Check NODES
Role is still down
This is expected as we need to update the availability group,
Checked - dashboard
Update the availability group with data loss command as this is async DR setup
ALTER AVAILABILITY GROUP "sqlag" FORCE_FAILOVER_ALLOW_DATA_LOSS
Wait for SQLB to switch to primary - refresh view after a few minutes
Database is now running on SQLB as primary
Start Wizard to failback to SQLA
As this is a test - starting SQLA server and using standard wizard to move the primary back to SQLA
Accept Data Loss message
Connect
On SQLB resume data movement
Check Dashboard on SQLA
Fix Quorum via cluster manager
No comments:
Post a Comment