2

According to this MSDN article, I need to add a distributor:

Configure Replication for AlwaysOn Availability Groups (SQL Server)

I'm confused by this particular statement:

The distributor should not be a host for any of the current (or intended) replicas of the availability group that the publishing database is (or will become) a member of.

I have a three node cluster. The first two nodes are configured as synchronous replicas. The third node is intended to be read-only, asynchronous.

This is the first time I've come across this requirement and I'm worried my current setup doesn't cover this critical piece.

Can I set up any of these three nodes as the distributor? Which node would be the better choice or does that matter?

The reason for my question is because we have a vendor application that is not able to access the database when we failover to the second node. Below is the error we are receiving.

Description: Error running query in SpDbOdbc State=37000
Msg=[Microsoft][ODBC SQL Server Driver][SQL Server]The target database, 'Mirage', is participating in an availability group and is currently not accessible for queries.
Either data movement is suspended or the availability replica is not enabled for read access.
To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.
For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

Server: \
Database: Mirage
Date: 2014-11-11 15:06:45
Module: .exe
Function: hGetErrCode
Error code: DATABASE_ACCESS

Error code general description: Could not access the item in the database.

Also getting this message when trying to login through the application.

Msg=[Microsoft][ODBC SQL Server Driver][SQL Server]Failed to update database "Mirage" because the database is read-only.

  • That KB article is talking about using SQL Server replication with an AlwaysOn Availability Group. This is not required. Replication and AlwaysOn Availability Groups are two different things. – mrdenny Nov 11 '14 at 23:43
  • 1
    As mrdenny states, the distributor is for replication, having nothing to do with AG. The error you are getting and the lack of access, kinda looks like you are trying to hit a replica that isn't set up for readonly access or you are hitting a replica directly instead of through a listener, or you are hitting a replica without setting your applicationIntent to readonly...or all of the above! – Steve Mangiameli Jan 16 '15 at 19:40
1

The statement you are reading in the Microsoft article says that you should not (and cannot) put a Distribution database inside an Availability Group.

Why?

Because the distribution database is a System database and Availability Groups will only fail over User databases. Therefore you will require another (4th) server in your situation to act as the (remote/scaled out) Distributor.

  • Beginning with SQL 2017, the distributor in a replication setup can now be included in an Availability Group, if so desired. – Mike Apr 16 at 5:52

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.