SQL
SERVER 2012 HIGH AVAILABILITY SOLUTION
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).
WINDOWS SERVER 2012
ENTERPRISE
You need to Install
SQL Server 2012 Enterprise or Data center edition to use this feature
During next steps
