[Top] [All Lists]

Re: Service Broker and SQL Server Express

Subject: Re: Service Broker and SQL Server Express
From: "Remus Rusanu [MSFT]"
Date: Thu, 3 Aug 2006 08:46:25 -0700
Newsgroups: microsoft.public.sqlserver.notificationsvcs, microsoft.public.sqlserver.servicebroker
Hello Rudolf,

So you had a setup that worked correctly between Server1 and Server2, but 
when you attempted to set up a conversation between Server1 and SQL Express 
but it doesn't work.
You noticed that Server1 still attempts to connect to Server2. This would 
indicate that Server1 has something to be delivered to Server2:
1. there could be still pending messages on Server1 from the original setup 
(targeting Server2), they'd be visible in Server1.db1.sys.transmission_queue
2. there could be still pending messages on Server2 from the original setup 
and Server2 sends them to Server1, causing Server1 to send back 
acknowledgements. These messages would be visible in 
3. (most likely) The route for 'Service_3' in Server1.db1 has the address of 
Server2 instead of SQLExpress. This causes the acknowledgements for messages 
sent to by SQLExpress to be routed back to Server2 instead of SQLExpress.
4. (unlikely) The target service name used by SQLExpress exists in more than 
one database on Server1. I.e. There is another copy of it in dbX, and in 
this database the route back the 'Service_3' points to Server2 (same as 
above, but not in db1). In this case dialogs targeting this service name 
that do not explicitly specify a broker instance are free to choose at 
random on of the two copies of the service and when they choose the one in 
dbX, you get this behavior.

Enabling forwarding on Server2 does not solve the issue on it's own, because 
Server2 still does not know where 'Service_3' is hosted, so it will drop the 
messages. To get forwarding to work, you also need to set up a route for 
'Service_3' in Server2.msdb. msdb is used as the routing database for 
forwarded messages. The forwarder does not need to understand the message 

BTW, Service Broker has its own newsgroup at 

This posting is provided "AS IS" with no warranties, and confers no rights.

~ Remus Rusanu

SQL Service Broker,SQL.90).aspx

<[email protected]> wrote in message 
news:[email protected]
> Hi there.
> I did search the web for the following question but could not find a
> clear answer maybe some one can point me in the right direction.
> I downloaded a sample for service broker and set it up between two SQL
> Servers and it works just fine. I then recreated the sample between SQL
> server and Sql Express.
> When I create a new conversation and send a message the message end up
> in the transmission que without any error. I then used sql Profiler on
> the target Sql server to trace why the message do not end up at the
> "other side". Sql profiler did not report anything I then set sql
> profiler up also to monitor  sql server on the second server. What I
> notice is that the server broker on the first server try to comm with
> sql server on the second server and not with sql express directly. I
> enabled forwarding on the endpoint (sql server 2) but this also do not
> help.
> My question is how can I tell the servers broker on server 1 to
> establish a conversation with sql express on the second server on not
> with sql server on the second server?? The servers names,messages,
> contract, queues, route and remoute service binding names are not the
> same on server 2. The setup is as follow:
> Server 1:
> Windows 2003 service pack 1
> SQl Server 2005 service pack 1
> for db1:
> service broker setup:
> - Service_1to2 ,q1 ,contract1,sendmsg1,recmsg1,route1 (for sql server
> on second server) and remoute binding1.
> -Service_2_toExp,contractExp,semdmsgExp,recmsgexp,routeExpr and remote
> binding for Expr.
> Server 2:
> Windows 2003 service pack1
> SQL Server 2005 service pack 1
> service broker setup for db2:
> - Service_2,,q1 ,contract2,sendmsg2,recmsg2,route2 (for sql server on
> first server) and remote binding2.
> SQL Express 2005
> service broker setup for db2:
> - Service_3,,q3 ,contract3,sendmsg3,recmsg3,route3 (for sql server on
> first server) and remote binding2.
> thank you in advance.
> Rudolf.

<Prev in Thread] Current Thread [Next in Thread>