SQL SERVER 2012 HIGH AVAILABILITY SOLUTION (Always On availability Group Configuration)

In my last post I have covered how to install Windows Cluster environment and Windows Server 2012. To review have a look at this link

1. SQL Server 2012 high availability solution (Windows Server Cluster Configuration)
2. SQL Server 2012 High availability solution ( SQL Server 2012 Installation)

SQL SERVER Always On availability Group Configuration

Once SQL Server installed on both nodes (SQL01, SQL02). For testing purpose I have created AlwaysOn_TestPSO database on primary node SQL01. Database must be created with full recovery mode

A SQL Server 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 AlwaysOn 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.

An availability group consists of the following components:

·         Replicas, which are a discrete set of user databases called availability databases that fail over together as a single unit. Every availability group supports one primary replica and up to four secondary replicas.

·         A specific instance of SQL Server to host each replica and to maintain a local copy of each database that belongs to the availability group.

Replicas and failover

The primary replica makes the availability databases available for read-write connections from clients and sends transaction log records for each primary database to every secondary replica. Each secondary replica applies transaction log records to its secondary databases. All replicas can run under asynchronous-commit mode, or up to three of them can run under synchronous-commit mode.

Before starting Always on availability group you need to enable this feature from SQL Server service. 

Now try to create availability group

Error still shows there is some problem. We need to restart sql server service.

Make sure you will perform this step on both nodes.

For testing purpose I have created a table and then take a full backup of this database. Place this backup on share folder \\DomainServer\ClusterShareFolder path

Start setup using Availability Group Wizard

Specify name of availability Group.

Primary instance is already there you need to add other replicas and all your replicas are from different nodes as in our case SQL02 is secondary node and act as replica. Check options and look at the last column Readable Secondary that is selected as [No]. If you select it as [Yes] then your secondary replicas and then 3rd and 4th replicas are in readable form as well. This approach can be used for reporting purposes

If port 5022 is enabled or firewall is blocked then this step will automatically fill are data

These options are for Backup references, by default [Prefer Secondary] is selected I will leave as is.

You need create listener either user DHCP or TCP. You can get this from your network person

You need to set DNS name, default port and IP address that is not using in domain for any other VM or server. Network person will help you in this. You need to remember

1. DNS name must not be any system or VM name in domain
2. Port must be enabled through firewall
3. IP Address must not in use

All validation steps are complete

Backup location is displayed in this screen. I have missed out its screen shot during installation wizard

Once setup is completed you can view primary database as Synchronized. Notice availability group objects

1. SQL_Group: is our availability group for this database. You can add more groups that use different databases to handle
2. Replicas: can be added more up to 4 replicas
3. Database: more than one database can be grouped here under SQL_Group and all follows combined point of failure i.e if one database fails all other will also moves to its next replica
4. Listener: This listener verifies database sync state and moves data between its objects

SQL SERVER 2012 HIGH AVAILABILITY SOLUTION (SQL Server 2012 Enterprise Installation)

In my previous post; We have learnt how to install Windows Server 2008 R2 Failover Cluster. Reivew from this link

Setup Windows Server Cluster (2 nodes)

to Setup SQL Cluster we need to install SQL Server 2012.

SQL Server 2012 Installation

You can install standalone SQL server 2012 on both nodes that are ready for cluster setup or you can install it using single cluster node wizard. Microsoft .Net framework 3.5 or above needs to be installed on VMs. I will perform standalone here, cluster node installation require share disk to be install and I am doing this on share folder, that’s why I am unable to install SQL server using single cluster node installation.

Needs to add feature from windows features  

Start SQL Server installation this is Enterprise setup

SQL Server 2012 install updates if required. Internet connection is required

If there is any error or warning message i.e firewall is enabled, allow proper ports or disable firewall

Select appropriate features that are required to install for always on feature. Features are

• SQL Server setup
• Network Name,
• IP Address,
• Shared disk or nodes,
• Database Engine,
• Agent Service,
• Analysis Services if installed,
• One file share resource, if FILESTREAM feature is installed.

Along with database engine, analysis services service will be installed 

Changed database engine service account from [NT Service] user to the domain user i.e SQL . Otherwise it will generate error while you are configuring always group through wizard.

Mix user mode selected

SQL Server 2012 successfully installed. Please install it on both nodes to complete SQL Server Always On availability Group.

In Next Step 

1- Configure Always On availability Group

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

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

1. install SQL Server 2012 

2. Configure Always on availability group