Select Page

AlwaysOn Availability Groups: Non-container Objects Synchronization Scripts

Anup Gopinathan | | April 7, 2020

Ever been in a situation where an AlwaysOn Availability Groups failed over to secondary? And then later you realized some important jobs are missing; or procedures are failing because the linked servers were not created or updated on secondary; or some logins are not working!

 
The AlwaysOn Availability Groups feature keeps the databases in sync for high availability, but the non-container objects needs to be copied over manually. The purpose of the script is to synchronize non-container objects (Logins, Linked servers and Jobs) between replicas or independent servers. This script automates the synchronization of these objects and make the failover seamless and thereby reducing human effort (and mistakes).

Additionally, it can be used between Mirrored or Log-shipped servers, as it was used for some of my clients/

There are three PowerShell-based scripts that will help to replicate three objects types Jobs, Linked servers and login. I kept it as three pieces so that each of the scripts can be easily customized to personal needs. I have listed the details of the scripts below.

SYNCHRONIZE LINKED SERVERS

The linked servers are scripted with a password and applied to the secondary servers. The PowerShell script will decrypt the remote passwords while applying the linked servers on the secondaries.

Yep, that is right, the remote passwords will be decrypted, so you do not need to know the password for the remote logins. A few of my clients have used this script (with a bit of modification) to identify the password set in the linked servers by the previous employee and is not documented.

Permission required

DAC connectivity to MSSQL instances

(needed to access registry key)

Dedicated Admins

DAC connectivity to MSSQL instances. The account running this script should be able to login as dedicated admin.
Sysadmin Sysadmin privileges to MSSQL instances
Local administrator privileges Local administrator privileges on the machine to access the registry for decrypting the password

 

Parameters

$instanceName Mandatory, String.

Value: Name of the sql instance the script is executed on. If you are running the script from SQL agent jobs, you can use “$(ESCAPE_SQUOTE(SRVR))” when the step type is CMD

$RECREATELINKEDSERVERS

 

Optional, Boolean

Value $true/$false, default is $false

If $true existing matching linked servers by name will be dropped and new scripts will be applied.

If $false, skips the linked servers that already exists.

$SCRIPTasFILE Optional, String

Value: path where the script should be stored in file

Creates a .sql file for each object with code. For security the passwords are replaced by hash (####)

$secondary

 

Optional, String

Value: Secondary/ replica SQL instance name

If blank, the script will look for Availability group secondary or Mirroring partner. The scripts are applied to the secondary server. The scripts are applied to multiple replicas if they exist.

 

If a secondary instance name is provided it will directly apply the script on it.  This is the parameter you want to use especially for logshipping or refresh process or migration

 

COMMAND:

script_SYNC_Linkedserver.ps1 -instanceName(required) -RECREATELINKEDSERVERS(optional)

-SCRIPTasFILE(optional) -secondary(optional)

 

EXAMPLE:

The blow command can be used when running via job and the step type is CMD

c:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe “C:\Scripts\AOAG_SYNC_linkedservers-jobs-logins\script_SYNC_Linkedserver.ps1"

-instanceName $(ESCAPE_SQUOTE(SRVR))

-SCRIPTasFILE "C:\Scripts"

-RECREATELINKEDSERVERS $true

-secondary "LABSQL******\K*****S"

SYNCHRONIZE LOGINS

I modified the sp_revlogin script to work with the powershell scripts. Same as linked servers it will identify find the replicas and apply the script on all replicas.

Permission required for the account executing the script

Sysadmin Sysadmin privileges to MSSQL instances

 

Parameters

$instanceName Mandatory, String.

Value: Name of the sql instance the script is executed on. If you are running the script from SQL agent jobs, you can use “$(ESCAPE_SQUOTE(SRVR))” when the step type is CMD

$RECREATELOGINS

 

Optional, Boolean

Value $true/$false, default is $false

If $true existing matching logins by name will be dropped and created.

If $false, skips the logins that already exists.

$SCRIPTasFILE Optional, String

Value: path where the script should be stored in file

Creates a .sql file for each object with code. The passwords are encrypted as sp_revlogins does

$secondary

Optional, String

Value: Secondary/ replica SQL instance name

If blank, the script will look for Availability group secondary or Mirroring partner. The scripts are applied to the secondary server. The scripts are applied to multiple replicas if they exist.

 

If a secondary instance name is provided it will directly apply the script on it.  This is the parameter you want to use especially for logshipping or refresh process or migration

$excludelogins

 

Optional, String

Value: Comma separated list of logins to exclude from sync.

 

COMMAND:

script_SYNC_Logins.ps1 -instanceName(required)    -RECREATELOGINS(optional)

-SCRIPTasFILE(optional)      -secondary(optional)

 

EXAMPLE:

c:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe <path>\script_SYNC_Logins.ps1

-instanceName "Srv01"

-SCRIPTasFILE "X:\folder"

-RECREATELOGINS $false

-secondary "Srv02" (only to sync between two independent servers)]

 

c:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

W:\Datavail_Scripts\Powershell\script_SYNC_Logins.ps1

-instanceName $(ESCAPE_SQUOTE(SRVR))

-SCRIPTasFILE "W:\Datavail_Scripts\Objects_Scripts"

-RECREATELOGINS $true

-excludelogins “GODDBA,domain\account’

SYNCHRONIZE JOBS

Scripts the jobs using SQL Server management objects (SMO). You can choose whether the job should be kept disabled on secondary after creating them. In addition to that you can specify job categories to synchronize selective jobs.

Permission required for the account executing the script

Sysadmin Sysadmin privileges to MSSQL instances

 

Parameters

$instanceName Mandatory, String.

Value: Name of the sql instance the script is executed on. If you are running the script from SQL agent jobs, you can use “$(ESCAPE_SQUOTE(SRVR))” when the step type is CMD

$RECREATEJOBS

 

Optional, Boolean

Value $true/$false, default is $false

If $true existing matching logins by name will be dropped and created.

If $false, skips the logins that already exists.

$SCRIPTasFILE Optional, String

Value: path where the script should be stored in file

Creates a .sql file for each object with code. The passwords are encrypted as sp_revlogins does

$secondary

 

Optional, String

Value: Secondary/ replica SQL instance name

If blank, the script will look for Availability group secondary or Mirroring partner. The scripts are applied to the secondary server. The scripts are applied to multiple replicas if they exist.

 

If a secondary instance name is provided it will directly apply the script on it.  This is the parameter you want to use especially for logshipping or refresh process or migration

$DisableJobs Optional, Boolean

Value: $true/$false

If $true, script will disable the jobs after creating them

If $false, script will be created as scripted from Primary instance

$jobCategory

 

Optional, String

Value: Comma separated list job category.

This parameter can be used to synchronize jobs in specific  job categories only.

Accepts, multiple, direct values(arguments) and wild cards values

.* – Includes jobs in all category

.*AOAG* – Includes jobs in category that contains word AOAG

.*AAG.*,.*HAG.* – Includes jobs in category that contains words AAG or HAG

 

COMMAND:

script_SYNC_SQLAgentJobs.ps1

-instanceName(required)

-$RECREATEJOBS(required)

-SCRIPTasFILE(optional)

-secondary(optional)

-DISABLEJOBS (optional)

-jobCategory (optional)

 

EXAMPLE:

c:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe <path>\script_SYNC_SQLAgentJobs.ps1

-instanceName "Srv01"

-SCRIPTasFILE "X:\folder"

-RECREATEJOBS $true

-DISABLEJOBS $true

-secondary (only of sync in independent servers)

-jobCategory “.*AAG.*,.*HAG.*“
 

I hope these scripts help you in the event an AlwaysOn Availability fails over into secondary. If you’re looking for support with your SQL Server databases, get in touch with us today. For more resources on SQL Server, click here.

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

ORA-12154: TNS:could not resolve the connect identifier specified

Most people will encounter this error when their application tries to connect to an Oracle database service, but it can also be raised by one database instance trying to connect to another database service via a database link.

Jeremiah Wilton | March 4, 2009

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Using Nulls in DB2

If a column “value” can be null, it can mean one of two things: the attribute is not applicable for certain occurrences of the entity, or the attribute applies to all entity occurrences, but the information may not always be known.

Craig Mullins | April 6, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS