This step-by-step has been created to help you get started in
creating a SQL Server 2012 AlwaysOn Availability Group for your databases.
In order to follow this post, you need the following :
Three (3) VMware Host with following details:
1) One Domain Controller (DC), Windows Server 2008 R2 Failover Cluster with 2 Nodes. (WIN1 & Win2). You can follow my post on setup Windows Server Failover cluster
2) Microsoft SQL Server 2012 (Standalone) Installed on Both the nodes. (WIN1 & WIN2)
3) SQL Instance on WIN1 : WIN1\WIN1SQL12
4) SQL Instance on WIN2 : WIN2\WIN2SQL12
5) Assign Service accounts with domain permissions to SQL Service.
6) Off the firewall Settings on both the node.
7) Shared folder on the Primary Node.
8) Make sure that you are able to connect both SQL Server Instances from both Node.
Enable Availability Groups
on SQL Server
This needs to be
done on all of the SQL Server instances that you will configure as replicas in
your Availability Group. To enable the SQL Server 2012 AlwaysOn Availability
Groups feature
1.
Open SQL Server Configuration
Manager
2.
Select SQL Server Services
3.
Right-click on your SQL Server
(in our example it is named WIN1SQL12) and select Properties
4.
Select the “AlwaysOn High
Availability” tab and check "Enable AlwaysOn Availability Groups"
5.
Restart the SQL
Server service.
Follow
the same steps on Second Instance (WIN2SQL12)
Sample
DB for Always On Configuration
Create
a sample Database on WIN1SQL12 Instance (which we are going to set as a Primary
Instance)
Here
we are giving DB name as “Test_AlwaysOn”
Make
sure that Recovery Model for the database should be Full.
Take
Backup of Test_AlwaysOn DB in a shared folder, so we can copy the backup file
on secondary Server and restore there.
Open
the shared folder on Secondary Node (WIN2) via Network Share.
Copy
the backup file on Secondary (WIN2) Server.
Restore
Test_AlwaysOn DB on Secondary Node (WIN2) in No recovery Mode.
Make
sure you select RESTORE WITH RECOVERY Option.
So
Now Database State will be in Restoring Mode.
Create and Configure SQL
Server 2012 AlwaysOn Availability Groups
To create and
configure a SQL Server 2012 AlwaysOn Availability Group,
1.
Choose any one instance to become
the PRIMARY (say WIN1\WIN1SQL12)
2.
Open SQL Server Management Studio
on WIN1\WIN1SQL12
3.
Expand the Management folder
4.
Right-click “Availability Groups”
and select “New Availability Group Wizard…”
In the Specify
Availability Group Name page, enter the name of the Availability Group
in theAvailability
group name: field. Click Next.
In the Select
Databases page, select the checkbox beside the database
that you want to include in your Availability Group.. Click Next.
In the Specify
Replicas page,
under the Replicas tab, click the Add Replicas button and connect to the other SQL
Server instances that you joined as nodes in your Windows Server Failover
Cluster.
·
Automatic Failover (Up to 2) :
Checked
·
Synchronous Commit (Up to 3)
: Checked
·
Readable Secondary:
Yes
In the Listener tab, select
the Create an availability group listener option. Enter the
following details.
·
Listener DNS name: Name that you will
use in your application connection string
·
Port: 1433
Click the Add… button to provide an IP address. In
the Add IP Address dialog box, enter your preferred
virtual IP address in the IPv4 Address field. Click OK. Click Next.
In the Select
Initial Data Synchronization page, select the Join option.
In the Validation page,
verify that all validation checks return successful results. Click Next.
In the Summary page, verify
all configuration settings and click Finish. This will create and
configure the AlwaysOn Availability Group and join the databases.
In the Results page, verify
that all tasks have been completed successfully.
View
the Availability Group in SSMS
In SSMS, drill down to Management and Availability Groups.
Here you will see your Availability Group. Drill down one farther and you'll
see Availability Replicas, Databases, and Group Listeners.
To add a database, replica or view the dashboard right click
on the Availability Group Name.
The dashboard will help you determine if your databases are
Synchronized and Healthy
You
can also check the Availability Group from Failover Cluster Manager.
Failover
Testing
There
are 2 ways via you can test your Always On Availability Failover.
1) Via Failover Cluster Manager.
2) Via SSMS.
1)
Failover
Always On Availability Group via Windows Failover Cluster Manger:
- Open Failover Cluster Manager
- Right Click on Always on Group. Currently Primary Node is WIN1.
- Move the Service or Application on Secondary Node
- Click on your Secondary Node (WIN2)
- Confirm Movement of Always on Group on Secondary Node. (WIN2)
- Confirm Successful Failover of Cluster on Secondary Node (WIN2)
- Confirm the Failover from SSMS.
- Check Availability Group on Primary and Secondary SQL Instance.
- Current Secondary Replica (Instance) : WIN1\WIN1SQL12
- The dashboard will help you determine if your databases are Synchronized and Healthy
- Current Primary Replica (Instance) : WIN2\WIN2SQL12
- The dashboard will help you determine if your databases are Synchronized and Healthy
2) Failover from
SSMS:
- Go to the primary Instance
- Right click on your Availability Group
- Click on Failover
- Click on next on the Introduction Window.
- Select the primary Replica (Instance) on Which you want to failover your Always On Database.
- Provide the Credential and connect to the Primary Instance.
- Click on Finish.
- Check the result for Successful Failover and click on Close.
- Check the Availability Group configuration after successful failover on both primary and secondary Replica (Instance)
- The dashboard will help you determine if your databases are Synchronized and Healthy
- Current Secondary Replica (Instance): WIN2\WIN2SQL12
- The dashboard will help you determine if your databases are Synchronized and Healthy
- Current Primary Replica (Instance) : WIN1\WIN1SQL12
If you get stuck with the setup somewhere, please leave a comment here and I will be glad to help.
Have fun!
