Sql-server – Adding a distributor for availability groups

availability-groupsfailoverreplicationsql serversql-server-2012

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.

Best Answer

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.