# Sql-server – Adding a database with Service Broker to an existing Availability Group on a new replica

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.

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