How can I turn Service Broker on and off for Amazon RDS SQL Server?

2 minuti di lettura
0

How can I turn on and turn off Service Broker 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 the Microsoft documentation for Service Broker.

Resolution

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

3.    Turn on Service Broker on an existing database:

ALTER DATABASE [YourDB] SET ENABLE_BROKER;
GO

If you run a Multi-AZ deployment, then run this command:

ALTER DATABASE [YourDB] SET PARTNER OFF;
GO

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

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.

  1. Convert your RDS database to Single AZ.

  2. Turn on Service Broker:

<p>ALTER DATABASE [DBName ] SET NEW_BROKER;<br>GO
</p><p>ALTER DATABASE [DBName ] SET ENABLE_BROKER;<br>GO
</p>
  1. Verify that Service Broker is turned on:
select name,is_broker_enabled from sys.databases<br>
 where name= [DBName ]
  1. Convert your RDS database back to Multi-AZ.

Related information

Microsoft SQL Server on Amazon RDS

Migrating Microsoft SQL Server databases to the AWS Cloud