tech2000-usa
asked on
PowerShell script to automate SQL Database Failover/Failback test
PowerShell script to automate SQL Database Failover/Failback test
PowerShell Automation TASK:
1) Create a PowerShell script to failover SQL Database servers from Primary Site to Data Recovery Site.
2) Create a second PowerShell script to failback SQL Database servers from Data Recovery Site to Primary Site.
We have two SQL database servers in our Primary Site, one server is setup as primary SQL database server (SQLSER01), and the other one is setup as secondary SQL database server (SQLSER02). The primary server is replicated to the secondary server, the two servers are members of Availability group AG1.
Availability group AG1 (Primary site)
SQLSER01
SQLSER02
We have two SQL database servers in our Data Recovery Site, one server is setup as primary SQL database server (SQLSER03), and the other one is setup as secondary SQL database server (SQLSER04). The primary server is replicated to the secondary server, the two servers are members of Availability group AG2.
Availability group AG2 (Data Recovery site)
SQLSER03
SQLSER04
DistrAG
Distributed group is the connection between the two Availability groups (AG1 & AG2).
Failover Process Steps:
1) Connect to each SQL server in AG1 group in Primary Site, query servers to find out which one is primary database server and which one is secondary database server.
2) Connect to each SQL server in AG2 group in Data Recovery site, query servers to find out which one is primary database server and which one is secondary database server.
3) Check to see if the databases are synchronized in Primary site in Availability groups AG1and Data Recovery site in Availability groups AG2 before performing failover from Primary Site to Data Recover Site.
a- Check to find out if the two servers (SQLSER01 & SQLSER02) in Availability groups AG1 are synchronized.
b- Check to find out if the two servers (SQLSER03 & SQLSER04) in Availability groups AG2 are synchronized.
NOTE: You can check the Dashboard (SSMS Dashboard) to see the status of synchronization (Status of HEALTHY) between the servers before performing the failover.
4) If the servers in AG1 group are synchronized and the servers in AG2 group are synchronized then perform the Failover.
• If the servers in any Availability groups are not synchronized then abort the failover process, display a message ‘Cannot processed with Failover of AG1 group servers to AG2 group servers because servers in (AG1 or AG2) are not synchronized. Please investigate the issue and perform the Failover again’.
5) If servers are synchronized
a- Connect to the Primary server in AG1 group, (in SQLCMD mode)
b- Perform failover to make it Secondary server.
c- Connect to the Primary server in AG2 group (in SQLCMD mode)
d- Perform failover to make it Primary server.
6) Check to see if the servers are synchronized in Data Recovery Site after failover.
NOTE: Check the Dashboard (SSMS Dashboard) to see the status of synchronization (Status of HEALTHY) between the servers after performing the failover.
7) If the servers are not synchronized in the Data Recovery Site, display a message ‘Issue with Failover in AG2 group servers. Please investigate the issue and perform the Failover again’.
Failback Process Steps
The Failback process steps are pretty much the same as the Failover process steps, but in reverse order.
PowerShell Automation TASK:
1) Create a PowerShell script to failover SQL Database servers from Primary Site to Data Recovery Site.
2) Create a second PowerShell script to failback SQL Database servers from Data Recovery Site to Primary Site.
We have two SQL database servers in our Primary Site, one server is setup as primary SQL database server (SQLSER01), and the other one is setup as secondary SQL database server (SQLSER02). The primary server is replicated to the secondary server, the two servers are members of Availability group AG1.
Availability group AG1 (Primary site)
SQLSER01
SQLSER02
We have two SQL database servers in our Data Recovery Site, one server is setup as primary SQL database server (SQLSER03), and the other one is setup as secondary SQL database server (SQLSER04). The primary server is replicated to the secondary server, the two servers are members of Availability group AG2.
Availability group AG2 (Data Recovery site)
SQLSER03
SQLSER04
DistrAG
Distributed group is the connection between the two Availability groups (AG1 & AG2).
Failover Process Steps:
1) Connect to each SQL server in AG1 group in Primary Site, query servers to find out which one is primary database server and which one is secondary database server.
2) Connect to each SQL server in AG2 group in Data Recovery site, query servers to find out which one is primary database server and which one is secondary database server.
3) Check to see if the databases are synchronized in Primary site in Availability groups AG1and Data Recovery site in Availability groups AG2 before performing failover from Primary Site to Data Recover Site.
a- Check to find out if the two servers (SQLSER01 & SQLSER02) in Availability groups AG1 are synchronized.
b- Check to find out if the two servers (SQLSER03 & SQLSER04) in Availability groups AG2 are synchronized.
NOTE: You can check the Dashboard (SSMS Dashboard) to see the status of synchronization (Status of HEALTHY) between the servers before performing the failover.
4) If the servers in AG1 group are synchronized and the servers in AG2 group are synchronized then perform the Failover.
• If the servers in any Availability groups are not synchronized then abort the failover process, display a message ‘Cannot processed with Failover of AG1 group servers to AG2 group servers because servers in (AG1 or AG2) are not synchronized. Please investigate the issue and perform the Failover again’.
5) If servers are synchronized
a- Connect to the Primary server in AG1 group, (in SQLCMD mode)
b- Perform failover to make it Secondary server.
c- Connect to the Primary server in AG2 group (in SQLCMD mode)
d- Perform failover to make it Primary server.
6) Check to see if the servers are synchronized in Data Recovery Site after failover.
NOTE: Check the Dashboard (SSMS Dashboard) to see the status of synchronization (Status of HEALTHY) between the servers after performing the failover.
7) If the servers are not synchronized in the Data Recovery Site, display a message ‘Issue with Failover in AG2 group servers. Please investigate the issue and perform the Failover again’.
Failback Process Steps
The Failback process steps are pretty much the same as the Failover process steps, but in reverse order.
ASKER
Distributed group is the connection between the two Availability groups (AG1 & AG2).
Distributed availability groups
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups?view=sql-server-ver15
Distributed availability groups
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups?view=sql-server-ver15
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
DistrAG
Distributed group is the connection between the two Availability groups (AG1 & AG2).
What does that mean?