0

I have an availability group with 3 replicas, as below: 

Node1: Sync Commit, Automatic Failover
Node2: Sync Commit, Automatic Failover
Node3: (Sync or Async) Commit, <===================I do not want this node to become Primary replica

In the abovementioned list, I want to make use of Node3, as dedicated backup node, which will never become the primary node(except when both Node 1 and Node2 are down), but is responsible for taking backups from my database(s). Is it possible to do this using AlwaysOn and Windows Failover Cluster?

I know that I can exclude nodes from participating in backup by configuring them in backup priority section, and I can exclude Node1 and Node2, but this will not prevent Node3, from being Primary node(Node3 can become as a primary node, whenever BOTH Node1 and Node2 are down). I think I should manage it via Windows Failover Cluster, but I do not know how...!

Event if I use AlwaysOn Availability Groups, how can I prevent Node3 from becoming a Primary node and also route the backups only to Node3, not any available secondary?

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
  • Out of curiosity, why? If nodes 1 & 2 are down for whatever reason, why not have node 3 be able to take over the AG? – Ben Thul May 18 '20 at 16:29
  • @BenThul I want to route my read-only requests to secondary, read/write requests to primary and backups to Node3. I do not want backups to be taken on any available secondary, except Node3. If Node 1 and Node 2, BOTH are down then Node 3 CAN act as primary. I have updated the question to clarify this – Vahid Farahmandian May 18 '20 at 18:19

1 Answers1

0

No, this is not possible with Failover Cluster (AlwaysOn Failover Clustering Instances) because a database can have only 1 active and running node across the FC.

What you need is AlwaysOn Availability Groups. Here you can setup an async replicate where you can perform backups.

Máté Farkas
  • 146
  • 2
  • Using AlwaysOn Availability Groups, how can I prevent a node from becoming a Primary node? – Vahid Farahmandian May 18 '20 at 09:43
  • Here you can see some guidelines for this: https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server?view=sql-server-ver15 – Máté Farkas May 19 '20 at 11:21