This page starts a new series of articles on building highly available SQL Server 2014 solution using guest clustering and AlwaysON availability group. For this article I’ll focus on building a physical cluster using a PC-based iSCSI target and two host machines; in Part 2 I’ll create two virtual machines – SQL1 and SQL2, install SQL Server Datacenter to both of them, create a guest cluster and make these virtual machines highly available. Finally, in Part3 I will enable AlwayOn on SQL1 and SQL2 and add one of my test databases to the AlwaysON availabilty group.
The type of the test database I’m going to add to the AlwaysOn group deserves special mention: prior to making a database highly available I prefer to convert it to the contained database – the type of database available since SQL Server 2012.
I think contained databases is one of the most important features in the latest releases of SQL Server . Why? Because it simplifies the complexity of managing SQL Server databases greatly: it introduces new boundaries by allowing to log into a database without the need of having a corresponding server login.
Before SQL Server 2012 all database administrators had to manage permissions at two separate levels: Server instance level (logins) and Database level (db users). Things get much more complicated when it comes to building highly available database solutions. The more SQL servers exist that can potentially host a database due to a failover or s switchover, the more confusing the process of sychronizing server instance logins across all secondary servers can become. Contained databases allow us to get rid of any server instance dependencies, such as server logins, settings and metadata.
After a database is converted to a contained database it can be made active (e.g. primary) on any server in a cluster with no extra work on sychronizing the db users with the new SQL Server instance logins.
Here’s the schematic of what I’m going to build:
I start creating a physical cluster by using three computers named Host1, Host2 and Host3 (all with Windows Server 2012R2 installed): Host1 and Host2 will be the cluster nodes and Host3 will be the iSCSI target. Given that I need to create two VMs for SQL1 and SQL2 I will create three iscsi disks: one for each vm and the third for the cluster quorum resource. All these disks will reside on Host3\M$.
Once the role has been installed I can proceed to creating iscsi disks:
Now I can attach these iSCSI disks to Host1 and Host2 by installing iSCSI Initiator on Host1 and Host2:
Now in Computer Management\Disk Management I’ll make all newly added disks online
and then initialize them:
On Host2 I must make the same disks online ( not initialize!) again:
The last step in configuring iSCSI disks is to create volumes:
Now it’s possible to start creating a cluster. First I need to install Failover Clustering feature on Host1 and Host2 (both cluster nodes must be members of a domain) and then proceed to creating a new cluster, for example, on Host1:
10.3.0.0/22 – management/cluster heartbeat network (of course in a production environment it MUST be different networks but I just don’t have enough network cards on my test machines).
10.1.1.0/24 – iSCSI network
Here’s my hosts’s network connection settings:
Note: HV network adapters will be used in Hyper-V for virtual machines SQL1 and SQL2.
And the last step in configuring the physical cluster: I add Cluster Disk1 and Cluster Disk 2 (the disks to be used for installing SQL1 and SQL2) to Cluster Shared Storage:
In Part1 of the series of articles on deploying AlwaysON using SQL Server 2014 we created a physical cluster and got ready for creating virtual machines SQL1 and SQL2 – please see Part2 for a step by step instructions on deploying SQL Server 2014 inside these virtual machines.