1

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.

0
2

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.,

6
  • Hi Remus, thanks for the reply. I had some issues with my environment and took me someti-me to get it back. I followed your suggestions and changed the route to redirect to the AG. I still have the same issue - database is in read-only mode. I might be missing something here and I don't know what. I've read the article three times and everything looks ok.
    – Alexandre
    Apr 27 '15 at 14:35
  • If you see a message stating that database is in read only mode in the transmission_status column it can only come from local delivery attempts. Remote delivery (using the AG endpoint) can never show a message about the target DB because such delivery errors are not marshaled across the network. Therefore it must be that your dialog(s) are still using a local route. Apr 27 '15 at 15:40
  • I did a very unusual test to check that my dialog was using a local route or not. I removed the replica from the AG, so database x was no longer available in server B. This instantly resolved the problem, without having to touch any routes, services, dialogs or anything. Are you sure the problem is in one of my routes? Is this setup really possible to be done?
    – Alexandre
    May 4 '15 at 13:32
  • It absolutely should work. If you can repro again, please attach profiler and monitor the Broker:Message Classify Event. The FileName will show the service name and the EventSubClass will show the route type. May 4 '15 at 14:35
  • Created the AG again and reproduced the issue. On the profiler the EventSubClass shows as 1 - Local. I understand what is going on, but I don't get why just creating the replica stops sending the message to the AG and sends it locally.
    – Alexandre
    May 5 '15 at 18:10

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.