I want to turn Service Broker on and turn off for Amazon Relational Database Service (Amazon RDS) SQL Server.
Short description
Service Broker is supported for Amazon RDS, but Service Broker endpoints aren't supported on Amazon RDS. For more information about Service Broker, see Service Broker on the Microsoft website.
Resolution
Turn on Service Broker
When you turn on Service Broker, Amazon RDS requests a database lock.
Note: Before you turn on Service Broker, be sure to close all open connections to the database.
Complete the following steps:
-
Check to see whether Service Broker is turned on:
select name,is_broker_enabled from sys.databases
where name= [YourDB]
-
If Service Broker isn't turned on, then generate a new Service Broker:
ALTER DATABASE [YourDB] SET NEW_BROKER;
GO
-
Turn on Service Broker on an existing database:
ALTER DATABASE [YourDB] SET ENABLE_BROKER;
GO
For Multi-AZ deployments
Complete the following steps:
-
Run the following command:
ALTER DATABASE [YourDB] SET PARTNER OFF;
GO
-
Turn on Service Broker again:
ALTER DATABASE [YourDB] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
Turn off Service Broker
To turn off Service Broker on an existing database, run the following command:
ALTER DATABASE [YourDB] SET DISABLE_BROKER;
GO
Turn on Service Broker for a database that's already part of an availability group
You can't turn on Service Broker for a database that's already part of an availability group. You receive an error similar to the following one:
ALTER DATABASE [test] SET PARTNER OFF;
GO
Msg 1416, Level 16, State 1, Line 1
Database "test" is not configured for database mirroring.
Instead, convert the RDS database to a Single AZ, and then turn on Service Broker:
-
Convert your RDS database to a Single AZ.
-
Turn on Service Broker:
ALTER DATABASE [DBName ] SET NEW_BROKER;
GO
ALTER DATABASE [DBName ] SET ENABLE_BROKER;
GO
-
Verify that Service Broker is turned on:
select name,is_broker_enabled from sys.databases
where name= [DBName ]
-
Convert your RDS database back to Multi-AZ.
Related information
Amazon RDS for Microsoft SQL Server
Migrating Microsoft SQL Server databases to the AWS Cloud