Waclaw Chrabaszcz
... there is no spoon ...

SQL 2012 – How to setup AlwaysOn cluster

Thursday, May 1, 2014 12:59 PM

As an application administrator, or transition engineer I need to store my data on some sort of database. Because SQL itself is not my primary focus I rather prefer "I don't care" solutions. It not means I don't care whether database is running or not. It mean I don't have enough time to become an expert in additional area to setup and understanding e.g. Log Shipping. What's more, it's not my problem only. I'll setup some SQL HA solution and walk away, whereas poor WinAdmin will RDP there day by day with heart palpitations.

Since SQL 2005 we've got two user friendly solutions, unfortunately each has a weak point.

  • SQL Cluster –needs a shared storage, in most cases expensive Enterprise class Disk array. And when we talk about DR scenario, we have to switch to remote location, as a result complexity and difficulty are growing very fast.
  • SQL Mirroring – for some reason not liked by many SQL admins (to be honest I would like to know why), and the weak point is limit to two nodes only.

Now with this background we can start to talk about SQL 2012 AlwaysOn. AlwaysOn thanks to mixing SQL and Server technologies removes mentioned limitations.

  • We can build cluster with more than two nodes – I think Windows Cluster (2008/1012) sets limit of nodes. In most of business cases you won't need more than 4-5 nodes. Only two of them works synchronously and can be failover automatically (HA), other are feed with data asynchronously (DR) and should be failed over manually. Good news, to not waist resources (and licenses) async nodes are not passive and you can use them for read only proposes e.g. reporting. Of course it can remove lot of load from primary production database.
  • We don't need any shared storage, what is huge advantage when we talk about Disaster Recovery scenarios.
  • My poor WinAdmin has his lovely right click tool J

In this exercise we will build typical SQL 2012 AlwaysOn Solution:

  • SQL-1 and SQL-2 will be High Availability pair in primary datacenter
  • SQL-2 will Disaster Recover in secondary DC, we will use SQL-3 for reporting proposes

Let's do it!

In first step on each node we will install cluster feature:

  1. In the second we will build Windows Custer using all nodes




    Read it carefully




    in production I would recommend you to use static IP address
  2. Now you can prepare each SQL instance for clustering

Make sure that both Replication Partners are added into each SQL users:

(an example for SQL-1)

use [master]
GO
CREATE LOGIN [TESTDOMAIN\SQL-2$] FROM WINDOWS
CREATE LOGIN [TESTDOMAIN\SQL-3$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [TESTDOMAIN\SQL-2$]
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [TESTDOMAIN\SQL-3$]
GO

  1. We will need to make full backup of our database for initial replication. Use \\UNC\path\

  2. Eventually we can enable AlwaysOn on the database






   

   

   

Voila !




Feedback

# re: SQL 2012 – How to setup AlwaysOn cluster

Winning tips to earn from Thai Government in Thailand lottery 8/22/2017 1:12 PM | jahiq

Post a comment