Rajendra Gupta
SQL Server Always On Availability Group dashboard

Refresh SQL Server Always On Availability Group databases using DBATools PowerShell

September 21, 2020 by

In the 20th article of the SQL Server Always On Availability Group series, we will explore the process to refresh an availability group database using SQL scripts. In this article, we will use the DBATools commands for this purpose.

Introduction

As we explored earlier, the database refresh for an availability group database requires a few additional steps. At a high level, it requires the following steps:

  • Remove the database from the primary replica of SQL Server Always On Availability Group
  • Restore a database from the production database backup
  • Add the database back to the availability group using manual or automatic seeding
  • Validate the AG dashboard synchronization
  • Failover to the current secondary replica and validate AG health

DBATools is a collection of useful PowerShell commands, and it helps database professionals to perform the task with minimum steps and less complexity. We have explored many useful DBATools commands on SQLShack. I would suggest you check out those articles as well.

Let’s explore useful DBATools commands for SQL Server Always On Availability Groups.

Install DBATools PowerShell module for SQL Server

To install the DBATools PowerShell module, run the following command. It downloads the module using an internet connection and installs it. You can refer to the article, DBATools PowerShell Module for SQL Server for detailed information.

DBATools powershell installation

Explore DBATools commands for SQL Server Always On Availability Groups

Once we have installed the DBATools in the system, you can try exploring the commands for SQL Server. Here, I specify a few useful commands for availability groups.

Get-DbaAgReplica cmdlet to retrieve AG information

We can retrieve information about the availability group replicas. You can identify primary, secondary replica instances using this module.

Get-DbaAgReplica cmdlet

It uses an argument -SqlInstanceName, and we can specify any of the primary replica, secondary replica or the SQL listener name in it.

In the below query, we input the SQL listener name (SQLShackLSN) and filtered output to return specific columns in the output such as Instance name, role, connection state, Synchronization status, Endpoint URL.

Example of Get-DbaAgReplica cmdlet

Similarly, if we specify the primary replica SQL instance in the -SqlInstanceName parameter, it returns the same information.

Specify listener or primary replica name

In the below script, we do the following tasks:

  • We used variable $SQLlistener and $availabiltyGroup to input the listener and availability group name
  • We used the Get-DbaAgReplica command to gather information about AG and stored this information in a data set $availabilityGroupReplicas
  • In the next part, we filtered the information from the data set variable
    • $primaryreplica: It stored the primary SQL instance in the AG
    • $secondaryreplica: It stored the secondary SQL instance in the AG

Remove-DbaAgDatabase cmdlet to remove the SQL Server Always On Availability Group database

It removes the database from the specified availability group. We specify the availability group name and the SQL instance to remove the specified database.

Remove-DbaAgDatabase cmdlet to remove the AG database

In this article, we need to restore the AG database [SQLShackDemo] from the production backup copy. Therefore, we need to remove this database from the availability group. Before dropping the database from the availability group, verify that AG is synchronized using a dashboard.

SQL Server Always On Availability Group dashboard

In the below script, we used the Remove-AGDatabase command and removed the database from the availability group. We stored the database name in a variable $databasename.

It asks for confirmation before proceeding with the operation. If you want to disable this prompt, add –Confirm:$false in the Remove-DbaAGDatabase command.

Confirmation prompt

In the output, it confirms the status that the database [SQLShackDemo] is removed from the availability group.

DB confirmation

You can verify in the AG dashboard that it does not show any database.

Dashboard error

Restore-DbaDatabase cmdlet to restore a SQL database

The Restore-DBADatabase command is for restoring the database backup. As we have removed the database from the availability group, it is similar to a standalone database. You can go through SQL Restore Database using DBATools for the detailed implementation of this command.

In the below script, we did the following tasks:

  • Defined a variable to store the backup directory
  • We already removed the database in the previous command, so commented the line for Remove-DbaAgDatabase
  • We use the Restore-DbaDatabase command and specify the database name, backup directory and use the option -WithReplca to replace the existing database on the primary replica
  • In my case, the backup file is C:\SQL\SQLShackDemo; therefore, I specify the path using the variables (“$backupDirectory\$databasename.bak) followed by backup file extension

DBATools restores the database with the specified configuration and gives the following output.

Restore-DbaDatabase cmdlet to restore a SQL database

Remove-DbaDatabase DBATools command

Earlier, we removed the database from the availability group using the Restore-DBADatabase cmdlet. It does not remove the secondary database. To remove the secondary database, we can use the Remove-DbaDatabase command of the DBATools.

In the Remove-DbaDatabase, we specify the secondary replica name (using variable $secondaryReplica), database name (using variable $databasename) and use the argument confirm:$false to disable confirmation prompt.

It dropped the database [SQLShackDemo] from the secondary replica [SQLAG2\INST1] as shown below:

Remove-DbaDatabase command

Add the database into the SQL Server Always On Availability Group

Once we have restored the database into the primary replica and dropped the existing database from the secondary replica, we can add it back to the availability group using the Add-DbaAgDatabase.

Add the database into the availability group

We can use the automatic seeding from SQL Server 2016 to initialize a database in the secondary replica. It eliminates the need to take a backup from the primary replica, restore on the secondary replica instance.

You can refer to this article, Automatic Seeding in Always On Availability Groups to understand automatic seeding process in detail.

In the Add-DbaAgDatabase command, we specify the primary replica, availability group under which this database should exist, database name and seeding mode. In case you use the backup restore method for secondary database initialization, use the manual seeding mode.

It adds the database successfully in the availability group replicas. As shown in the below screenshot, both the primary and secondary replica databases are in Synchronized mode.

DB synchronization

To validate manually, connect to the primary replica and launch the availability group dashboard. As shown below, the dashboard is healthy.

AG dashboard status

If you get regular database refresh requirements for the SQL Server Always On Availability Group, you can schedule the scripts, and it automates the tasks database refresh for you. You can also add commands to take production database backup and put it in a shared location. In this way, you do not require copy files into the development environment manually.

Conclusion

In this article, we explored useful commands for DBATools PowerShell to refresh a SQL Server Always On Availability Group database. DBATools scripts are easy to write and implement. It makes database professional life more manageable. I recommend you to explore these as per your requirements.

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
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
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

Rajendra Gupta
341 Views