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.
Blog Author
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.
Popular Posts
How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
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.
Scripting Out SQL Server Logins, Server Role Assignments, and Server Permissions
This blog reviews how you can generate scripts for SQL server logins, role assignments, and server permissions for a smooth migration.