How can I turn Service Broker on and off for 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:

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;
   GO

3. Turn on Service Broker on an existing database:

   ALTER DATABASE [YourDB] SET ENABLE_BROKER;
   GO

For Multi-AZ deployments

Complete the following steps:

1. Run the following command:

   ALTER DATABASE [YourDB] SET PARTNER OFF;
   GO

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

1. Convert your RDS database to a Single AZ.

2. Turn on Service Broker:

   ALTER DATABASE [DBName ] SET NEW_BROKER;
   GO
   ALTER DATABASE [DBName ] SET ENABLE_BROKER;
   GO

3. Verify that Service Broker is turned on:

   select name,is_broker_enabled from sys.databases
     where name= [DBName ]

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