SQL Server – Adding Database with Service Broker to Availability Group

availability-groupsservice-brokersql serversql-server-2016sql-server-2017

  • Server 1 – SQL Server 2016 (Primary in existing AG)
  • Server 2 – SQL Server 2016 (Secondary in existing AG)
  • Server 3 – SQL Server 2016 (Offsite Secondary in existing AG)
  • Server 4 – SQL Server 2017 (New server, destined to become new primary)

An ~800GB database is happily running on the 2016 AG. It has Service Broker enabled and is running fine.

The plan is to restore the database onto Server 4, add it to the AG, then manually failover so Server 4 is the primary in order to upgrade Servers 1, 2 & 3 to SQL 2017.

Server 4 has a different drive configuration to the other Servers so in order to add the database to the AG, I am using dba-tools Restore-DbaDatabase command. The database must be left in NORECOVERY mode in order to finally add it to the AG.

The problem here is that Service Broker must be enabled via ENABLE_BROKER on the DB before it is added to the AG whereas this is not possible because of the NORECOVERY state of the DB. Also SET TRUSTWORTHY ON must be set.

Is there a way to achieve this without tearing down the whole AG?

I'm really trying to avoid this because it takes a long time to copy and restore backups onto the offsite server (Server 3) when initially joining the AG.

This guide has proved useful for the rest of the process.

Best Answer

Both SET TRUSTWORTHY ON and ENABLE_BROKER are database settings. When you backup and restore a database, these settings will be included in the bak-file and can be restored on the new SQL instance.
Probably, you'll need to make some server level configurations though, such as a service broker endpoint as described here

Edit: Just to clarify, when restoring the backup you don't need to specify the ENABLE_BROKER option. The broker settings will be synced from the primary replica as soon as you join the database to the AG. You only need to restore the database using NORECOVERY.

On a side note, when you add your SQL Server 2017 instance to the AG and failover to it you won't be able to fail back. This leaves your AG with a single point of failure (the new primary replica). Consider upgrading all your secondary replicas before failing over to your new instance. After the failover, upgrade your (previous) primary replica. More information about rolling upgrades can be found here

Related Question