Removing mirroring from a service broker route

• FishNChipPapers

  SSC Eights!

  Points: 935

  More actions

  July 28, 2018 at 10:19 am

  #407254

  We have a service broker operating between three mirrored databases across 4 instances:

  Instance 1 and Instance 3 - DB1
  Instance 2 and Instance 4 - DB2  and DB3 

  Service broker moves data from DB1 to DB2 and from DB2 to DB3

  We have service broker routes configured with the MIRROR_ADDRESS specified for all routes. We are now planning to remove mirroring, which raises a number of questions:

  1) Is it possible to simply leave the MIRROR_ADDRESS specified or does Service Broker proactively check for the existence of the mirror address?
  2) If the answer to 1) is no then, according to https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-route-transact-sql?view=sql-server-2017 leaving the MIRROR_ADDRESS out will not remove the MIRROR_ADDRESS setting because

  Clauses that are not specified in the ALTER ROUTE command remain unchanged.

  Does that mean that the route has to be dropped and recreated
  3) If 2) is indeed the case then can the route be dropped and recreated without losing messages? According to https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-route-transact-sql?view=sql-server-2017 

  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.

  . So, if the route is dropped and then recreated for the service without the MIRROR_ADDRESS should it all continue to operate.

  Thanks in advance