Adding a database with Service Broker to an existing Availability Group on a new replica

Question

• 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.

Answer 1

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

Answer 2

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.

For adding the database having SERVICE_BROKER enable into Availability Group, we don't need to enable SERVICE_BROKER explicitly on secondary node or simply we can't because we need to restore it with NORECOVERY & we can't make any change on the database in RESTORING state.

When we add database to availability group, all settings of primary database applies to all secondary replicas. So no additional effort required here.

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.

It's not possible to join SQL Server 2017 into 2016 AG or vice-versa. So once you make Server4 primary, you wouldn't be able to switch back to Server 1, 2 or 3 having SQL Server 2016. So you should consider using Distributed Availability Group feature for upgrading the AG to SQL Server 2017. That'll help you to reduce down time to few seconds.

Thanks!