We are working on implementing Service Broker in our SQL 2008/2008 R2 environment. Our initial implementation will have the initiator db on a 2008 R2 instance and the target on a 2008 instance. We have development and QA environments that have all the matching servers from production. We currently anticipate that we would want all the Service Broker conversations that occur in production to occur in development and QA, and that there would be no cross talk between production, QA or development. Before I read about the “RESTORE... NEW_BROKER” stuff I figured that by properly maintaining the routes in the databases, I would avoid the crosstalk. I already have sophisticated scripts for restoring production backups to the dev/QA servers, so adding the commands to set the routes properly, and making sure they are executed, would not be a problem.
So, to a certain extent, if the Dev Server DatabaseA has the same Service Broker GUID as the Prod Server DatabaseA, proper maintenance of routes should eliminate cross talk. In the “RESTORE... NEW_BROKER” stuff I read, I see one major advantage is the automatic clearing out of existing conversations (we definitely don’t want lingering prod conversation handles in dev). But in reviewing the documentation about restoring Service Broker enabled databases from one server to another, and I am seeing some inconsistent/conflicting documentation.
In the “RESTORE Arguments (Transact-SQL)” (1) and “ALTER DATABASE SET Options (Transact-SQL)” (2) Books Online topics, the description of the NEW_BROKER option are almost identical, and they sound fairly benign in that it just does a cleanup and assigns a new GUID (from RESTORE):
“Specifies that the database be assigned a new Service Broker identifier. Because the database is considered to be a new Service Broker, existing conversations in the database are immediately removed without producing end dialog messages. Any route referencing the old Service Broker identifier must be recreated with the new identifier.”
On the other hand, there is this somewhat scarier description for NEW_BROKER in the “Managing Service Broker Identities” (3) topic:
“NEW_BROKER. This option activates Service Broker message delivery and creates a new Service Broker identifier for the database. This option ends all existing conversations in the database, and returns an error for each conversation. This is because these conversations do not use the new identifier. Any route that references the old Service Broker identifier must be re-created with the new identifier.”
Where are the errors returned? Are they T-SQL errors I would get in my Query Window, or are they error messages that might get sent back to my production Service Broker queues?
Manager SQL Server DBA
CureSearch for Children's Cancer
I would like to involve someone famililar with this issue to have a look, and give an update later. Thanks for your understanding.TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.
TechNet Community Support
Let me try to summarize your situation to make sure i understand your requirements, before i propose a solution ;
1. You are restoring one or more production database to development & QA and setting up service broker in these enviornments similar to production
2. You want to get rid of the messages that might be in the Queue due to the restore from production.
3. you want to ensure that Service Broker set ups in each enviornment stay isolated to the respective enviornment and there is no cross talk across service broker setups between the enviornments.
With this framework established, the following steps should help achieve your goals and mitigate any corss talk concerns;
1. While Restoring the Databases, definitely use the NEW_BROKER option in each of your dev & qa enviornments. This will generate a new service_broker_guid that you will use later to define your routes. The NEW_BROKER option will end all existing conversations.
2. If any of the conversations return error, you can identify that my querying the sys.transmission_queue view and specifically look for the values under columns : is_conversation_error ( BIT - 0 = No Error, 1 = Error ) and transmission_status. If there are any rows in this view, get hold of all the values under the column conversation_handle . use this conversation_handle to end all the conversation explicity for a clean up using the command - END CONVERSATION <conversation_handle goes here> WITH CLEANUP;
3. Now you can go about the task for setting up the routes in your dev & qa enviornments. Please take a note that the syntax for creating a route requires you to specify the following details ;
- Service_name (you can get this from the object explorer view or from the view sys.services)
- ADDRESS ( This will be the physical machine name of the target broker end point or the next hop. The server names you would specify here need to be different in each enviornment to prevent any corss talk across enviornments)
- Broker_Instance ( this is where the newly created service_broker_guid from RESTORE ...... NEW_BROKER comes into play). you can get this GUID using the following query;
SELECT service_broker_guid FROM sys.databases WHERE database_id = DB_ID()
These steps should help you achieve your goals and mitigate corss talk concerns.
Please note that creating routes is a critical step here and you may end up creating a lot of them depending on how many services you have in your enviornment. Please refer to this MSDN topic on creating routes before you go ahead with the task :
Hope this helps. Feel free to comment if you have any more questions.
Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer.
- Proposed as answer by Sanil Mhatre Thursday, February 23, 2012 4:17 PM
When you use the option "new_broker" it only
affects the one side of the conversation where this command is issued. When
new_broker option is specified, SQL Server truncates the user queue tables [the
service broker queue] and system tables that host the conversation groups and
dialog endpoints in that database. No end dialog message is sent to the
opposite side of the conversation. So you could be in a state where a
conversation does not exist on the initiator side (new_broker command executed)
and a conversation exists on the target side (or visa versa depending on where
you issued the new_broker command). Now if the target sends a message to the
initiator on that conversation it will fail with an error because the initiator
side of the conversation does not exist anymore [cleaned by new_broker]. The
message on the target would remain in the sys.transmission_queue because no ACK
is received from the initiator. You would see the details in the
transmission_status column of sys.transmission_queue. You would then have to
run a script on the target to iterate through these conversations and end the
conversations with cleanup to remove the target side of the conversation.