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

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:

On Host1:P1-4P1-5-1As you can see SQL1 will be connected to the virtual switch that’s based on the network adapter  named HV.

P1-6P1-7

I’ll perform the same steps on Host2 for creating SQL2:

P1-8P1-9-4P1-10I’ll set the size of the memory for SQL1 and SQL2 =5196Mb and the number of processors = 2. P1-10-1P1-10-2

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)

P2-11P2-12P2-13 P2-14and  run SQL Server 2014 setup:

P2-16 P2-17 P2-18P2-18-1I’m going to install only Database engine and Management tools:

P2-19 P2-20 P2-20-1

P2-21

P2-22The key takeaway here that we must use the same domain user accounts on both servers to be able to enable AlwaysOn later!!!

P2-23

P2-24

P2-25SQL Server 2014 Enterprise is successfylly installed on SQL1.

SQL Server installation on SQL2 succeeds as well:

P2-27

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):

P2-31

P2-32 P2-33P2-34 P2-35 P2-36This warning merely says Hyper-V can’t and my ISO for Windows Server 2012R2 as a clustered storage – this ISO is attached as drive D: on both virtual machines.

P2-37

From now on I’ll be managing the virtual machines in Cluster Manager, not in the Hyper-V console.

P2-38

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:

P2-39 P2-40P2-41

There’re only two steps left to complete: restoring the backup of my test database we’ll be using in the AlwaysOn availability group to SQL1  and converting it to the contained database. P2-51 P2-52

P2-53

P2-54

To enable contained databases at server level (both on SQL1 and SQL2!!!) I will either run the following commands:

 

sp_configure ‘show advanced’, 1
GO
reconfigureGO

 

sp_configure ‘contained database authentication’, 1;
GO
RECONFIGURE;

GO

…or enable it in Management Studio:

P2-56-CODEs

To convert db Resources – either

USE [master]
GO
ALTER DATABASE [Resources] SET CONTAINMENT = PARTIAL
GO

…or set containment option to Partial on the Database Properties\Options page:P2-58-ContainDB

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:

Use Resources
GO
CREATE USER [TEST\User1]
GO

P2-59-NewUSER

…and set appropriate permissions for the database:

P3-USER-Owner

Summary:

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.

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: