Configure SQL Server on Linux high availability for metabases - Precisely Data Integrity Suite

Configure each SQL Server on Linux replica with the login, job, service, and filesystem requirements needed after failover.

1. Check whether the availability group is contained.

   SELECT name, is_contained FROM sys.availability_groups;

   If the is_contained column does not exist, the group is not contained.

2. For a standard availability group, retrieve the rpuser security identifier from the replica where the metabase was created.

   SELECT name, sid FROM sys.server_principals WHERE name = 'rpuser';

3. For a standard availability group, create the rpuser login with the same security identifier and password on every other replica.

   USE master;
   CREATE LOGIN rpuser
       WITH PASSWORD = '<same password as the original replica>',
            SID = 0x<sid from the query above>,
            DEFAULT_DATABASE = master;
   
   EXEC sp_addsrvrolemember 'rpuser', 'bulkadmin';

4. For a standard availability group, grant rpuser access to msdb.

   USE msdb;
   IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'rpuser')
       CREATE USER rpuser FOR LOGIN rpuser;
   EXEC sp_addrolemember 'SQLAgentReaderRole', 'rpuser';

5. Enable SQL Server Agent on every replica.

   systemctl status mssql-server-agent

   If SQL Server Agent is not enabled, enable it and restart SQL Server.

   sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
   sudo systemctl restart mssql-server

6. Create the backup directory on every replica.

   sudo mkdir -p /var/opt/mssql/backup
   sudo chown mssql:mssql /var/opt/mssql/backup

   The directory must be writable by the mssql service account. Connect CDC writes the metabase enrollment backup to this path, and your log backup job can also use it.

7. For a standard availability group, create CDC capture and cleanup jobs for each CDC-enabled source database on every replica where CDC was not originally enabled.

   USE [<source database>];
   
   IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = 'cdc.<source database>_capture')
       EXEC sys.sp_cdc_add_job @job_type = N'capture';
   
   IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = 'cdc.<source database>_cleanup')
       EXEC sys.sp_cdc_add_job @job_type = N'cleanup';

   This applies to source databases enabled for CDC, not to the metabase. Repeat this step whenever a new source database is added to a pipeline.

8. Verify the replica configuration.

   SELECT name, sid FROM sys.server_principals WHERE name = 'rpuser';
   
   SELECT dp.name FROM msdb.sys.database_principals dp WHERE dp.name = 'rpuser';
   
   SELECT name, enabled FROM msdb.dbo.sysjobs WHERE name LIKE 'cdc.%';

   For a contained availability group, the rpuser, msdb, and CDC job checks confirm that the contained system databases replicated as expected. In contained groups, Connect CDC must connect through the contained availability group listener so it resolves to the contained master and msdb.

Add each SQL Server metabase to your transaction log backup schedule.

1. Add each metabase to the same log backup routine used for other availability group databases.
   If your maintenance jobs already enumerate all availability group databases in FULL recovery, each new metabase is covered automatically.
2. Keep each metabase in FULL recovery.
   Do not change the metabase to SIMPLE recovery. A database in SIMPLE recovery cannot remain in an availability group.

Drop leftover secondary metabase copies after deleting a metabase from the Connect CDC portal.

1. On each secondary replica, find databases in RESTORING state that are no longer availability group members.

   SELECT d.name, d.state_desc, d.create_date
   FROM sys.databases d
   LEFT JOIN sys.availability_databases_cluster adc
          ON adc.database_name = d.name
   WHERE d.state_desc = 'RESTORING'
     AND adc.database_name IS NULL
     AND d.database_id > 4
   ORDER BY d.create_date;

2. Confirm that each candidate database is a deleted Connect CDC metabase.
   A database in RESTORING state that is no longer in an availability group can also be a log shipping secondary or a restore that is deliberately in progress. If you use a metabase naming convention, add it to the query before dropping anything.
3. Verify that the metabase is not online and is not still in the availability group.

   SELECT name, state_desc FROM sys.databases WHERE name = '<metabase>';
   
   SELECT COUNT(*) AS still_in_ag
   FROM sys.availability_databases_cluster
   WHERE database_name = '<metabase>';

4. Drop the secondary copy.

   DROP DATABASE [<metabase>];

   Warning: Never drop a database that is ONLINE or still an availability group member. In a distributed availability group, check the partner group replicas as well.

Confirm the target replica and metabases are ready before a planned availability group failover.

1. Allow any in-flight metabase creation or deletion to finish.
2. Confirm the target replica has been prepared.
   The target replica should have the required rpuser setup, msdb access, SQL Server Agent service, backup directory, and CDC jobs for its availability group type.
3. Confirm the metabase log backup job exists on the target replica.
4. Run the deleted-metabase sweep on secondary replicas.
5. Take a transaction log backup.
6. Confirm all synchronous-commit replicas report SYNCHRONIZED.

   SELECT ar.replica_server_name, ars.synchronization_health_desc
   FROM sys.dm_hadr_availability_replica_states ars
   JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id;

7. Record the current primary for comparison after failover.

Check Connect CDC metabases and CDC jobs after any planned or unplanned failover.

1. Identify the new primary replica.

   SELECT @@SERVERNAME AS instance, role_desc
   FROM sys.dm_hadr_availability_replica_states
   WHERE is_local = 1;

2. Connect through the listener and confirm it resolves to the new primary.

   SELECT @@SERVERNAME;

3. Confirm metabases are online and still availability group members.

   SELECT d.name, d.state_desc,
          CASE WHEN adc.database_name IS NULL THEN 'NOT IN AG' ELSE 'in AG' END AS ag_status
   FROM sys.databases d
   LEFT JOIN sys.availability_databases_cluster adc ON adc.database_name = d.name
   WHERE d.database_id > 4;

4. Check each metabase for orphaned users.

   USE [<metabase>];
   SELECT dp.name AS orphaned_user, dp.sid
   FROM sys.database_principals dp
   LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
   WHERE sp.sid IS NULL
     AND dp.type IN ('S', 'U')
     AND dp.name NOT IN ('guest', 'dbo', 'sys', 'INFORMATION_SCHEMA');

   No rows is the expected result. If rpuser appears and the login exists with a different security identifier, repair the mapping with ALTER USER rpuser WITH LOGIN = rpuser;.

5. Confirm CDC capture and cleanup jobs exist.

   SELECT name, enabled FROM msdb.dbo.sysjobs WHERE name LIKE 'cdc.%';

   Each CDC-enabled source database should have a _capture job and a _cleanup job. If either job is missing, create the missing jobs on the new primary.

6. Confirm the log backup job is present and enabled.

   SELECT name, enabled FROM msdb.dbo.sysjobs WHERE name LIKE '%log%backup%';

7. Sweep the former primary for leftover deleted metabase copies.
8. Verify in the Connect CDC portal that pipelines have reconnected through the listener and are capturing changes.

Change the rpuser password consistently across SQL Server on Linux replicas.

1. On each replica, change the rpuser password to the same value.

   ALTER LOGIN rpuser WITH PASSWORD = '<new password>';

2. Update the data connection in the Connect CDC portal.
3. Confirm pipelines reconnect successfully.

Confirm that metabase operations are directed to the global primary in a distributed availability group.

1. Confirm that the deployment uses a distributed availability group.

   SELECT name, is_distributed FROM sys.availability_groups;

2. Before creating or deleting a metabase, check whether the listener has resolved to a writable metabase instance.

   SELECT DATABASEPROPERTYEX(N'<an existing metabase>', 'Updateability') AS updateability;

   The value must be READ_WRITE. A value of READ_ONLY indicates a forwarder.

3. Check this instance's role in each availability group.

   SELECT ag.name, ag.is_distributed, ars.role_desc
   FROM sys.dm_hadr_availability_replica_states ars
   JOIN sys.availability_groups ag ON ag.group_id = ars.group_id
   WHERE ars.is_local = 1;

4. If the listener resolved to a forwarder, fail the distributed group back or direct the operation to the availability group that currently holds the global primary role.

Use common symptoms to identify missing replica preparation, routing, backup, or cleanup work.

1. For active rpuser sessions, identify the owning sessions.

   SELECT s.session_id, s.login_name, s.host_name, s.program_name,
          s.status, s.last_request_end_time, DB_NAME(s.database_id) AS database_name
   FROM sys.dm_exec_sessions s
   WHERE s.login_name = 'rpuser';

2. Stop the pipelines that own the sessions.
3. If sessions persist after the pipelines stop and you confirm the sessions are idle, stop the sessions.

   KILL <session_id>;

4. Enroll a metabase manually only when automatic enrollment did not take effect.

   USE master;
   ALTER DATABASE [<metabase>] SET RECOVERY FULL;
   BACKUP DATABASE [<metabase>] TO DISK = N'/var/opt/mssql/backup/<metabase>.bak' WITH INIT, FORMAT;
   ALTER AVAILABILITY GROUP [<ag name>] ADD DATABASE [<metabase>];

   Run manual enrollment on the global primary. Then join the database on each secondary by using your standard onboarding process or automatic seeding.