Tag Archives: HA

How to set up AlwaysOn Availability Group in SQL Server 2016

SQL Server 2016 has improved the HA/DR solution – AlwaysOn, and also adds this great feature in Standard Edition with some limitation. AlwaysOn in SQL Server 2016 Standard Edition is very similar with the mirroring feature which was deprecated on SQL Server 2012.

This is the some limitation of AlwaysOn in SQL Server 2016 Standard Edition.

48

Actually, SQL Server 2016 is not released yet. Current released version is SQL Server 2016 RC3(April 15, 2016), and it has only Evaluation(Enterprise), Developer, and Express Edition. So I couldn’t confirm the limitation of this feature on Standard Edition.

 

So I just would like to introduce you how to install and configure the AlwaysOn Availability Group with SQL Server 2016 Evaluation Edition.

 

1. Set up Windows Failover clustering

01

The big difference between mirroring and AlwaysOn is the Failover clustering. Mirroring doesn’t use it, but AlwaysOn use it. Let’s get started to install the Failover clustering on Windows Server 2012. SQL Server 2016 support Windows Server 2012 or higher version.

Install Failover clustering feature on the first node.

02

 

03

 

04

 

05

 

06

And do same thing on the second node.

Then open the Failover Cluster Manager on any node.

07

Click Validate configuration.

08

Add nodes to join this new cluster.

09

 

10

 

11

 

12

 

After finish this validation, please review carefully the report if there is any error message.

13

 

Now it’s time to create new cluster if there is no error message on the report.

14

 

15

 

AlwaysOn doesn’t need any shared disk on the Failover clustering, so when new cluster is created, “Add all eligible storage to the cluster” should be unchecked.

16

 

When the cluster creation is finished, the Failover Cluster Manager will look like this.

17

 

2. Install SQL Server 2016 RC3

18

It’s same as previous version. SQL Server 2016 doesn’t need .Net Framework 3.5. Instead, it uses .Net Framework 4.0.

19

 

20

 

21

 

22

 

23

 

27

 

28

 

SQL Server 2016 installation image doesn’t have SQL Server Management Studio(SSMS) and SQL Server Data Tools(SSDT). These tools need to be downloaded separately. If you click “Install SQL Server Management Tools”, the installer will open the web browser so you can download the tool.

29

This is new SQL Server Management Studio. Its theme looks like Visual Studio 2012.

30

 

31

 

32

 

3. Set up AlwaysOn Availability Group

33

First, enable AlwaysOn Availability Groups on SQL Server service’s properties.

34

 

35

I will configure AlwaysOn on the database “AG_Test”.

36

If the second node doesn’t have same database on the instance, SQL Server 2016 will create same database during the configuration of AlwaysOn. But if the database is huge, the AlwaysOn configuration time will be quite long. So I backed up this database on the first node and then restored it on the second node.

37

Create new Availability Group on the first node.

38

 

39

 

40

 

41

 

The target database is already copied and restored on the second node, so “Join Only” will be chosen. If you don’t have the same database on the second node, “Full” should be selected.

42a

 

43

 

44

45

Now it’s done!

46

When you see the Availability Group’s Dash board, it will look like this.

47

 

Reference : Introducing Microsoft SQL Server 2016 Preview2 – Stacia Varga, Denny Cherry, Joseph D’Antoni