the problem of service broker rout use mirror address !!

Question

I have two databases on two computers,and I can send message each other!

when I use mirror databases,I change the broker route add mrrior adress,and the broker is ok!

later, I remove ther database mirror ,and then the message can not be send.

I use  select * from sys.transmission_queue,find that the to_broker_instance is nothing.

when I change the broker route to remove  mirror address ,the the broker is recovered!

so I want to know,this is correct?why I mast remover mirror address in broker route?

Answer 1

I have two databases on two computers,and I can send message each other!

when I use mirror databases,I change the broker route add mrrior adress,and the broker is ok!

later, I remove ther database mirror ,and then the message can not be send.

I use  select * from sys.transmission_queue,find that the to_broker_instance is nothing.

when I change the broker route to remove mirror address ,the the broker is recovered!

so I want to know,this is corect?why I mast remover mirror address in broker route?

Answer 2

Hi lengshan,

What's the version of the SQL Server instance?

---

Stephanie Lv

TechNet Community Support

Answer 3

Hi,

From Using Database Mirroring (for SQL Server 2012, the URL is http://msdn.microsoft.com/en-us/library/ms131373.aspx):

        The production database is called the principal database, and the standby copy is called the mirror database.
In other words, the mirrored database is not usable. But Service Broker does not know whether a given database is in the mirror role or the principal role (at any given point in time).  Service broker's lack of knowledge is why, (from "Service Broker Routing Examples", http://msdn.microsoft.com/en-us/library/ms166090.aspx, "Example 3: Network Route to a Mirrored Database" section):

        Service Broker checks both the address and the mirror address to determine which partner is the principal, and then sends the message to the principal.

For further details, see each paragraph (below Example 3's table) that starts with "For conversations". Consider whether each paragraph is applicable to this system's situation.

If the mirror's address is altered or removed, if you check the sys.transmission_queue, and if you find the to_broker_instance is nothing, you should check (after the mirror is dropped, but before the mirror is added back):

        1. sys.transmission_queue's transmission_status
        2. sys.routes (http://msdn.microsoft.com/en-us/library/ms187393.aspx). and compare what is within a system's sys.routes with Example 3 (see above).
        3. If 1 and 2 do not help, you can run C:\Program Files\Microsoft SQL Server\NNN\Tools\Binn\SSBDiagnose.exe (where NNN=80, 90, 100, 110...).

If you remove the the mirror without updating sys.routes, the transmission_status could be DELAYED, per http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/InternalsTroubleshootingScaleOut.doc:

        6. Initiator: First Send

        a. A part of the Service Broker infrastructure, the message classifier, consults the sys.routes routing table in the initiator's database. If there is no route available to the target, it marks the message DELAYED.
But, I do not know whether DELAYED was this system's transmission_status, or whether a different transmission_status was seen.

You can use DROP ROUTE (http://msdn.microsoft.com/en-us/library/ms189449.aspx) to remove the route to the mirror. But, from the same topic:

        You can drop a route regardless of whether any conversations use the route. However, if there is no other route to the remote service, messages for those conversations will remain in the transmission queue until a route to the remote service is created or the conversation times out.

I believe (I do not have enough details to be certain) the above sentence explains why adding the mirror back had allowed Service Broker to continue routing its conversations to that mirror.

This behavior is also explained within the technical paper http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/InternalsTroubleshootingScaleOut.doc:

        Service Broker [] assumes that missing routes can be (and will be) fixed eventually.

If you wish to consider more details, additional information can be obtained within the "Service Broker metadata for routing", "Service Broker trace events" and "Troubleshooting" sections of the above technical paper. It may be useful to reproduce the steps that had been followed (to drop the mirror).

I also recommend searching that paper for the 6 sentences that contain the word "mirror". Also see that paper's "END CONVERSATION @handle WITH CLEANUP" example, or the "END CONVERSATION @dialog_handle WITH CLEANUP " example that is within "END CONVERSATION (Transact-SQL)" (http://msdn.microsoft.com/en-us/library/ms177521.aspx).

Thanks,

Cathy Miller