Powershell Commands for SQL Server Always On Availability Groups

By: Alejandro Cobar   |   Comments (2)   |   Related: 1 | 2 | 3 | > Availability Groups

Problem

Nowadays within the SQL Server space, to cover high availability and disaster recovery solutions, it is almost certain that you are using Always On Availability Groups. It is also very likely that you are providing support to multiple AGs that require your attention to some extent. Whether it is for patching, maintenance or to act as a firefighter per se, having tools to perform crucial tasks on Availability Groups can be really valuable.

Solution

As a part of a series of tutorials, called PowerShell tools for the DBA, I'm presenting a PowerShell tool that can help any SQL Server DBA to perform very important tasks on Availability Groups. In the current version of the script I'm presenting, I will be focusing on Monitoring and Failover aspects of the tool, but I'm definitely planning on expanding it even further. You can download the script with the link at the bottom of this article.

Initial considerations

Here are some assumptions before diving into the details of the script:

• The script heavily uses the Invoke-Sqlcmd cmdlet, that's part of the SQLPS module. If you don't have it installed, do a web search to find it, download and install it. Also, the script will let you know if you don't have such module installed in your system.
• It would be good if you have a test environment where you can test the script first.
• For the script to work flawlessly, please make sure to grant the "sysadmin" server role to the account you will use to execute the operations. However, I strongly suggest that you assign the least required privileges, but that would add an extra layer of complexity to the script which I'm currently not covering.
• This version of the script currently hasn't been tested with Availability Groups deployed on Linux versions of SQL Server.
• The test case that I will be presenting within this article involves a 2 replica Availability Group running on SQL Server 2016.

Using the PowerShell Script

General Note: If you run the script from PowerShell ISE, then you will see pop-ups asking you to choose among several options. However, if you run it directly from a PowerShell console, instead of pop-ups you will see the options presented to you in the command line interface (you will see the difference in the screenshots below).

You can run the script a few different ways:

1. Either by right clicking on the PowerShell script and selecting "Run with PowerShell" (non-ISE variant)
2. Open the PowerShell ISE (ISE variant)
   • either copy and paste the script into the PowerShell script pane
   • or run the script as follows in the PowerShell command line (I saved the file in the C:\temp folder.)

PS C:\temp> .\AvailabilityGroup.ps1

The script will ask for:

• Name of the Primary replica or DB Listener acting as the primary
• Name of the Availability Group you want to target
• The type of login you want to use to interact with the Availability Group that has the necessary privileges
  • Trusted = the connection to the SQL Server instance will be made with the same user executing the PowerShell script (basically, you don't have to specify any credentials, it will assume them based on the context).
  • Windows Login = A Windows login has to be provided for the correct authentication.
  • SQL Login = A SQL login has to be provided for the correct authentication.

(non-ISE version)

If you choose a type of login that requires you to input credentials, the script will notify you if such credentials fail to connect to the specified replica.

(non-ISE version)

If you specify an Availability Group that doesn't exist within the replica that you specify, the script will notify you.

As for the name of the Primary replica, if you specify an instance name that cannot be reached from the location you are running this script (or it simply doesn't exist, then it will notify you). If you specify the name of a Secondary replica instead, the script will figure out the name of the Primary replica and use that instead.

Select Monitor or Failover Availability Group

Once connected, you will be prompted with the action you want to apply over the Availability Group (Monitor or Failover). You will also see basic information of the Availability Group right away.

(non-ISE version)

Failover Availability Group

If you choose Failover, the script gives you an option of which Secondary to failover to.

For this mode, you have to type the entire name of the Replica.

(non-ISE version)

Synchronous Commit Mode Failover

If the Secondary replica that you pick is in a SYNCHRONOUS COMMIT mode, then it will perform the failover immediately.

Asynchronous Commit Mode Failover

If the Secondary replica that you pick is in ASYNCHRONOUS COMMIT mode, then it will ask you if you want to shift to SYNCHRONOUS COMMIT mode first.

(non-ISE version)

If you choose yes, then it will change the mode and proceed with the failover.

If you choose no, then it will ask you if you want to perform a forced failover with potential data loss.

(non-ISE version)

If you choose no, then no modification will be made and the script will notify you.

If you agree with the forced failover with potential data loss, then it will proceed and will also automatically attempt to resume the data movement in all databases within all Secondary replicas in the Availability Group (so make sure to proceed with extreme caution with this particular option).

After it completes the failover, it will show you information of the Availability Group, so that you can see that the role of the replicas has successfully been switched.

Monitoring Availability Groups

If you choose Monitor, then it will enter into a loop and every 5 seconds it will show you information about all the replicas in the Availability Group, so you can see live how far behind the Secondary replicas are or if they are completely synchronized. Press CTRL+C to stop the execution of the monitoring.

Download the PowerShell Script

• You can download the PowerShell script here.

Next Steps

• If you'd like to monitor multiple Availability Groups at the same time, you can span multiple PowerShell windows and throw different groups at them, which can be handy in very specific scenarios.
• I'm aware that there are other PowerShell tools out there to work with Availability Groups (e.g. dbatools), but I wanted to craft a version of my own from scratch, with the purpose of not only being useful, but to add value to the services we provide every day within the SQL Server space. Therefore, any input you are willing to give me around this tool will be more than welcome so I can improve it as much as possible.
• In a different series of PowerShell articles I have published, I have more material regarding Availability Groups that you can take a look at from an holistic approach instead of a particular one as described in this article.

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips