dbatools.io = command-line SQL Server Management Studio: AlwaysOn Availability Groups

dbatools = command-line SSMS

This post is part of the series showing practical usage examples. The main post covering links to all posts can be found here: dbatools.io = command-line SQL Server Management Studio: Table of contents.

dbatools commands used in this post:

AlwaysOn Availability Groups

The feature that has been introduced in SQL 2012 is quite an important one. Also, it requires a number of clicks there and there in SSMS. Luckily for us dbatools have set of commands to manage AlwaysOn Availability Groups from almost every angle. We will start with enabling HADR which is done in Configuration Manager rather than SSMS.

SSMS

dbatools: Enable-DbaAgHadr

dbatools: Enable-DbaAgHadr

# Enable HADR / AlwaysOn 
Enable-DbaAgHadr -SqlInstance $server
<#
ComputerName InstanceName SqlInstance IsHadrEnabled
------------ ------------ ----------- -------------
SQL01        MSSQLSERVER  SQL01                True
#>

# in order to apply the changes - restart the SQL service
Restart-DbaService -ComputerName $server -Type Engine
<#
ComputerName : SQL01
ServiceName  : MSSQLSERVER
InstanceName : MSSQLSERVER
ServiceType  : Engine
State        : Running
Status       : Successful
Message      : Service was successfully restarted.
#>

dbatools: Disable-DbaAgHadr

# to disable the feature use 
Disable-DbaAgHadr

Create AG, replicas, listener

Let’s create an AG, add a replica and a listener.

SSMS

dbatools: New-DbaAvailabilityGroup

dbatools: New-DbaAvailabilityGroup

# add a new availability group
New-DbaAvailabilityGroup -Primary $server -Name NewAG -FailoverMode External
<#
ComputerName               : SQL01
InstanceName               : MSSQLSERVER
SqlInstance                : SQL01
LocalReplicaRole           : Primary
AvailabilityGroup          : NewAG
PrimaryReplica             : SQL01
ClusterType                : External
DtcSupportEnabled          : False
AutomatedBackupPreference  : Secondary
AvailabilityReplicas       : {SQL01}
AvailabilityDatabases      : {}
AvailabilityGroupListeners : {}
#>

Once the AG is created we can add replicas.

SSMS

dbatools: Add-DbaAgReplica

dbatools: Add-DbaAgReplica

# add new replica
Get-DbaAvailabilityGroup -SqlInstance $server -AvailabilityGroup MikeyAG | Add-DbaAgReplica -SqlInstance $server2 -FailoverMode Manual
<#
ComputerName               : SQL01
InstanceName               : MSSQLSERVER
SqlInstance                : SQL01
AvailabilityGroup          : MikeyAG
Name                       : SQL02
Role                       : Secondary
RollupSynchronizationState : NotSynchronizing
AvailabilityMode           : SynchronousCommit
BackupPriority             : 50
EndpointUrl                : TCP://SQL02.bronowski.it:5022
SessionTimeout             : 10
FailoverMode               : Manual
ReadonlyRoutingList        : {}
#>

Another step would be to add a listener to our AG.

SSMS

dbatools: Add-DbaAgListener

dbatools: Add-DbaAgListener

# create AG listener
Add-DbaAgListener -SqlInstance $server -AvailabilityGroup MikeyAG -IPAddress $listener -Name AGListener
<#
ComputerName           : SQL01
InstanceName           : MSSQLSERVER
SqlInstance            : SQL01
AvailabilityGroup      : MikeyAG
Name                   : AGListener
PortNumber             : 1433
ClusterIPConfiguration : 
#>

dbatools: Remove-DbaAgListener

# Remove existing AG listener - Get it first and pipe to the remove command
Get-DbaAgListener -SqlInstance $server | Remove-DbaAgListener
<#
ComputerName      : SQL01
InstanceName      : MSSQLSERVER
SqlInstance       : SQL01
AvailabilityGroup : MikeyAG
Listener          : MikeyAG
Status            : Removed
#>

And finally, let’s add some databases.

SSMS

dbatools: Add-DbaAgDatabase

dbatools: Add-DbaAgDatabase

# create a new database
New-DbaDatabase -SqlInstance $server -Name NewDB4AG

# get a full backup
Backup-DbaDatabase -SqlInstance $server -Database NewDB4AG

# add the database to the AG
Add-DbaAgDatabase -SqlInstance $server -AvailabilityGroup MikeyAG -Database NewDB4AG -Secondary $server2
<#
ComputerName         : SQL01
InstanceName         : MSSQLSERVER
SqlInstance          : SQL01
AvailabilityGroup    : MikeyAG
Replica              : SQL01
Name                 : NewDB4AG
SynchronizationState : Synchronized
IsFailoverReady      : True
IsJoined             : True
IsSuspended          : False

ComputerName         : SQL02
InstanceName         : MSSQLSERVER
SqlInstance          : SQL02
AvailabilityGroup    : MikeyAG
Replica              : SQL02
Name                 : NewDB4AG
SynchronizationState : Synchronized
IsFailoverReady      : True
IsJoined             : True
IsSuspended          : False
#>

Checking all is in sync

The SQL Server Management Studio has a dashboard where you can check health of your Availability Groups. I have not seen a dashboard using dbatools, but there are commands than can pull out same information.

SSMS

dbatools: Get-DbaAgDatabase

dbatools: Get-DbaAgDatabase

# See the database details
Get-DbaAgDatabase -SqlInstance $server -AvailabilityGroup MikeyAG -Database NewDB4AG
<#
ComputerName         : SQL01
InstanceName         : MSSQLSERVER
SqlInstance          : SQL01
AvailabilityGroup    : MikeyAG
Replica              : SQL01
Name                 : NewDB4AG
SynchronizationState : Synchronized
IsFailoverReady      : True
IsJoined             : True
IsSuspended          : False
#>

Synching the AG and Failing it over

One of the main features of AlwaysOn availability groups is option to fail the group over to the secondary node. Having Synchronous commit mode will help to prevent data loss.

SSMS

dbatools: Set-DbaAgReplica

dbatools: Set-DbaAgReplica

# change the mode from Async to Sync
Set-DbaAgReplica -SqlInstance $server -Replica $server -AvailabilityGroup MikeyAG -AvailabilityMode SynchronousCommit
<#
ComputerName               : SQL01
InstanceName               : MSSQLSERVER
SqlInstance                : SQL01
AvailabilityGroup          : MikeyAG
Name                       : SQL01
Role                       : Primary
ConnectionState            : Connected
RollupSynchronizationState : Synchronized
AvailabilityMode           : SynchronousCommit
BackupPriority             : 50
EndpointUrl                : TCP://SQL01.bronowski.it:5022
SessionTimeout             : 10
FailoverMode               : Manual
ReadonlyRoutingList        : {}
#>

Once the AG is synchronised we can fail it over.

SSMS

dbatools: Invoke-DbaAgFailover

dbatools: Invoke-DbaAgFailover

# failover the AG (using Force as those AG are clusterless)
Invoke-DbaAgFailover -SqlInstance $server2 -AvailabilityGroup MikeyAG -Force
<#
ComputerName               : SQL02
InstanceName               : MSSQLSERVER
SqlInstance                : SQL02
LocalReplicaRole           : Primary
AvailabilityGroup          : MikeyAG
PrimaryReplica             : SQL02
ClusterType                : None
DtcSupportEnabled          : False
AutomatedBackupPreference  : Secondary
AvailabilityReplicas       : {SQL01, SQL02}
AvailabilityDatabases      : {MikeyDB}
AvailabilityGroupListeners : {}
#>

Suspend\Resume data movement

Two useful alternatives to SSMS options.

SSMS

dbatools: Suspend-DbaAgDbDataMovement

dbatools: Suspend-DbaAgDbDataMovement

Suspend-DbaAgDbDataMovement -SqlInstance $server -AvailabilityGroup MikeyAG -Database MikeyDb
<#
ComputerName         : SQL01
InstanceName         : MSSQLSERVER
SqlInstance          : SQL01
AvailabilityGroup    : MikeyAG
Replica              : SQL01
Name                 : MikeyDb
SynchronizationState : NotSynchronizing
IsFailoverReady      : True
IsJoined             : True
IsSuspended          : True
#>

dbatools: Resume-DbaAgDbDataMovement

Resume-DbaAgDbDataMovement -SqlInstance $server -AvailabilityGroup MikeyAG -Database MikeyDb
<#
ComputerName         : SQL01
InstanceName         : MSSQLSERVER
SqlInstance          : SQL01
AvailabilityGroup    : MikeyAG
Replica              : SQL01
Name                 : MikeyDb
SynchronizationState : Synchronized
IsFailoverReady      : True
IsJoined             : True
IsSuspended          : False
#>

Bonus: Sync it all up

At some point, you have lots of different objects between replicas like logins, credentials, agent jobs, other agent objects. In some scenarios, it is good to have them all in sync. I am going to show you the -WhatIf output just to get an idea.

dbatools: Sync-DbaAvailabilityGroup

# see what can be synchronised between replicas.
Sync-DbaAvailabilityGroup -Primary $server -Secondary $server2 -WhatIf

<#
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing SQL Server Configuration from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing logins from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Updating database owners to match newly migrated logins from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing custom errors (user defined messages) from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing SQL credentials from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing database mail from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing linked servers from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing System Triggers from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing Agent Categories from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing Agent Operators from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing Agent Alerts from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing Agent Proxy Accounts from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing Agent Schedules from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing Agent Jobs from SQL01 to SQL02".
What if: Performing the operation "Sync-DbaAvailabilityGroup" on target "Syncing login permissions from SQL01 to SQL02".
#>

dbatools have a couple more functions to work with AGs, but I am going to leave it to you as homework 😉

Thank you,
Mikey

2 Comments on "dbatools.io = command-line SQL Server Management Studio: AlwaysOn Availability Groups"



Leave a Reply