Synchronizing SQL Server Instance Objects in an Availability Group

Availability groups can no longer be considered new. The feature was released in SQL Server 2012 but I often notice that SQL Server Failover Cluster Instances (FCIs) are much better understood. For a very long time, when people thought about SQL Server they thought about the entire instance as a unit. Microsoft has been implementing new features and services which begin to segment the concept of an instance from the concept of a database.

With an FCI, which most seem to be comfortable with, your entire SQL Server instance gains high-availability. With an Availability Group (AG), that is not the case.

“An availability group supports a replicated environment for a discrete set of USER databases, known as availability databases.” (emphasis is mine) – MSDN

Since AGs only replicate user databases, that means that all data stored in master, model, and msdb will not be handled. Replication of your SQL Agent jobs, linked servers, logins, credentials, and all over instance objects, will not be handled for you. This means that you have to implement a process to do keep all of your cluster nodes synchronized.

Options

I wrote about this same topic about two years ago. In that article, I expressed a release based method which would be hooked into your database lifecycle management (DLM) process. Over the years, I found that very few people adopted that way of dealing with their server objects. Shifting-left something like security and other instance level objects did not appeal to many and the method was highly specialized to development shops that used desired state configuration tools to manage their DLM.

This article is focusing on a new method that I wrote which is much more generic and fitting for a strong separation of duties between the production DBAs and the development / DevOps teams.

The process

The solution that you see below is a PowerShell script which is executed by a SQL Agent job on the primary replica of the AG. While it only executes on the primary replica, the job must exist on all nodes of the cluster and be smart enough to know which node is allowed to execute and which are secondary replicas.

The core functionality all resides within the PowerShell script. In the diagram, I show the script in a Microsoft Azure File Share. This is definitely not a requirement. If you prefer, the script could reside on an on-premises file server, locally on each node of your cluster, or even pasted into the SQL Agent job step itself. When I developed this solution, my servers were Azure VMs and I wanted a single piece of code to manage, rather than ensuring that the script was updated on all nodes of the cluster at all times.

The PowerShell script relies on the community project dbatools and follows this process flow.

A notable limitation of this process is that it does not update existing objects. Jobs which already exist but were updated, will not be altered. I chose to omit that functionality because it presents merge complications and problems. For example, the cleanest way to handle the process would be to drop and create the object each time the synchronization runs. If that happened, however, there would be gaps when logins didn’t exist and applications would fail to connect, SQL Agent jobs would lose history, and/or the processing of a job would fail because it was dropped part way through executing.

Prerequisites and community code

For this script to work, there are two prerequisites. The prerequisites are required to be installed on all nodes of the cluster.

Dbatools PowerShell module version 0.9.24 or higher.
PowerShell version 5.0 or higher.

What is dbatools?

Dbatools is a community developed, open-source, PowerShell module. It contains over 200 cmdlets designed to help the DBA with SQL Server administration, migrations, and best practices.

I chose to use this tool over rolling my own script because this is a supported platform with a team of experts constantly fixing bugs and adding new features. The long-term maintenance of this entire solution is nearly zero because the dbatools contributors will handle all of the tedium for you. They will update the cmdlets for new versions of SQL Server and they have hundreds of automated unit tests to stress this code base with every change.

Even outside of this one-use case, I highly recommend to look into what dbatools has to offer and how it can help optimize your day.

Installation

You can download and install the prerequisites yourself. I prefer scripting out installs, however. For that I recommend reviewing this article by Mike Robbins on scripting out the installation of PowerShell version 5.0 and you can use the below script to install dbatools by running a PowerShell console window as administrator.

# Install the installer

if ((Get-Module -ListAvailable -Name PowerShellGet) -eq $null)

{

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force | Out-Null

}

Import-Module PowerShellGet | Out-Null

# Install dbatools

Install-Module dbatools -MinimumVersion 0.9.24 -AllowClobber -Force

The code

Full PowerShell script and SQL Agent job T-SQL script available for download here as a zip file.

SQL Agent job

You will need to create a SQL Agent job to run the PowerShell script. In the downloadable content, there is the full T-SQL required to create the job, but edits are required. As mentioned above, I chose to use an Azure File Share to store my PowerShell script. If this is the direction that you will go, then you will need to update the script with:

The storage account name.
The share name.
The storage account’s primary or secondary key.

If you choose to use an on-premises file server or local directories, you will need to adjust the script accordingly. No matter which direction you take, you will need to create a proxy account with access to each node of your cluster and update the scripts:

Domain name.
Port number.
Proxy name.

sp_add_jobstep parameters which need review / editing:

@subsystem=N'CmdExec',

@command=N'cmd /c net use M: \\mystorageaccountname.file.core.windows.net\maintenancescripts "WQblqxDeyorCdof1I+PfakeprimarykeyFDWVVdo6NENQnof4/2AfkSSjGiTmZ5XtMsQ==" /user:Azure\mystorageaccountname && C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe "& M:\Sync-SqlInstanceObjects.ps1 -DomainName ''mydomain.com'' -Port 1433;" && net use M: /delete /Y',

@proxy_name=N'DOMAIN\service_account_name'

Downloads

Sync-SqlInstanceObjects-Job-and-Script

Appendix A – The PowerShell script

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

.DESCRIPTION

This script will detect your Availability Group primary replica and copy all of its instance level objects to all of the secondary replicas within the Availability Group.

.EXAMPLE

./Sync-SqlInstanceObjects.ps1 -DomainName "mydomain.com" -Port 1433

.NOTES

One limitation of this script is that it assumes you only have one availability group.

.LINK

https://www.sqlhammer.com/synchronizing-server-objects-for-availability-groups/

[CmdletBinding(SupportsShouldProcess=$true)]

Param

(

[Parameter(Mandatory=$false,

ValueFromPipeline=$true,

ValueFromPipelineByPropertyName=$true,

HelpMessage="Domain that this Availability Group is in. I.e. 'mydomain.com'")]

[string]$DomainName,

[Parameter(Mandatory=$false,

ValueFromPipeline=$true,

ValueFromPipelineByPropertyName=$true,

HelpMessage="Port number that all connections will be made under.")]

[bigint]$Port

)

Write-Output "Sync started."

#Error handling

$ErrorActionPreference = "stop";

Trap

{

$err = $_.Exception

while ( $err.InnerException )

{

$err = $err.InnerException

Write-Output $err.Message

};

}

# Helper functions

function Get-FullConnectionName ($ServerName)

{

return "$ServerName.$DomainName,$Port"

}

function Compare-SystemVersion ($Version1, $Version2)

{

try

{

if ([System.Version]$Version1 -lt [System.Version]$Version2)

{

return -1

}

elseif ([System.Version]$Version1 -eq [System.Version]$Version2)

{

return 0

}

elseif ([System.Version]$Version1 -gt [System.Version]$Version2)

{

return 1

}

catch

{

return -2

}

# Prerequisites

try

{

Write-Output "Valiating prerequisites."

<# Works but needs to run as admin which our SQL Agent is not going to do.

# Code remains for reference of how to initiate a new node

if ((Get-Module -ListAvailable -Name dbatools) -eq $null `

-or ((Compare-SystemVersion (Get-Module -ListAvailable -Name dbatools).Version "0.9.24") -lt 0))

{

# Install the installer

if ((Get-Module -ListAvailable -Name PowerShellGet) -eq $null)

{

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force | Out-Null

}

Import-Module PowerShellGet | Out-Null

# Install dbatools

Install-Module dbatools -MinimumVersion 0.9.24 -AllowClobber -Force

}

if ((Get-Module -Name dbatools) -eq $null)

{

Import-Module dbatools | Out-Null

}

Write-Output "Prerequisites loaded."

}

catch

{

Write-Error $_.Exception.Message -EA Continue

Write-Error "One or more of the prerequisites did not load. Review previous errors for more details." -EA Stop

return

}

# Detect Availability Group Primary replica

Write-Output "Begin query of Availability Group state."

$DataSource = (Get-FullConnectionName $env:COMPUTERNAME)

$Query = @"

SELECT replica_server_name

, HAGS.primary_replica

FROM sys.availability_replicas AR

INNER JOIN sys.dm_hadr_availability_group_states HAGS

INNER JOIN sys.availability_groups AG ON AG.group_id = HAGS.group_id

ON HAGS.group_id = AR.group_id;

$AGStates = Invoke-Sqlcmd2 -ServerInstance $DataSource -Query $Query -ConnectionTimeout 30

if(([DBNull]::Value).Equals($AGStates[0].primary_replica))

{

Write-Error "Availability Group state query returned no results. Confirm that you connected to a SQL Server instance running an Availability Group. No work was accomplished."

return

}

Write-Output "Completed query of Availability Group state."

# Only runs if it is the primary, secondaries remain idle.

if($AGStates[0].primary_replica.ToLower().CompareTo($env:COMPUTERNAME.ToLower()) -eq 0)

{

foreach($replica in $AGStates)

{

# Skip this iteration because source and destination would be the same

if($replica.replica_server_name.ToLower().CompareTo($env:COMPUTERNAME.ToLower()) -eq 0)

{

continue

}

$SecondaryReplica = (Get-FullConnectionName $replica.replica_server_name)

Write-Output "Copying objects from $DataSource to $SecondaryReplica"

# Copy objects

Write-Output "Copying Logins."

Copy-DbaLogin -Source $DataSource -Destination $SecondaryReplica

Write-Output "Copying BackupDevices."

Copy-DbaBackupDevice -Source $DataSource -Destination $SecondaryReplica

Write-Output "Copying Endpoints."

Copy-DbaEndpoint -Source $DataSource -Destination $SecondaryReplica

Write-Output "Copying Linked Servers."

Copy-DbaLinkedServer -Source $DataSource -Destination $SecondaryReplica

Write-Output "Copying Agent Categories."

Copy-DbaAgentCategory -Source $DataSource -Destination $SecondaryReplica

Write-Output "Copying Shared Schedules."

Copy-DbaAgentSharedSchedule -Source $DataSource -Destination $SecondaryReplica

Write-Output "Copying Operators."

Copy-DbaAgentOperator -Source $DataSource -Destination $SecondaryReplica

Write-Output "Copying Credentials."

Copy-DbaCredential -Source $DataSource -Destination $SecondaryReplica

Write-Output "Copying Proxy Account."

Copy-DbaAgentProxyAccount -Source $DataSource -Destination $SecondaryReplica

Write-Output "Copying Job."

Copy-DbaAgentJob -Source $DataSource -Destination $SecondaryReplica

Write-Output "Copy complete from $DataSource to $SecondaryReplica"

}

else

{

Write-Output "No work accompished, this is not the primary node."

}

Write-Output "Sync complete."

Author
Recent Posts

Derik Hammer

Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability, disaster recovery, continuous integration, and automated maintenance. His experience has spanned database administration, consulting, and entrepreneurial ventures.

Derik thanks our #sqlfamily for plugging the gaps in his knowledge over the years and actively continues the cycle of learning by sharing his knowledge with all and volunteering as a PASS User Group leader.

View all posts by Derik Hammer