Search This Blog & Web

Friday, August 30, 2013

SQL SERVER 2012 HIGH AVAILABILITY SOLUTION (Windows Server Failover Cluster Setup)

A SQL Server Always On Availability Group is a new concept in 2012 SQL server. To understand high availability solution we need to understand following three concepts

1.      Windows Server Failover Clustering (WSFC)
2.      Failover Cluster Instance (FCI)
3.      Always On Availability Group
4.      Always On Availability Group Instance

Let’s understand above concepts in following paragraph

As part of the SQL Server AlwaysOn offering, AlwaysOn Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level—a failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable. Always On availability group enables you to specify a set of databases that you want to fail over together as a single entity. When an availability group fails over to a target instance or target server, all the databases in the group fail over also. Because SQL Server 2012 can host multiple availability groups on a single server, you can configure Always On to fail over to SQL Server instances on different servers. This reduces the need to have idle high performance standby servers to handle the full load of the primary server, which is one of the many benefits of using availability groups.

To setup high availability solution we need to setup windows server failover cluster. To setup this I have used Windows 2008 R2 Enterprise/Data Center with Service pack 1.

Windows Server Failover Cluster Setup
To install Cluster using windows server following are Pre-requisites
·         Windows 2008 R2 with Service pack 1
·         Shared Storage (SAN) is recommended for secure SQL Server availability solution but you can use Share drive or Folder to setup cluster in Windows.
·         For Lab purpose I have used VMWare to setup 3 instances of Windows server 2008 R2 SP1
·         My network consists of One domain Server and other two are Client machines
·         Disable the firewall for testing purpose if not then perform following two steps
·         Port 1433 must enabled (Inbound/Outbound) as it is default port for SQL Server across network
·         Port 5022 must enabled (Inbound/Outbound) as it is default port for SQL Server Always On availability group end point throughout the network
·         Domain user must have read all properties / create computer objects
·         Same user is used to run database engine service this will require to allow database synchronization between two databases.
·         1 Share drive or Share folder on a third drive (it is required for quorum settings)
Let’s consider my user name is Domain\SQL and windows servers are as follows SQL01, SQL02 along with domain server as DC01
Add Windows cluster feature from server manager on both nodes. (SQL01, SQL02)

This will restart your systems to show windows Fail over cluster manager in control panel. After restart go to the Server Manger and verify that Fail over clustering feature is enabled on both nodes.

Go to Windows Startà Administrative Tools à Windows Failover Cluster Managerà 

Open Failover Cluster Manager and start creating cluster setup. It is recommended and beneficial to validate configuration before setup. To setup cluster both nodes must be up and running and user have appropriate rights to communicate between both nodes. You might need Network person with you to help you creating cluster setup.
In my example I have made SQL01 node as Primary and SQL02 as secondary node. Start validating a configuration wizard

Next screen shot is asking permission to test all possibilities before setting up cluster environment

View successful report, there might be some errors regarding share storage disk but in the case of windows clustering we can continue with that

After successful verification Start create new cluster wizard

I have named my cluster as SQL-Cluster

Before finishing above is detail of your cluster. IP Address range is auto selected from you domain

You have successful configured you Cluster with minimal 2 nodes. Now have a look at cluster up and running in Windows cluster manager

Next step is creating Quorum setting for your cluster. 
Quorum; cluster generally requires more than half of the nodes to be up to facilitate high availability. This concept is called quorum. A Quorum is the numbers’ element that must be online for the cluster to continue running. In our scenario we have 3 nodes cluster (sql01, sql02 and share folder/node3). So if one node is down but node 2 and share folder is up cluster will remain on-line, the cluster will be on-line by winning majority vote. In the end of cluster set-up following alarms has been raised

To start setting up Quorum setup I have used Domain server DC01 server and create a share folder on one of its drive. Let's configure quorum setting for SQL-Cluster from Failover cluster manager

Select Quorum settings. The following screen describes the different quorum modes available since Windows Server 2008.

Select share folder

Selected options are shown before configuring quorum

Once Quorum configured you can view its type and detail on SQL-Cluster setup screen from Cluster Manager

At this point we have successfully established Windows server cluster WFCS and FCI. To configure SQL Server 2012 feature Always On availability we need to install SQL Server 2012 on both nodes (node1, node2).

You need to Install SQL Server 2012 Enterprise or Data center edition to use this feature

During next steps

No comments: