AlwaysOn Availability Groups SQL Server 2012

AlwaysOn Availability Groups SQL Server 2012

In this post we will learn how to configure AlwaysOn Availability Group in SQL Server. I am using SQL Server 2012 Enterprise edition here. On my previous post I have already discussed about introduction of AlwaysOn Availability Groups SQL Server. We have taken two Nodes here to configure AlwaysOn, details of them are mentioned below.

In our earlier post we had discussed Introduction of AlwaysOn Availability Groups SQL Server.

Both these nodes are joined in domain and user Mandeep has been mapped on servers.

Node1:

Operating System: Windows Server 2012 Standard

IP Address: 192.168.160.71

SQL Service Account: mandeep@hightechnology.in

DNS Name: Node1.hightechnology.in

SQL Server: 2012 Enterprise Edition

Node2:

Operating System: Windows Server 2012 Standard

IP Address: 192.168.160.72

SQL Service Account: mandeep@hightechnology.in

DNS Name: Node2.hightechnology.in

SQL Server: 2012 Enterprise Edition

Now install failover clustering on both servers, take a restart and now proceed for SQL Server 2012 Installation.

Install SQL Server 2012 on both nodes, I have installed (Database services & Management Tools) only. User Mandeep has been mapped as SYSTEM ADMINISTRATOR and run all SQL Server services from this user. Once Installation is finish.

Now go to anyone node which you would like to make primary and go to Server Manager > Tools > click on Failover Cluster Manager.

  1. Click on create Cluster
  2. Pop-up window will open, click on Next.
  3. On this windows add both nodes. Click Next.
  4. In this window, provide name for cluster and IP address. Click Next.
  5. In this window choose from mentioned option, I have checked to not run validation test.
  6. Click next, click on Install and our cluster has been created.

Go to SQL Server configuration manager & Right click on go to properties and click on AlwaysOn Availability tab and enable this feature on both nodes and do a restart of SQL Server services.

Now open SQL Server Management Studio on Primary Node and creates two databases, create some tables on them.

Backup them on a location and do share & give permission to our domain user Read/Write.

Now Right click on Availability Groups > Availability Group Wizard.

In next step you can start the Availability Group Wizard from SQL Server Management Studio that asks you in the first step for the name of your new Availability Group.

In Next step choose databases which will be part of your Availability Group.

In the next step you can specify how many replicas you want to have for your Availability Group, if you want to have an automatic failover between them (up to 2), if you want to have synchronous or asynchronous data movement between the replicas, and if you want to have read-only access for a secondary replica. So there are a lot of things that must be configured in this step.

In the next step you have to configure how you want to join your Secondary Replicas to your AG. There are 3 options available:

  1. Full
  2. Join Only
  3. Skip Initial Data Synchronization

In the final step the wizard validates all your configured settings, and you can create your first AlwaysOn Availability Group.

Now you can work with your created Availability Group. Just insert some records into the table on your Primary Replica. The generated Transaction Log records will be transferred to your Secondary Replica.

Video Tutorial: