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.
As 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.
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)!
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!
Database Resources is now a highly available database – we can look at its properties on the dashboard:
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):
You can read about user authentication in contained databases here:
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.
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).