Azure SQL Managed Instance – Sync Agent Jobs and Logins in Failover Groups

[narenan]

Introduction

In an Azure SQL Managed Instance setup with Failover Group, the schema, data, and database-level users will always be synced between primary and secondary instance. As of October 2021, there is no sync mechanism for SQL Server Agent Jobs or Server Logins/Roles because the functionality of replicating system databases does not exist. This blog provides guidance and scripts to implement a workaround solution to have the Agent Jobs and Server Logins/Roles synced between primary and secondary instances.

Sync Agent Jobs

The objective of syncing Agent jobs is achieved by having a custom database named “AgentDB” in the MI instance which stores the latest job definitions from primary. There will be a SQL Agent Job that runs in both primary and secondary which will monitor on a schedule for failover events.

In the event of a failover, the jobs from the primary managed instance will be automatically created in a secondary managed instance by using the definition stored in the AgentDB.

The reason for choosing the AgentDB as the main information holder is due to the following:

1. It avoids the need for a Linked Server between the managed instances.
2. To isolate the objects required for the sync mechanism which else may have led to interfering with other user databases and schemas. If this is not wished then the scripts can be modified to use an existing user database.

Limitations

There are certain limitations in the workaround which should be noted before using the scripts.

1. If a job is in-progress during failover, all the progress made will be lost. The secondary jobs will start only from the first step.
2. When job switching occurs between primary and secondary, the history will be lost as the jobs are deleted and created.
3. Instant specific jobs like primary only jobs and secondary only jobs is not available and can be extended based on the available scripts.

Solution Components

The solution contains two major components, A user database “AgentDB” and a scheduled job “SyncLoginsAndAgentJobs”.

Custom Database - AgentDB

The AgentDB contains the following supporting tables and procedures for syncing the jobs:

1. Tables:
   1. Syscategories: Stores the job categories
   2. Sysfailoverhistory: Stores cluster failover history information
   3. Sysjobs: Stores agent job details
   4. Sysjobschedules: Stores job and schedule relation details
   5. Sysjobservers: stores job servers’ information
   6. Sysjobsteps: Stores all job step details
   7. Sysschedules: Stores all available schedules for the agent
2. Procedures:
   1. SyncAgentJobs: This is the main procedure that acts as an entry point for Syncing Agent jobs across primary and secondary managed instances.
   2. BackupAgentJobs: This procedure copies the job definitions from msdb system tables to AgentDB tables with a similar structure. 
   3. CreateJobsInNewPrimary: This procedure creates new jobs in secondary after the primary failover to secondary.
   4. DisableJobsInSecondary: This procedure is used to disable all the common jobs on the secondary instance which were created as part of the failover process when the primary is available and takes over the primary role.

Sync Scenarios

The default setup in two managed instances, Instance A and Instance B.

1. Instance A – Primary
2. Instance B – Secondary

Scenario 1: Instance A is Primary

When Instance A is primary, SQL Agent job contents from msdb will be copied from the local MSDB to the AgentDB and into the respective tables.

Scenario 2: Instance B is Primary after Failover

When Instance B becomes primary after failover happens, the SQL agent job contents from AgentDB are used to create or sync jobs in the SQL Agent running in Instance B.

Agent DB databases will be kept in sync between Instance A and Instance B by using the standard database replication feature of failover groups.

Sync Server Logins/Roles

Ensure AgentDB is fully synced between primary and secondary before continuing with the following Login Sync section.

The additional functionality of syncing logins is also achieved by using the same aforementioned custom user database “AgentDB” in the managed instance which stores the latest logins, roles, and permission definitions from the primary managed instance. There will be a SQL Agent Job which runs in both primary and secondary which will take the latest job definitions synced to the secondary and create the logins automatically. Both AAD and SQL logins are handled.

The Sync Agent Job can be scheduled to run at user defined intervals, the default value is to run every 2 minutes.

Limitations

There are certain limitations in the workaround which should be noted before using the scripts.

1. If a Login/Role creation is in-progress during failover, all the progress made will be lost. The Secondary server won’t have these logins and associated access and permissions.

Solution Components

The solution contains two major components, A user database called “AgentDB” and a scheduled job called “SyncLoginsAndAgentJobs”.

Database objects - AgentDB

The AgentDB contains the following supporting tables and procedures for syncing the logins/roles:

1. Tables:
   1. dbo.syssqllogins: Contains one row for each login account.
   2. dbo.sysserverpermissions: Stores permissions given to any principal
   3. dbo.sysserverprincipals: Contains a row for every server-level principal.
      1. S = SQL login
      2. U = Windows login
      3. E = External Login (AAD)
      4. G = Windows group
      5. X = External Group (AAD)
      6. R = Server role
      7. C = Login mapped to a certificate
      8. K = Login mapped to an asymmetric key
   4. dbo.sysserverrolemembers: Stores one row for each member of each fixed and user-defined server role.

2. Procedures:
   1. SyncLoginsRolePermissions: This is the main procedure which acts as an entry point for Syncing Logins jobs across primary and secondary MI instances.
   2. BackupLoginsRolePermissions:This procedure copies the login, roles, and permission definitions from system tables to AgentDB tables with similar structure.
   3. CreateLoginsInSecondary: This procedure creates/updates/deletes logins, roles, and permissions in secondary based on the latest definitions copied from primary.

SQL Agent Job – SyncLoginsAndAgentJobs

This is a SQL agent job which runs in both the primary managed instance as well as the secondary managed instance with a recurring schedule of 2 minutes. The agent job calls two stored procedures “SyncAgentJobs” and “SyncLoginsRolesPermissions” held within the AgentDB. The job schedule can be modified based on custom requirement. Any new jobs created in primary will be created in secondary as disabled. Also, any changes made to the jobs in the primary will be carried over to the secondary but disable state is maintained.

Deployment Steps

To deploy the solution, follow the below steps in order (Scripts are located here),

1. Enable Failover cluster in Azure SQL Managed Instance.
2. Check and verify if primary and secondary databases are online and accessible.
3. Run 01_Create_AgentDB.sql in primary to create AgentDB database
4. Run 02_Create_Tables.sql in primary to create necessary tables.
5. Run 03_Create_Procedures.sql in primary to create necessary procedures.
6. Run 04_Create_Agent_Job.sql in primary to create Sync Agent Job in SQL Agent.
7. Before moving to the next step, ensure AgentDB is fully synced between primary and secondary.
8. Run 04_Create_Agent_Job.sql in secondary to create Sync Agent Job in SQL Agent.
9. Validate the solution functionality by doing a manual failover.

Feedback and suggestions

If you have feedback or suggestions for improving this data migration asset, please contact the Azure Databases SQL Customer Success Engineering Team. Thanks for your support!