Windows Cluster with AG

Question

Windows Cluster with AG

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

leon.booij Ten Centuries Points: 1062 More actions · Answer 1

Have you read these documents they are very handy

http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/Building%20a%20High%20Availability%20and%20Disaster%20Recovery%20Solution%20using%20AlwaysOn%20Availability%20Groups.docx

http://www.sqlservercentral.com/articles/AlwaysOn/91408/

Joe Sack SQLskills Old Hand Points: 314 More actions · Answer 2

Hi Sarah,

A recap and some clarifying questions I have for you:

- You have a three node Windows Server Failover Cluster

- You have three SQL Server instances - one per node? And are they standalone instances or Failover Cluster Instances?

- You created an Availability Group that spans all three SQL Server instances?

- Which instance hosts the read-write replica and which WSFC node does that instance currently "live" on?

- You created an AG Listener associated with the Availability Group

- Can you clarify what you mean by "windows cluster points to secondary"? You're seeing that the AG resource in Failover Cluster Manager is owned by a SQL Server instance hosting a secondary replica?

Thanks,

Joe

Sarah Wagner Hall of Fame Points: 3178 More actions · Answer 3

- You have a three node Windows Server Failover Cluster -- Yes

- You have three SQL Server instances - one per node? And are they standalone instances or Failover Cluster Instances? -- Standalone, one per node

- You created an Availability Group that spans all three SQL Server instances? - Yes one AG with 2 databases

- Which instance hosts the read-write replica and which WSFC node does that instance currently "live" on? -- Should be TestSql01

- You created an AG Listener associated with the Availability Group -- Yes AO01

- Can you clarify what you mean by "windows cluster points to secondary"? You're seeing that the AG resource in Failover Cluster Manager is owned by a SQL Server instance hosting a secondary replica? -- Cluster manager it says "Current Host Server: TestSql02", so when I connect in SSMS to the listener and do @@servername I get TestSql01, when I use the cluster name I get TestSql02.

Joe Sack SQLskills Old Hand Points: 314 More actions · Answer 4

So if you go in Failover Cluster Manager and expand "Services and Applications" and click on the Availability Group name - what do you see under "Current Owner" vs. "Preferred Owners"?

Assuming you have not configured read-only routing, and you're using the AG Listener name to connect via SQL Server Management Studio, you should be landing on the read-write primary replica - and you'll see the SQL Server instance hosting that replica via @@SERVERNAME.

Sarah Wagner Hall of Fame Points: 3178 More actions · Answer 5

We were able to resolve this by in the Failover Cluster Manager navigating from the cluster name to More Actions -> Move Core Cluster Resources -> Select Node and picking SQL01. Not sure why powershell wouldn't work.

Joe Sack SQLskills Old Hand Points: 314 More actions · Answer 6

For availability groups - you don't want to manage them using Failover Cluster Manager - you want to manage them using T-SQL commands.

I suspect we have a mismatch on what we're discussing (using different terminology).

An example hierarchy...

Windows Server Failover Cluster (WSFC)

WSFC Node 1

SQL Instance 1

Availability Group 1 - Primary (read-write) Replica

Database 1

Database 2

WSFC Node 2

SQL Instance 2

Availability Group 1 - Secondary Replica (Read-Only or Not)

Database 1

Database 2

WSFC Node 3

SQL Instance 3

Availability Group 1 - Secondary Replica (Read-Only or Not)

Database 1

Database 2

And "Availability Group 1" would have a listener associated with it. If connecting to the listener without read-only routing enabled, you'll connect to the primary read-write replica. In this example, I would have connected to "SQL Instance 1" on WSFC Node 1.