Configure SQL server fail-over cluster and Always On in multi-subnet environment-Part 3

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

InterServer Web Hosting and VPS

Lets Begin: We are going to start a series of blogs on configuring SQL server fail-over cluster and AG in multi-subnet environment.

This is the 3rd part of the series where we will take you through setting up quorum for voting.

Also note, we want SQL fail-over to be happened on California i.e. in same DC or same subnet only. We do not want here that SQL cluster fail-over to NewYork DC.

NewYork DC will be dedicated for AG only,like AG should be owned on California or NewYork but SQL cluster should always been on California Primary DC.

Click Here to get part-2 of this series.

Windows server 2012 R2 and SQL sever 2014 are taken as an example in this series.

  • IMP Note:- Multi-subnet windows cluster has been supported from Windows server 2008 Onward but for SQL, multi-subnet fail-over cluster started from version:- SQL server 2012 onward.
  • We have considered the setup for multi-subnet as per below example.
  • California–> In same subnet–>Same(1st) Datacenter–>SQL fail-over cluster will be setup between them in same subnet.
  • New York–> In Different subnet–>Different DC(2nd)–> AG will be setup between 1st and 2nd DC i.e. multi-subnet network.

In this part we will talk about windows cluster quorum configuration.

Windows Cluster Quorum Configuration

Since the Cluster for Windows created now we need to configure the Quorum Setting for Windows Cluster. GO To Windows Cluster name–>Properties–>More Actions –>Select Configure Quorum Settings as shown in below screenshot.

Select advance quorum configuration

Select only California Primary DC node for voting as we discussed above why we need to add only these 2 Primary DC and not to included NewYork DC node. Do not select DR node to keep without vote.

Configure the file share witness

We will have put two-file share path. One for California site here and one for DR site during AG setup.

Please refer Part-1 of this series to under properly about IP and file share details.

Click Next, verify and Finish the setting.

Now, take a look on setting up Failover Cluster MSDTC

If you are installing only the Database Engine, the MSDTC cluster resource is not required. If you are installing the Database Engine and SSIS, Workstation Components, or if you will use distributed transactions, you must install MSDTC. Note that MSDTC is not required for Analysis Services-only instances.

Go to Failover cluster manager and create role..

Now put the name for MSDTC and virtual IP, see part-1 for IP details.

Select Disk which you want to assign for this role, verify and Finish the setting.

Hope this helps!

Click to get its part-2

Part-4 coming soon………


Do not spend even a single day where you can not find five things: Successful businessmen, educated brahmins, soldiers, a river and a doctor

Chanakya

 

One thought on “Configure SQL server fail-over cluster and Always On in multi-subnet environment-Part 3

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s