In Part1 of the series of articles on deploying AlwaysOn availability group in a gust cluster we went through the steps required to build a physical cluster. In this part I’ll walk your through creating virtual machines with SQL Server 2014 Datacenter and making them highly available by adding to the newly created cluster.
First I’ll create two virtual machines – SQL1 on ClusterStorage\Volume1 and SQL2 on ClusterStorage\Volume2 and install Windows Server 2012R2 to both of them:
I’ll perform the same steps on Host2 for creating SQL2:
Once the VMs are created I will install Windows Server 2012R2 to them and add to the domain – cluster nodes (both of the physical and guest clusters) must be members of the domain.
Next I install .NET Framework 3.5 SP1 on Host1 (you can read more about SQL Server 2014 prerequisites here: http://msdn.microsoft.com/en-us/library/ms143506.aspx)
SQL Server installation on SQL2 succeeds as well:
Now it’s time to use the cluster we’ve created in Part1: I’ll make SQL1 and SQL2 the highly available virtual machines (I shut them down before configuring the role):
From now on I’ll be managing the virtual machines in Cluster Manager, not in the Hyper-V console.
SQL1 and SQL2 are now highly available virtual machines and can be hosted on any cluster node. For examle, SQL1 can be moved to Host2 during a planned switchover:
To enable contained databases at server level (both on SQL1 and SQL2!!!) I will either run the following commands:
sp_configure ‘show advanced’, 1
sp_configure ‘contained database authentication’, 1;
…or enable it in Management Studio:
To convert db Resources – either
ALTER DATABASE [Resources] SET CONTAINMENT = PARTIAL
Resources database already contains the db user I’ll be using for connecting to the database – for example TEST\Administrator – and there’s NO corresponding server login on SQL1, so by connecting to Resources under domain account Test\Administrator I will make use of contained database authentication.
If I needed to create a new contained user I would run this command:
CREATE USER [TEST\User1]
…and set appropriate permissions for the database:
In Part2 of the series of articles on deploying AlwaysON using SQL Server 2014 we created two virtual machines – SQL1 and SQL2- with SQL 2014 Enterprise, made them highly available by adding to the cluster role, restored to SQL1 and converted to the contained type the database Resources that will be made fault tolerant using AlwaysOn in Part3.