How can I turn on and turn off Service Broker for Amazon Relational Database Service (Amazon RDS) SQL Server?
Service Broker is supported for Amazon RDS, but Service Broker endpoints aren't supported on Amazon RDS. For more information about Service Broker, see the Microsoft documentation for Service Broker.
When you turn on Service Broker, Amazon RDS requests a database lock. Before turning on Service Broker, close all open connections to the database.
1. Check to see whether Service Broker is turned on:
select name,is_broker_enabled from sys.databases
where name= [YourDB]
2. If Service Broker isn't turned on, then generate a new Service Broker:
ALTER DATABASE [YourDB] SET NEW_BROKER;
3. Turn on Service Broker on an existing database:
ALTER DATABASE [YourDB] SET ENABLE_BROKER;
If you run a Multi-AZ deployment, then run this command:
ALTER DATABASE [YourDB] SET PARTNER OFF;
Then, turn on Service Broker again in a Multi-AZ deployment:
ALTER DATABASE [YourDB] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
To turn off Service Broker on an existing database, run this command:
ALTER DATABASE [YourDB] SET DISABLE_BROKER;
You can't turn on Service Broker on a database that is already part of an availability group. If you run the above resolution on such a database, then you receive an error similar to the following:
ALTER DATABASE [test] SET PARTNER OFF;<br>GO <br>Msg 1416, Level 16, State 1, Line 1<br>Database "test" is not configured for database mirroring.
Instead, turn on Service Broker before adding it to the availability group. To do this, convert the RDS database to single AZ, and then turn on Service Broker.
Convert your RDS database to Single AZ.
Turn on Service Broker:
<p>ALTER DATABASE [DBName ] SET NEW_BROKER;<br>GO
</p><p>ALTER DATABASE [DBName ] SET ENABLE_BROKER;<br>GO
- Verify that Service Broker is turned on:
select name,is_broker_enabled from sys.databases<br>
where name= [DBName ]
- Convert your RDS database back to Multi-AZ.
Microsoft SQL Server on Amazon RDS
Migrating Microsoft SQL Server databases to the AWS Cloud