Setting up Service Broker where the initiator database is part of the AG
There have been several posts on setting up Service Broker where the database is part of the Availability Group. This is a one stop blog for setting up Service Broker where the initiator database is part of the AG
What happens when the AG fails over? Should there be any additional steps in setting up the service broker so that it resumes its operation after an AG failover? This blog post answers all these questions with details wherever required. The below technet article outlines the steps: https://msdn.microsoft.com/en-IN/library/hh710058.aspx
In this blog, we will discuss about the steps along with the screenshots, wherever applicable.
Requirements:
- Service Broker has to be setup and enabled on the database before the database is added to the AG.
- Availability Group must possess a listener.
- Service Broker endpoints must be configured on every instance of SQL Server that hosts an availability replica for the availability group.
Environment:
AlwaysOn
Name of Availability Group: AGService
AG Listener: LIST
VM1\INS1 : Primary Synchronous Replica
VM2\INS2 : Secondary Synchronous Replica (Automatic Failover)
Service Broker
VM1\INS1 : Initiator 1
VM2\INS2 : Initiator 2
VM3\INS3 : Target
At the end of this blog, we’ll be setting up the below environment
Overview:
‹•› Setup service broker on the initiator: VM1\INS1 and target: VM3\INS3
‹•› Take a backup of the Initiator database on VM1\INS1 and restore it with NORECOVERY on the 2nd Initiator (secondary replica) –VM2\INS2
‹•› Create Service Broker endpoint on the secondary replica: VM2\INS2
‹•› Create Route on the secondary replica: VM2\INS2
‹•› Backup the Service Master Key from the primary replica and restore it on the secondary replica.
‹•› Add the 2 initiator databases (from VM1\INS1 and VM2\INS2) to the AG
Steps:
- Setup service broker on the initiator: VM1\INS1 and target: VM3\INS3
Here, we’ll be setting up Service Broker as we normally do, with initiator as VM1\INS1 and VM2\INS2 as target.
The T-SQL here to do the same is included, just so there is more clarity on the naming convention that is used in this blog. These scripts have been taken from the below MSDN article:
https://technet.microsoft.com/en-us/library/bb839483(v=sql.105).aspx
------------------------------------------
--Lesson 1: Creating the Target Database--
--***[connect to the TARGET instance- VM3\INS3]***--
------------------------------------------
--Create a Service Broker endpoint
USE master;
GO
IF EXISTS (SELECT * FROM master.sys.endpoints WHERE name = N'InstTargetEndpoint')
DROP ENDPOINT InstTargetEndpoint;
GO
CREATE ENDPOINT InstTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022, LISTENER_IP=ALL )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
--Create the target database, master key, and user
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases WHERE name = N'TargetDB')
DROP DATABASE TargetDB;
GO
CREATE DATABASE TargetDB;
GO
USE TargetDB;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'<EnterStrongPassword1Here>';
GO
CREATE USER TargetUser WITHOUT LOGIN;
GO
--Create the target certificate & backup the certificate
CREATE CERTIFICATE InstTargetCertificate
AUTHORIZATION TargetUser
WITH SUBJECT = 'Target Certificate',
EXPIRY_DATE = N'12/31/3010';
BACKUP CERTIFICATE InstTargetCertificate
TO FILE = N'\\DC\Certshare\InstTargetCertificate_INS3.cer';
GO
--Create message type for request and reply
USE TargetDB;
GO
Create message type [//RequestMsg]
go
Create message type [//ReplyMsg]
go
--Create a contract for the above created message
USE TargetDB;
GO
Create contract [//SampleContract]
(
[//RequestMsg] sent by initiator,
[//ReplyMsg] sent by target
)
--Create Queue & Service for target
USE TargetDB;
GO
CREATE QUEUE TargetQueue_TargetDB;
go
CREATE SERVICE [//TargetService]
AUTHORIZATION TargetUser
ON QUEUE TargetQueue_TargetDB([//SampleContract]);
GO
Below queries need to be run on the initiator. At this point, we’ll run it only on VM1\INS1 which will be the primary replica on the AG.
---------------------------------------------
--Lesson 2: Creating the Initiator Database--
--***[connect to the INITIATOR instance: VM1\INS1]***--
--------------------------------------------
--Create a Service Broker endpoint
USE master;
GO
IF EXISTS (SELECT * FROM sys.endpoints
WHERE name = N'InstInitiatorEndpoint')
DROP ENDPOINT InstInitiatorEndpoint;
GO
CREATE ENDPOINT InstInitiatorEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022, LISTENER_IP=ALL )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
--Create the initiator database, master key, and user
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases
WHERE name = N'InitiatorDB')
DROP DATABASE InitiatorDB;
GO
CREATE DATABASE InitiatorDB;
GO
USE InitiatorDB;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'<EnterStrongPassword2Here>';
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
GO
--Create the initiator certificate
CREATE CERTIFICATE InstInitiatorCertificate
AUTHORIZATION InitiatorUser
WITH SUBJECT = N'Initiator Certificate',
EXPIRY_DATE = N'12/31/3010';
BACKUP CERTIFICATE InstInitiatorCertificate
TO FILE =
N'\\dc\certshare\InstInitiatorCertificate_INS1.cer';
GO
--Create message type for request and reply
USE InitiatorDB;
GO
Create message type [//RequestMsg]
go
Create message type [//ReplyMsg]
go
--Create a contract for the above created message
USE InitiatorDB;
GO
Create contract [//SampleContract]
(
[//RequestMsg] sent by initiator,
[//ReplyMsg] sent by target
)
--Create the initiator queue and service
CREATE QUEUE InitiatorQueue_InitiatorDB;
CREATE SERVICE [//InitiatorService]
AUTHORIZATION InitiatorUser
ON QUEUE InitiatorQueue_InitiatorDB;
GO
--Create references to target objects
CREATE USER TargetUser WITHOUT LOGIN;
CREATE CERTIFICATE InstTargetCertificate
AUTHORIZATION TargetUser
FROM FILE =
N'\\DC\certshare\InstTargetCertificate_INS3.cer'
GO
--Create routes
DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE InitiatorDB;
CREATE ROUTE Initiator_TO_Target_Route
WITH SERVICE_NAME =N''//TargetService'',
ADDRESS = N''TCP://VM3:4022'';';
EXEC (@Cmd);
SET @Cmd = N'USE msdb
CREATE ROUTE Inst_Local_InitiatorRoute
WITH SERVICE_NAME =
N''//InitiatorService'',
ADDRESS = N''LOCAL''';
EXEC (@Cmd);
GO
CREATE REMOTE SERVICE BINDING TargetBinding
TO SERVICE
N'//TargetService'
WITH USER = TargetUser;
GO
Now, on the target: VM3\INS3, run the below. Note that I am using the routing address of VM1 while creating a Target_To_Initiator Route. We’ll change that to name of the AG listener after we test if the service broker is sending and receiving messages between the VM1\INS1 and VM3\INS3( Without AG in picture)
-------------------------------------------------------
--Lesson 3: Completing the Target Conversation Objects
--***[connect to the TARGET instance VM3\INS3]***--
-------------------------------------------------------
--Create references to initiator objects
USE TargetDB
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
CREATE CERTIFICATE InstInitiatorCertificate
AUTHORIZATION InitiatorUser
FROM FILE = N'\\DC\certshare\InstInitiatorCertificate_INS1.cer';
GO
--Create routes
DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE TargetDB;
CREATE ROUTE Target_To_Initiator_Route
WITH SERVICE_NAME =
N''//InitiatorService'',
ADDRESS = N''TCP://VM1:4022'';';
EXEC (@Cmd);
SET @Cmd = N'USE msdb
CREATE ROUTE Inst_Local_TargetRoute
WITH SERVICE_NAME =
N''//TargetService'',
ADDRESS = N''LOCAL''';
EXEC (@Cmd);
GO
GRANT SEND
ON SERVICE::[//TargetService]
TO InitiatorUser;
GO
CREATE REMOTE SERVICE BINDING InitiatorBinding
TO SERVICE N'//InitiatorService'
WITH USER = InitiatorUser;
GO
This is the basic Service Broker setup between VM1\INS1 and VM3\INS3. You can check by sending and receiving messages using the below scripts.
--------------------------------------------------
--Lesson 4: Beginning the Conversation--
--***[connect to the INITIATOR instance VM1\INS1]***--
--------------------------------------------------
USE InitiatorDB;
GO
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
FROM SERVICE [//InitiatorService]
TO SERVICE N'//TargetService'
ON CONTRACT [//SampleContract]WITH ENCRYPTION = Off;
SELECT @RequestMsg = N'<RequestMsg:1>Message for Target service.</RequestMsg:1>';
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE [//RequestMsg]
(@RequestMsg);
SELECT @RequestMsg AS SentRequestMsg;
COMMIT TRANSACTION;
GO
Once the message is sent from the initiator, it can be seen by querying the Target Queue on the target database.
Select *from [dbo].[TargetQueue_TargetDB]
If it is not present on the Target queue, query the sys.transmission_queue on the initiator. If there are any errors, it will be shown on the transmission_status column.
Now, run the below on the target instance to receive the request and send a reply.
--------------------------------------------------
--Lesson 5: Receiving a Request and Sending a Reply--
--***[connect to the TARGET instance VM3\INS3]***--
--------------------------------------------------
--Receive the request and send a reply
USE TargetDB;
GO
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;
BEGIN TRANSACTION;
WAITFOR
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM [dbo].[TargetQueue_TargetDB]
), TIMEOUT 1000;
SELECT @RecvReqMsg AS ReceivedRequestMsg;
IF @RecvReqMsgName = N'//RequestMsg'
BEGIN
DECLARE @ReplyMsg NVARCHAR(100);
SELECT @ReplyMsg =
N'<ReplyMsg:1>Message for Initiator service.</ReplyMsg:1>';
SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE [//ReplyMsg]
(@ReplyMsg);
END CONVERSATION @RecvReqDlgHandle;
END
SELECT @ReplyMsg AS SentReplyMsg;
COMMIT TRANSACTION;
GO
Make sure this message appears in the initiator queue on VM1\INS1
End the conversation by running the below on the initiator: VM1\INS1:
--------------------------------------------------
--Lesson 6: Receiving the Reply and Ending the Conversation--
--***[connect to the INITIATOR instance VM1\INS1]***--
--------------------------------------------------
--Receive the reply and end the conversation
USE InitiatorDB;
GO
DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
WAITFOR
( RECEIVE TOP(1)
@RecvReplyDlgHandle = conversation_handle,
@RecvReplyMsg = message_body
FROM [dbo].[InitiatorQueue_InitiatorDB]
), TIMEOUT 1000;
END CONVERSATION @RecvReplyDlgHandle;
-- Display recieved request.
SELECT @RecvReplyMsg AS ReceivedReplyMsg;
COMMIT TRANSACTION;
GO
2. Take a Full database backup and transaction log backup of the Initiator database on VM1\INS1 and restore them with NORECOVERY on the 2nd Initiator (secondary replica) –VM2\INS2.
3. Create an availability group named AGService and add the database: InitiatorDB on VM1\INS1 and VM2\INS2 to it with VM1\INS1 as the primary replica and VM2\INS2 as the Synchronous secondary replica. Also, create an AG listener named: LIST.
4. Now, failover the AG to VM2\INS2 Replica. After the failover, check if Service Broker is enabled on the database we restored on VM2\INS2. The is_broker_enabled bit should be set to 1.
select is_broker_enabled, *from sys.databases where name=’InitiatorDB’
5. Create the Service Broker endpoint on the replica: VM2\INS2
USE MASTER
GO
CREATE ENDPOINT InstInitiatorEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022, LISTENER_IP=ALL )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
6. Create the local Route on the MSDB of the secondary replica: VM2\INS2
USE msdb
GO
CREATE ROUTE Inst_Local_InitiatorRoute
WITH SERVICE_NAME =
N'//InitiatorService',
ADDRESS = N'LOCAL'
7. Backup the Service Master Key from the primary replica VM1\INS1 and restore it on the secondary replica VM2\INS2
BACKUP SERVICE MASTER KEY TO FILE='\\DC\CertShare\SMKVM1' encryption by password='IamStrong!'
RESTORE SERVICE MASTER KEY FROM FILE='\\DC\CertShare\SMKVM1' decryption by password=' IamStrong!' FORCE
--Run this on VM3\INS3
USE [TARGETDB]
GO
ALTER ROUTE [Target_To_Initiator_Route]
WITH SERVICE_NAME =
N’//InitiatorService’,
ADDRESS = N’TCP://LIST:4022’
8. Now, we’ll change the Target_To_Initiator_Route to use the AG Listener. This will make sure Service Broker works even after an AG failover.
--Run this on VM3\INS3
USE [TARGETDB]
GO
ALTER ROUTE [Target_To_Initiator_Route]
WITH SERVICE_NAME =
N’//InitiatorService’,
ADDRESS = N’TCP://LIST:4022’
You can test the service broker functionality now between the new initiator: VM2\INS2 and target: VM3\INS3 by running the queries in step 1 (only from Lesson 4 to Lesson 6).
Author:
Prabhjot Kaur, Support Engineer, Microsoft India GTSC
Reviewed by:
Balmukund Lakhani, Support Escalation Engineer, Microsoft India GTSC
Comments
- Anonymous
July 02, 2016
Hi Pradeep,Nice Article!!..I have recently migrated the databases from SQL 2008 R2 cluster environment to SQL 2012 Availability group. Few databases have service broker enabled for messaging purpose.After successful migration, when I configure the databases to AG the service broker is not working properly , when I remove the databases from AG the service broker queue seems to be working. AG Name : PRD01AG Port: 5022Primary Instance Name: PRD1\PRD11Secondary Instance Name: PRD2\PRD12In this scenario, can you please let me know what step would I need to follow to make service broker to work properly in AG ?