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

On By ADsqlIn Uncategorized

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 5th part of the series where we will take you through setting up Standalone SQL server installation on DR i.e. secondary DC followed by configuring AG between Primary DC(SQL FCI) to DR

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 that means SQL sever failover cluster setup would be between Nodes of Primary DC(California in same subnet only).

NewYork DC (Different subnet) 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-4 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 steps involved setting up setting up Standalone SQL server installation on DR i.e. secondary DC followed by configuring AG between Primary DC(SQL FCI) to DR.

Install standalone SQL on DR i.e. Secondary DC
  • Refer part-1 for IP details/rights required here and refer any blogs on installing standalone SQL server
  • Once this is completed, we will be good to proceed with configuring AG
Configure Always On between SQL FCI and DR

Go to SQL FCI owning node–>open SQL server configuration manager–>right click property of SQL service–>click on AlwaysOn High Availability tab

You will see the SQL cluster name and click check Enable AlwaysOn high availability groups and restart the service

IMP Note:-

As per our current scenario, here you need to put 2 virtual IPs on Listener setting page while configuring AG.

one for Primary DC and second for secondary DC. i.e. same and different subnet.

Click here to see AG configuration steps.

Click to get part-4 of it.

Hope this helps!

A king orders once, a great scholar speaks once, and daughters are married once. Therefore, one must be very careful regarding those

Chanakya