1

After we tried configure a database with service broker into an always on environment and failed (the service broker is not working properly) we are now considering to try mirroring for high availability.

The client demands it'll use automatic failover which i know needs a witness server. i have configured mirroring many times before but never with service broker. Has anyone tried and it worked? I tried to find an official documentation for it but didn't find anything. What are the steps to configure it on the mirrored database? will it cause performance issues to use service broker with mirroring which is also synchronized?

  • It's unclear what you're having a problem with. There should be no problem with using Service Broker with Always On or Mirroring. Both technologies are supported for use with Service Broker. You should include details about what failed if you want answers on how to fix it. – Max Vernon Aug 22 '18 at 13:57
  • i have a different question opened for always on my question here is what are the steps that i need to perform on the mirrored database to allow service broker in it as well. do i need to configured it somehow differently etc. – sql_girl Aug 22 '18 at 13:58
  • cause when we configured the always on the service broker didn't work at all even though we did use microsofts document explaining how to configure it. its a medical application for hospital appoitments and old appointments did not close properly until we took the db out of the always on. so to prevent this from happening i'm asking what is the way to configure a mirrored database with service broker . thanks – sql_girl Aug 22 '18 at 14:02
0

There is nothing special about configuring Service Broker to work with mirroring. Configure Service Broker, then enable mirroring.

Having said that, you'll need to test failover to ensure any clients accessing the Service Broker infrastructure, such as queues, are able to failover gracefully from primary to secondary.

I'd recommend building a test-network using Virtual Machines including a primary, a secondary, and a witness VM. Restore a copy of the target database into the primary, and configure mirroring to see how it works.

  • Hi what details do you need? – sql_girl Aug 22 '18 at 14:04
  • You'd need to include what you've tried, and what error messages (if any) you're seeing. I'd suggest creating a test setup you can use to validate the process, then compare that against your actual setup. See how to create a minimal, verifiable, and complete, example - admittedly setting up Service Broker and mirroring or Always On is a bit more involved, but still a test setup can be built. – Max Vernon Aug 22 '18 at 14:11
  • i did not try configuring the mirroring therefore there are no errors. the question was about advice and how to configure best and not about troubleshooting an existing problem. you're right but a test environment means 3 servers and I'm not sure they can provide that..thanks anyway – sql_girl Aug 22 '18 at 14:17
  • I'd suggest using 3 virtual machines so you can reduce the requirements enough to make the test setup viable. Not having a test/dev setup means you're testing changes in a production environment, which is a strategy destined for disaster. – Max Vernon Aug 22 '18 at 14:22
  • that's exactly what i asked for and is waiting for approval. – sql_girl Aug 22 '18 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.