SQL Server Always On Availability Group

Overview:

SQL server always on Availability group is a new feature developed in SQL server 2012 on the basis of SQL mirroring and Windows server failover clustering. So the environment must achieve the same requirement of the windows server failover clustering and SQL Installation. For configuration of a fail over cluster, Environment should be configured with a domain network .For the best practices configure the Domain network with 2 Node domain controller.

Prerequisites

1. Domain controller
2. Windows server  2012 or windows 2008 r2 ( Enterprises or data-center)
3. SQL server 2012
4. Shared storage

Scope:

1. Configure the single node domain controller
2. Install SQL server on each machine
3. Configure the Windows fail over cluster in SQL nodes
4. Enable the SQL server Always on Availability Group
5. Creating a Always on Availability group and listener
6. Adding a Secondary read write Replica in the Environment
7. Adding a third node with read only replica

Configuring Windows Server Failover Clustering

Install fail over cluster feature in windows server

1. Make sure all SQL NODE is configured as a domain member server
2. Configure the shared storage and initialize the disk in all SQL nodes
3. Open server manager in SQLNODE1
4. Click on the feature in  and click add feature
5. Select fail over cluster and click next
6. Repeat this step in both SQLNODE2 and SQLNODE3

Validate the Cluster requirement

1. Open fail over cluster manager in any one of the cluster node
2. Click “Validate a configuration”  and click on next
3. Click browse button and enter the server name , Click OK to select the server then next
4. On testing  option page  select  “ Run all test recommended” and click next
5. Ignore the” IP WARNING MESSAGE “
6. Once it is completed click finish to close the page

Creating Fail over cluster

1. Open fail over cluster manager in any one of the cluster node
2. Click create a cluster link
3. Click browse button and enter the server name , Click OK to select the server then next
4. Enter the cluster name of the cluster (Eg: SQLHACL1)
5. Remove the tick mark on the DHCP
6. In the address box enter the Cluster IP address and click next.
7. For the best practices  Make sure the IP address should be in the same subset
8. On the summary page click finish

Always on availability Group

Configure always on availability group

1. Open SQL configuration manager in SQLNODE1
2. Take the properties of the instance ( Default MSSQLSERVER”)
3. Select the proper account in the logon tab and enter the password for the account
4. Please enter a domain level account
5. On the always on high availability tab click the check box to enable the availability group
6. Close the both warning
7. Restart the SQL service from the SQL configuration manger window
8. Do the same on both SQLNODE2 and SQLNODE3
9. Enter the same account in Every node

Making Always on availability for a Database
Note: Please ensure that database are configured in full recovery mode

1. Open SQL management studio in SQLNODE1
2. Ensure that database recovery mode is set to full
3. Take a backup of the database using SQL backup window
4. Expand Always on high availability node tab in management studio
5. Right click and select “New availability Group wizard”
6. Enter the name of the availability  group (Eg:SQLHA-1)
7. On the Select Databases page , click the check box to select the Data base
8. In the replica page select SQLNODE2
   1. Automatic fail over : Select both node (Node1 and node 2)
   2. Synchronous Commit: Select both node (Node1 and node 2
9. Click next to finish the configuration

Availability Group Listener

1. Availability Group Listener
2. Open sql management studio in SQLNODE1
3. Expand Always on high availability node and navigate to SQLHA-1
4. Click right button and select add listener
5. Type a DNS name for the listener (eg: SQLHA-1 listener)
6. Port :1433 ( Ensure port is opened )
7. Network mode static
8. Add IP address for the Listener
9. Make sure the ip address should be in the same subset
10. Click OK to Create and finish the wizard

Note: Verify the service name under the fail over cluster manager