Sql-server – Availability Group configuration & licencing

availability-groupsconfigurationsql-server-2012

I am about to setup a 3 node AV group for SQL Server 2012 my configuration will be –

London-Srv1 – PRIMARY
Core Database (OLTP)
set to READ_WRITE

London-Srv2 – SECONDARY PASSIVE synchronous
Core Database (OLTP)
automatic failover
no connections allowed (for quick writes from PRIMARY)

Cambridge-Srv1 – PASSIVE asynchronous
Core Database (OLTP)
no automatic failover
READ_ONLY connections

  1. A Reporting DB is currently restored nightly from core and then heavily indexed. My original intention was to move it to the London-Srv2 and leave it outside the availability group. But I moved the READ_ONLY connections to the Cambridge server as read my synchronous server should not allow connections to make the writes as fast as possible. I would also have to licence both REPLICA nodes, which isn't an option at that cost!

IF I configure READ_ONLY routing to Cambridge, does SQL Server detect which are read_only queries automatically or does every connection string have to specify that they are read only?

Will this perform OK with a decent link between data centres or should I licence the Srv2 in London and keep all READ_ONLY connections accessing here, along with reporting database ?

For a bit of background, its a bookings website, so not huge volumes of data and losing a minute or 2 of data is not mission critical like financial orgs

Best Answer

In order to redirect reporitng app or application with only read only queries you need to specify read only intent in application connection string (web.config or app.config in case client application is .NET)

The below is excerpts from SQL Server BOL

The application intent connection string property expresses the client application’s request to be directed either to a read-write or read-only version of an availability group database. To use read-only routing, a client must use an application intent of read-only in the connection string when connecting to the availability group listener. Without the read-only application intent, connections to the availability group listener are directed to the database on the primary replica."

Regarding your second question, if network throughput between two DC is OK you should be fine.

As long as any secondary replica is only solely used for DR (disaster recovery) and does not perform any work like read only request or backup you do not need to license it.

Related Question