Rajendra Gupta
configured availability groups

Configure SQL Server Always On Availability Groups using Windows PowerShell scripts

December 10, 2020 by

In this 38th article for SQL Server Always On Availability Groups, we will explore Windows PowerShell scripts to configure it.

Introduction

Windows PowerShell is a scripting language that offers useful modules for database professionals (Developer and DBA). Usually, we use SQL Server Management Studio to perform administrative tasks using the interactive windows. In the previous articles, we used SSMS for all AG configurations. If we go through AG configuration consoles, do the configuration and generate scripts, you get scripts in SQLCMD mode.

In this article, let’s explore how you can use Windows PowerShell for SQL Server Always On Availability Groups.

A quick overview of SQL Server PowerShell

We have two SQL Server PowerShell Modules – SqlServer and SQLPS.

  • SQLPS: It is available with SQL Server installation and useful for backward compatibility. Microsoft does not update this module
  • SqlServer: The SqlServer PowerShell module has cmdlets for the latest SQL Server features. You can install this module from the PowerShell gallery

Connect to your primary AG replica in SSMS, right-click on the availability group and Launch Windows PowerShell. You get the following error message because the SqlServer module is not installed on the replica.

SQL Server PowerShell module for Windows PowerShell Scripts

To install the SqlServer module, run the following command in the Windows PowerShell with administrative command.

It downloads the required package and installs it on your primary AG replica.

install the SqlServer module

PowerShell drives for SQL Server

Windows PowerShell scripts uses the virtual drives so that users can traverse different modules, functions similar to a drive. These drives are also known as PowerShell drives.

To understand these PowerShell drives, import the SqlServer module and access the virtual drive using the following command.

You can access the PowerShell drive now. As you see, the drive appears as SQLServer:\SQL>

PowerShell drives for SQL Server

Now, use the dir command to check the existing directories. It gives you a server name (in my case SQLNode1). Here, the instance name appears as a folder.

Let’s enter into the folder (SQLNode1) and use the dir command to check its contents. It gives you the instance name and its properties.

The instance name for my primary replica is INST1.

Instance name

Now, enter into the instance folder, and it returns the folders for the different SQL features such as Audit, databases, Logins, Roles, Mail, Triggers.

In this list, you also have a folder for AvailabilityGroups. Let’s enter into this folder, and it should return the availability group name.

SQL Server features

You get the following values from this directory.

  • Availability Group Name: [AG-MyNewDB-Demo]
  • Primary Replica Instance: SQLNode2\INST1

Availability Group Name

In the availability group, you further get the path for different AG configurations.

  • Availability databases
  • Availability Group Listeners
  • Availability Replicas
  • Database Replica states

Availability databases

We can enter into the respective folder, and it returns the corresponding details. For example, cd Availabilitydatabases give you the following details.

  • Availability database name
  • Synchronization status
  • Suspend status: If your AG database synchronization is suspended, it shows True value in the IsSuspended column
  • Join status

AG database synchronization

You should be familiar with the PowerShell Virtual drives for SQL Server Always On Availability Groups. Let’s start the AG configurations with the Windows PowerShell cmdlets.

In my demo environment, the availability group is already configured with the following configuration.

  • Primary replica: SQLNode2\INST1
  • Secondary replica: SQLNode1\INST1
  • AG database: MyNewDB

To begin with the Windows PowerShell, we use the following approach:

  • Remove an availability database from the primary replica
  • Delete availability group
  • disable AG configuration for SQL Service
  • Stop and Start SQL Services
  • Enable AG configuration for SQL Service
  • Configure the availability group and database in the synchronous mode
  • Configure the SQL Server Listener

Windows PowerShell Scripts to remove an availability database from the primary replica

Suppose we want to remove a database from the availability group. In Windows PowerShell, we use the Remove-SqlAvailabiltyDatabase cmdlet in the SqlServer module. In the path, you need to be in the availability group folder.

PowerShell Virtual drives

Launch the AG dashboard. The dashboard does not look healthy because there is no database in the availability group for synchronization.

AG dashboard

In the below screenshot, we see the AG database after removing it from the availability group.

  • Primary replica SQLNode2: MyNewDB in the online status
  • Secondary replica SQLNode1: MyNewDB in the restoring status

Primary replica database status

Windows PowerShell Scripts to delete availability groups

To remove an availability group, connect to the primary replica in SSMS, right-click on the availability group and Remove the availability group.

Delete availability group

In Windows PowerShell, we use Remove-SqlAvailabilityGroup cmdlet. In the path, you should be in the availability group folder.

Remove-SqlAvailabilityGroup cmdlet

If the availability group dashboard is already opened, try to refresh it. You get the error because we have removed the SQL Server Always On availability group.

In the object explorer, you do not see any configured availability groups.

configured availability groups

Windows PowerShell Scripts to disable AG configuration for SQL Service

As you know, to use the SQL Server Always On Availability Group features, we enable the feature from the SQL Server Configuration Manager on all AG replicas.

You can verify it from the SQL Server Configuration Manager as per the following screenshot.

Disable AG configuration for SQL Service

Suppose, we want to disable the SQL Always On feature. Using the PowerShell, we use Disable-SqlAlwaysOn cmdlet. In the path, you should be in the instance folder, as shown below.

Disable the SQL Always On feature

Now, if you open SQL Server properties, you see it removed the tick from the AG feature checkbox.

open SQL Server properties

We require to restart the SQL service to make the configuration effective. To start the SQL Service, run the following command.

It stops the SQL Services for the INST1 instance.

restart for SQL Service

To start the SQL service, it uses the start-service PowerShell cmdlet. Run the following command, and it starts the database engine service for SQL Server.

start-service PowerShell cmdlet

You can validate the SQL Service status in the SQL Server Configuration Manager.

SQL Server Configuration Manager

Windows PowerShell Scripts to enable AG configuration for SQL Service

Previously, we disabled the AG configuration from the SQL Services. We need to enable the SQL Server Always On Availability Group configuration, run the following command.

Enable AG configuration for SQL Service

You can verify from the SQL Server Configuration Manager, and you get a check on the Always On Availability Group as shown below.

check on the Always On Availability Group

You need to restart SQL Service before configuring SQL Server Always On Availability Groups.

restart SQL Service

Windows PowerShell scripts to configure availability groups, add database in the synchronous mode and create a SQL listener

In this step, we configure the availability group between primary and secondary replica in synchronous mode. The overall script for the AG implementation is as below.

Let’s understand the script before implanting it.

  • Import the SqlServer module in your Windows PowerShell

  • Restore database MyNewDB on the secondary replica in the NORECOVERY mode from the specified backup file. Here, we prepare our secondary replica with the AG database from a primary replica database copy

  • The below scripts does the following tasks for us:
    • Define the primary and secondary replica PowerShell drive path and stored it into the $PrimaryServer and $SecondaryServer variables
    • We use the cmdlet New-SqlAvailabilityReplica to create the primary and secondary replica. It is similar to the AG wizard where we add all replicas and defines their availability mode, failover mode and endpoint URL
      • EndpointURL: Specify the endpoint in the format – TCP://[ServerName]:[PortNumber]
      • FailoverMode: Specify whether you require automatic or manual failover. For asynchronous mode, we can use only manual failover
      • AvailabilityMode: Specify whether you require synchronous or asynchronous AG mode
  • The below script uses the cmdlet New-SqlAvailabilityGroup for creating an availability group. Use the variable $PrimaryReplica, $SecondaryReplica in the AvailabiltyReplica parameter

  • The Join-SqlAvailabilityGroup joins the secondary replica to the availability group. You need to specify the availability group name created using the above script

  • The Add-SqlAvailabilityDatabase cmdlet adds the secondary database to the availability group. In the path, specify the path for the availability group of the secondary replica

  • In this part of the script, we use the New-SqlAvailabilityGroupListener cmdlet for configuring the availability group listener. For the listener, we require a static IP address, subnet mask and the listener port
    • StaticIP: In this parameter, we specify both the static IP address and its subnet mask
    • Port: Specify the listener port. Make sure the port is opened in the Windows firewall

Now, it’s time to execute the PowerShell Script for creating the SQL Server Always On Availability Group. In the output, it logs the commands in the console.

Configure the availability group, add database in the synchronous mode and create a SQL listener

You can see the configured availability group [AGusingPowerShell] and its primary replica [SQLNode2\INST1].

View PowerShell script output

You can connect to the primary replica, launch the AG dashboard. Everything looks good.

Launch the AG dashboard

Conclusion

In this article, we configured the SQL Server Always On Availability Group using Windows PowerShell scripts. You can use Windows PowerShell Scripts for automatically implementing an availability group based on the supplied inputs.

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Configure a SQL Server Always On Availability Group on the domain-independent Failover Cluster
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Groups
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups

Rajendra Gupta
Always On Availability Groups, PowerShell

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

1,929 Views