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