Skip to main content

Create 3-Node Windows 2012 Multi-subnet Cluster

Environment

There are two Data centers connected via a WAN link.



Two Windows 2012 Servers (called SQLDEV1 and SQLDEV2) are located in the Primary Data Center (on the IP subnet 192.168.79.0/24) and the third server is placed in the Secondary Data Center with the 192.168.69.0/24 subnet. We’ll be creating a three-node Windows cluster with no shared storage on the multi subnet network with a file share witness at the Primary Data Center. We’ll be using a file share witness to protect from the cluster failure in a situation when the network between the Data Centers is unavailable and one of the servers in the Primary Data Center is also down (or being rebooted).


The final state will look like depicted above:
-          Two Virtual IP’s will be assigned (192.168.76.218 and 192.168.69.134) to the cluster
-          The servers at the Primary Data Center will have a vote (Vote=1) and the server at the Secondary Data Center will have no vote (Vote=0). The file share witness always has a vote
-          The cluster network name will be DevCluster1.

Install Failover Cluster Features



Check with PowerShell that the Failover Clustering features are not installed on SQLDEV1, SQLDEV2, SQLDEV3

Get-WindowsFeature *clu* -ComputerName sqldev1

The output shows that these features need to be added


From the Server Manager (where we have already added all 3 servers to the sqldev pool)
Do right-click and select
Add Roles and Feature





In the Wizard select “Role-based or feature-based installation”
Select a server from the pool (SQLDEV1 in this case) and click Next


Select the “Failover Clustering” Feature



And on the new window click “Add Features”


Continue on with the wizard to install the selected features.

For legacy clustered applications (and SQL 2012 is a legacy application) Failover Cluster automation and failover cluster command interface are required.

Go the same wizard again and in the Features menu  and under

Remote Server Administration Tools à Feature Administration Tools à Failover Clustering Tools

select

Failover Cluster automation and failover cluster command interface

(This can also be selected when the Failover Clustering is installed. Again, this is required for legacy application only and because we’ll be installing SQL 2012 we need to add these two features. Otherwise skip this step)



Verify the installation by executing the same PowerShell commands

Get-WindowsFeature *clu* -ComputerName sqldev1

Now it shows that all the servers have the failover clustering features installed




Creating Windows Failover Cluster

Before proceeding make sure that 2 new IP addresses and a cluster name have been provided to you.

From the Failover Cluster Manager select “Create Cluster”.
Add all the servers (SQLDEV1, SQLDEV2, SQLDEV3) to the list of servers to be added to the cluster and click Next



Enter

DevCluster1
Into the cluster name

And 218 for the first subnet and 134 for the second one

And click Next



Uncheck “Add all eligible storage to the cluster” and click Next


When completed  you'll see errors and warning (this is because the cluster spans multiple subnets) like this one is expected and should be ignored





Granting NTFS permissions on File Share


The computer account for the cluster name (DevCluster1) must have the Modify permissions on the file share to be used as a witness.
Log into the server where the file share was created and on the security tab for the folder grant the Modify rights to the computer account DevClustr1$.

Configuring the Quorum Settings

From the Failover Cluster Manager right-click on the cluster name and select “More Actions” à “Configure Cluster Quorum Settings”

Advanced quorum configuration and witness selection
On the Voting Configuration page Remove vote from SQLDEV3


Then select "Configure File Share witness" and provide the path to the file share.

Configuring Possible Owners for Virtual IP Addresses

Because the cluster spans two subnets the virtual IP addresses can be owned by subset of cluster nodes.
For example, 192.168.79.218 may be owned by SQLDEV1 and SQLDEV2 only. And 196.168.69.143 may be owned by SQLDEV3.

In the Failover Cluster Manager select the cluster and then in the “Cluster Core Resources” right-click on the 1st Virtual IP (192.168.78.218) and go to the Advanced Policies tab.

Select SQLDEV1 and SQLDEV2 as  possible owners for this IP


Testing Cluster Failover

To test cluster failover run this PowerShell command and specify each of 3 servers in the node parameter:

Move-ClusterGroup   “Cluster Group” -node sqldev1


N.B. The cluster service registers both virtual IP addresses (192.168.79.218 and 192.168.69.134) for the cluster name (DevCluster1) and this can be confirmed by executing

IPCONFIG /displaydns

But the PING command is not multi-subnet aware and pinging the cluster name (ping devcluster1) after failing over to the secondary data center will not work (when executed from SQLDEV1 or SQLDEV2) because the cluster service takes offline the virtual IP address in the primary data center (192.168.79.218) but the DNS registration will not change and ping will try to use the IP address from the same subnet as the server it runs from. That’s why the proper failover test should be done with the PowerShell cmdlet.



































Comments

Post a Comment

Popular posts from this blog

Joining Windows 10 to Azure AD Domain

As of October 2016 to join Windows 10 computers to Azure AD Domain service requires these steps: Create a VNET in the classic portal . The VNET must be placed to a region where Azure AD domain service is available (( https://azure.microsoft.com/en-us/regions/services/ )  In the classic portal  go to Directory -> Configure and enable the domain service. And wait for ~ 30 min When completed the IP address will be populated Go back to the VNET configuration and add a DNS server with the IP (10.0.0.4 in this case) Create the "AAD DC Administrator" administrators group (again in Directory -> Group). Members of this group are granted administrative privileges on machines that are domain-joined to the Azure AD Domain Services managed domain. Add to the group your users who are supposed to have the administrative access on a Windows 10 computer go to Settings -> Accounts (this is true for Windows 10 version  1607) then select 'Access work

Generate Calendar Table in Power BI with Fiscal Year Attributes

In Power BI go to Get Data --> Blank Query and paste into the Function windows the text below. This function takes as parameters: - StartYear (e.g., 2012) - EndYear (e.g., 2018) -FiscalYearStartMonth (e.g., 4) And it will generate a calendar table for dates from Jan-1-<StartYear> till Dec-31-<EndYear> and columns for Fiscal Year, Fiscal Month, Fiscal Quarter, Fiscal Year Day where the Fiscal year begins on FiscalYearStartMonth = (StartYear as number, EndYear as number, FiscalYearStartMonth as number)=> let     //Capture the date range from the parameters     StartDate = #date(StartYear, 1, 1),     EndDate = #date(EndYear, 12, 31), //Get the number of dates that will be required for the table     GetDateCount = Duration.Days(EndDate - StartDate)+1, //Take the count of dates and turn it into a list of dates     GetDateList = List.Dates(StartDate, GetDateCount,     #duration(1,0,0,0)), //Convert the list into a table     DateListToTable