Saturday, February 1, 2020

SQL Server 2017 Always On in Disaster Recovery Scenario and Window 2016 servers in different subnets


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.


My situation was a pure DR and only two database each in its own subnet. This is how
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




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





Google +