SQL Server – Service Broker and AlwaysOn Issue

availability-groupsservice-brokersql server

I'm having an issue setting up my current environment using AlwaysOn and SSB and I'll try to explain as best as I can here:

Current environment:

Server A             Server B
database x(Pub)      database x (Sub)
                     database y

Our app runs on database x. We have transactional replication setup to make database x available on Server B.
Database y is used to receive data from another app. We use it to process this data and just send the necessary information to database x. The replica of database x on Server B helps us to validate all the information and make sure we won't send any invalid data.

To communicate between database y and database x we use SSB. Why? It's faster.

We are moving to AlwaysOn and we'd like to maintain the current setup and add the failover cluster technology along with it.

So we come to the problem: AlwaysOn does not let us choose what we want to have on the replica. So it has everything, including the SSB objects (Services, Queues, etc.)
When we set up the databases and try to communicate database y and x, we get this error on transmission_queue:

Database x is read-only mode

So SSB is using the local services we have on the replica of database x in Server B instead of looking at our routes that tell him to go to database x in Server A.

One alternative we found is to create a new instance for database y. I don't like the approach and from what I've read we also lose the automated failover from AlwaysOn with two instances and we'd have to do it manually.

My personal guess is that SSB Always uses the local services and I have no way out of this. One thing I have considered is removing the AutoCreatedLocal route from Server B and see if that Works (still have to try) but if it does, I'm not sure how I'll make the setup when it does the failover. I'll probably have to have a script to run and create it back.

Appreciate any help on this.

Editing to add more information:
Followed the link provided by Remus ( Service Broker with AlwaysOn ) to configure SSB with AlwaysOn. Basically just changed the route to use the AG instead of the specific server.

Still having the same error: database in read-only mode.

Best Answer

You need to setup a AG aware SSB route between x and y. See Service Broker with AlwaysOn Availability Groups. This route will deliver the messages over the network even when x and y are collocated on the same host. When failover occurs and x and y are on different hosts, the delivery continues uninterrupted.

My personal guess is that SSB Always uses the local services and I have no way out of this.

You should read Service Broker Routing (although is a bit dated, as it misses the AG aware endpoints). SSB uses the local services (the "auto" routes) as a last resort, if no explicit route is declared. You always have a way "out" by explicitly creating a route.,

Related Question