SQL 2014: Deploying AlwaysON availability group in a guest cluster – Part 3

In the previous article we walked all the steps required to build fault tolerant virtual machines and converted the test database (Resources) to the contained database. This post offers the detailed instructions on how to implement AlwaysOn inside two virtual machines – SQL1 and SQL2.
AlwaysOn Availabilty Groups require SQL Server instances to reside on the nodes in the WSFC failover cluster so the first thing to do is to create a guest cluster inside virtual machines SQL1 and SQL2. The process is almost the same as decribed in Part1 when creating a physical cluster, with some exceptions: 1) there’s only one vitual nic available to each vm, 2) a file share 9\\terminal\quorum) will serve as the quorum resource, not the disk as was in physical cluster.

AlwaysOn prerequisites:
https://msdn.microsoft.com/en-us/library/ff878487.aspx

I install Failover Clustering feature on SQL1 and SQL2:P3-1Then in Failover Cluster manager click “Create Cluster“:

P3-2 P3-4 P3-5 P3-6P3-7-1

P3-8p3-151 P3-16As you can see on the screenshots above there’re two DNS-related errors: if the Create Cluster Wizard for some reason  would not create the computer account for the cluster resource and corresponding A-record in DNS you can always do it manually – in my case both the cluster computer account and A record had been created successfully, but nevetheless the errors showed up in Failover Cluster Manager.

Clustering for AlwaysOn Availability groups DOES NOT require the use of symmetric shared storage, but as there are only two nodes (and NO quorum disk) in this guest cluster I must change cluster quorum setting to nodes and file share.

p3-9

P3-10P3-11P3-WITNESSshare P3-13P3-14

Now in SQL Configuration Manager I enable AlwaysOn High Availability – it must be done on ALL servers wich are to participate in AlwaysOn Availabilty group (on SQL1 and SQL2 in this test)!

P3-21

P3-22

Before we proceed to creating an AlwaysON availability group we must assign cluster account (GUESTCLUSTER$) FullControll permission to the OU that will be containing SQL Listener computer account (by default it is the OU containing SQL1 and SQL2 computer accounts) – otherwise Creating a new availability group wizard will fail to create the SQL Listener!

P3-New2Now we can start the wizard:

p3-22-1

P3-22-2

P3-25 P3-26 P3-27P3-28P3-29 P3-30 P3-31 P3-32  P3-33 P3-34

P3-35

P3-36-GOOD

P3-50-COMMON-ALAWAYSON P3-50-COMMON2-ALAWAYSON

Database Resources is now a highly available database – we can look at its properties on the dashboard:

P3-50-COMMON-DASHBOARD2P3-50-COMMON-DASHBOARD-SQL2

Let’s log in to Resources as Test\User1 – this user DOES NOT have a server instance login neither on SQL1 no on SQL2 so this is a “contained” user. (I will do it on another workstation):

P3-61-USER1 P3-62-USER1Please pay attention to the Connect to database field: we must type a database name explicitly when connecting to a contained database using a contained database user account!

You can read about user authentication in contained databases here:
http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/08/contained-database-authentication-in-depth.aspx

P3-63-GOOD

As AwaysOn Availability Group is a disastery recovery solution I’ll run a test switchover to make sure Resources database is still available for clients wichever node hosts a primary replica of the database.

On SQL1 click either “Failover” or “Start Failover Wizard”:P3-71-FAILOVER

P3-71-FAILOVER P3-72 P3-73P3-74 P3-75 P3-76

P3-78 P3-79

Switchover is copmleted. Let’s refresh User1’s connection and make sure Resources is still available:P3-80

P3-80-1

Summary:

This article concludes the series of articles on deploying AlwaysOn avilability group in a guest cluster. The  test database is made fault tolerant using AlwaysOn avialabilty group created on two virtual machines, and the virtual machines themselves, too, made fault tolerant using the physical cluster created in Part1.
You can read about how to configure a database backup on the AlawaysON secondary replicas in the upcoming article SQL 2014: Implementing Backup on Secondary Replicas (AlwaysOn Availability Groups).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: