Sql Server Broker - remote server messages not processing

Question

I have formulated a solution between a local server (lets call this server A) and a WAN server (Server B). It took me some time to complete my solution but I did eventually formulate a one way comms link between the servers and ultimately two separate databases.

on server A there is currently a single source DB on server A which will frequently transmit messages to Server B. There are multiple databases on Server B.

initially I formulated a single process that upon execution of an SP on Server A would update a database on Server B. I figured I had mastered the required elements to continue with my solution.

I have written a second SP that sits on my source DB on server A that now updates a second DB on Server B. in order to keep all aspects separate I set up secondary services, queues etc within server broker.

I execute my SP all appears well, I can see the conversation on both servers on both databases, but the target server (server B) does not process the message.

I am at a complete loss and eager to find a way through this, I am struggling to debug this issue in order to full appreciate the problem.

I am happy to share any code I have formulated.

---

PW

Answer 1

What does the following code return when running on both server:

select *
from sys.transmission_queue
order by enqueue_time, message_sequence_number;
select db_name(database_id) as DbName, * 
from sys.dm_broker_queue_monitors

I also find Remus' blog very helpful.

Answer 2

Server A output to query 1

Server B does not return any complimenting values at this stage.

Query 2 results at the bottom, does this mean anything to you?

72EEA770-9367-E111-9193-14FEB5CAF648 udef_prodcat_tgt_svc  Udef_ProdCat DEFAULT 2012-03-06 13:51:17.027 0 DEFAULT 0 0 0xFFFE3C0050006100720061006D0073003E003C00500072006F00640075006300740043006F00640065003E005000570054004500530054003C002F00500072006F00640075006300740043006F00640065003E003C00430061007400490044003E0031003C002F00430061007400490044003E003C002F0050006100720061006D0073003E00  5
70EEA770-9367-E111-9193-14FEB5CAF648 udef_prodcat_tgt_svc  Udef_ProdCat DEFAULT 2012-03-06 13:51:15.570 0 DEFAULT 0 0 0xFFFE3C0050006100720061006D0073003E003C00500072006F00640075006300740043006F00640065003E005000570054004500530054003C002F00500072006F00640075006300740043006F00640065003E003C00430061007400490044003E0031003C002F00430061007400490044003E003C002F0050006100720061006D0073003E00  5
3260BA42-9267-E111-9193-14FEB5CAF648 udef_prodcat_tgt_svc  Udef_ProdCat DEFAULT 2012-03-06 13:42:49.020 0 DEFAULT 0 0 0xFFFE3C0050006100720061006D0073003E003C00500072006F00640075006300740043006F00640065003E005000570054004500530054003C002F00500072006F00640075006300740043006F00640065003E003C00430061007400490044003E0031003C002F00430061007400490044003E003C002F0050006100720061006D0073003E00  5
9F7026D1-9167-E111-9193-14FEB5CAF648 udef_prodcat_tgt_svc  Udef_ProdCat DEFAULT 2012-03-06 13:39:42.473 0 DEFAULT 0 0 0xFFFE3C0050006100720061006D0073003E003C00500072006F00640075006300740043006F00640065003E005000570054004500530054003C002F00500072006F00640075006300740043006F00640065003E003C00430061007400490044003E0031003C002F00430061007400490044003E003C002F0050006100720061006D0073003E00  5
9D7026D1-9167-E111-9193-14FEB5CAF648 udef_prodcat_tgt_svc  Udef_ProdCat DEFAULT 2012-03-06 13:39:38.470 0 DEFAULT 0 0 0xFFFE3C0050006100720061006D0073003E003C00500072006F00640075006300740043006F00640065003E005000570054004500530054003C002F00500072006F00640075006300740043006F00640065003E003C00430061007400490044003E0031003C002F00430061007400490044003E003C002F0050006100720061006D0073003E00  5
C4B9CA00-9167-E111-9193-14FEB5CAF648 udef_prodcat_tgt_svc  Udef_ProdCat DEFAULT 2012-03-06 13:33:48.903 0 DEFAULT 0 0 0xFFFE3C0050006100720061006D0073003E003C00500072006F00640075006300740043006F00640065003E005000570054004500530054003C002F00500072006F00640075006300740043006F00640065003E003C00430061007400490044003E0031003C002F00430061007400490044003E003C002F0050006100720061006D0073003E00  5
CA724F93-9067-E111-9193-14FEB5CAF648 udef_prodcat_tgt_svc  Udef_ProdCat DEFAULT 2012-03-06 13:30:45.230 0 DEFAULT 0 0 0xFFFE3C0050006100720061006D0073003E003C00500072006F00640075006300740043006F00640065003E005000570054004500530054003C002F00500072006F00640075006300740043006F00640065003E003C00430061007400490044003E0031003C002F00430061007400490044003E003C002F0050006100720061006D0073003E00  5

Server A Result to Query 2

BulkHardware 7 1362936773 INACTIVE 2012-02-29 15:25:51.343 2012-02-29 15:25:51.343 0
BulkHardware 7 1266936431 INACTIVE 2012-01-27 16:14:38.717 2012-03-06 14:07:39.460 0
msdb 4 1568724641 INACTIVE 2011-12-20 08:43:44.307 2011-12-20 08:43:44.307 0
BulkHardware 7 1204316196 INACTIVE 2011-12-20 08:44:19.637 2012-03-06 14:07:39.460 0
msdb 4 1627152842 INACTIVE 2011-12-20 08:43:44.313 2011-12-20 08:43:44.313 0
msdb 4 1659152956 INACTIVE 2011-12-20 08:43:44.320 2011-12-20 08:43:44.320 0
BulkHardware 7 1506937286 INACTIVE 2012-03-06 13:25:56.953 2012-03-06 13:25:56.953 0

Server B Result to Query 2

bulkexpress 5 1035150733 NOTIFIED 2012-03-06 13:25:58.897 2012-03-06 13:37:31.570 0
msdb 4 1568724641 INACTIVE 2012-02-15 03:15:40.090 2012-02-15 03:15:40.090 0
msdb 4 1627152842 INACTIVE 2012-02-15 03:15:40.170 2012-02-15 03:15:40.170 0
bulkexpress 5 443148624 INACTIVE 2012-03-06 12:25:42.587 2012-03-06 12:25:42.540 0
msdb 4 1659152956 INACTIVE 2012-02-15 03:15:40.233 2012-02-15 03:15:40.233 0

---

PW