question

Ken-9235 avatar image
0 Votes"
Ken-9235 asked AmeliaGu-msft commented

Loss of Primary Data Centre.How to manually failover in SQL 2012 three nodes with AG on

Hi Everyone,

We are planning for DR where we have two DataCentre's one in location 'A' and other one in location 'B'. Currently our Primary DC is in location 'A' and we are planning to bring down Data center location 'A' . In case of Disaster Recovery where DC 'A' goes down , we need to bring up our secondary DC which is in location 'B'. The current setup of three node cluster failover with Always On configiured.

SQL version on three node cluster is SQL 2012 SP4 enterprise edition

we have three node clusters :

SQL01P (Primary) -Automatic failover -> Synchronus Commit
SQL02P(secondary) - Automatic failover -> Synchronus commit
SQL03P (secondary) - Manual failover -> Asynchronus commit

SQL01P & SQL02P is in synchronus commit whereas SQL03P is in Asynchronus commit. Both SQL01P & SQL02P servers belong to same Data Center 'A' whereas SQL03P is in secondary Data centre location 'B'.

My question is when our primary DC 'A' goes down how we gonna bring up secondary DC 'B' ?
what are the steps required to bring up secondary DC 'B'?


Please help.

Regards,
Ken

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered Ken-9235 edited

My question is when our primary DC 'A' goes down how we gonna bring up secondary DC 'B' ?

If you setup everything correctly, then the secondary in a failover cluster comes up automatically; that's what a cluster is good for.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for replying.

My cluster configuration is little different. Like I said, we have three node cluster with AG's ON. Two nodes SQL07P & SQL08P are in automatic failover (Synchronus commit) in Primary Data Centre but third node SQL06P in Manual failover (Asynchronus commit) in Secondary Datacentre.

we have three node clusters :

SQL07P (Primary) -Automatic failover -> Synchronus Commit
SQL8P(secondary) - Automatic failover -> Synchronus commit
SQL06P (secondary) - Manual failover -> Asynchronus commit

137517-image.png

When primary DataCentre goes down including SQL07P &SQL08P server , How should I perform failover as SQL06P node, failover mode is in Manual mode?

I think I need to perform forced failover it with some data loss but how? please share me steps if possible?

-Do we need to force a quorum and then bring the surviving cluster nodes back online in a non-fault-tolerant configuration?

Also, how should I reconfigure back SQL07P & SQL08P node back in synchronized state in case of primary DC crashed?

-will it synchronized again once I perform forced failover?

Regards,
Ken


0 Votes 0 ·
image.png (21.2 KiB)
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered AmeliaGu-msft commented

Hi Ken-9235,

Where is the Quorum? If you have two nodes and disk witness (or file share witness) in primary data center A, and the whole site
goes down, you will need to force the start of the cluster on the remaining node in your DR site because you have lost too much at one time.
You can change the availability mode of the secondary replica SQL06P on the site B from asynchronous to synchronous with manual failover or automatic failover, do a failover from the primary data center to remote data center before shutting down the primary data center.

Best Regards,
Amelia

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Amelia for the reply.
we don't have quorum neither witness.This is three node cluster with Always ON configured managing through WSFC service.

0 Votes 0 ·

Hi ken-9235,
Thanks for your reply.

we don't have quorum neither witness.This is three node cluster with Always ON configured managing through WSFC service.

Then you will need to force WSFC Cluster to start on the remaining node in your DR.
Please refer to Force a WSFC Cluster to Start Without a Quorum and Understanding cluster and pool quorum which might be helpful.

Best Regards,
Amelia


0 Votes 0 ·