Ranga Babu
SQL Server cluster setup

How to configure SAN less SQL Server clusters in AWS EC2 using SIOS DataKeeper

July 30, 2019 by

In this article, we will review how to configure SAN less SQL Server cluster in AWS EC2 instances using SIOS DataKeeper.

SQL Server clustering is a high availability feature which failover the SQL Server resources to the secondary node in case of a failure in the primary node. The data is stored in the shared drive and will be accessible to the active node in the cluster. In case of failover, we do not need to transfer jobs, logins, linked server or server scoped triggers, etc. to the other node as the instance is the same.

Please refer to the below image which shows the traditional SQL Server failover cluster using shared disks. The shared disks are active on the NODE1 which is active, and the SQL server services are running on NODE1. The users connect to the instance using a virtual SQL Server cluster name or IP address which routes the request to the active node in the cluster.

traditional SQL Server cluster

SQL Server clustering using SIOS DataKeeper

SIOS DataKeeper allows us to build the SQL Server cluster on cloud environments without the need for shared storage.

The data in the disk on the primary node is mirrored to the secondary node. Please refer to the below image for SAN less SQL Server cluster using SIOS DataKeeper.

san less SQL Server cluster

NODE1 will have its disk and NODE2 will have its disk. SIOS DataKeeper will replicate the data to the other node synchronously or asynchronously based on the option we choose.

The following are the steps involved in configuring the SAN less SQL Server cluster in AWS EC2 instances.

  1. Launch two instances and add them to one domain
  2. Configure windows failover cluster
  3. Attach a volume to both instances
  4. Configure SIOS jobs to replicate the data
  5. Install the SQL Server failover cluster

Let go over these steps one by one.

For demo purpose, I am launching a Windows server 2016 base from AWS console to installing active directory services and make it as a domain controller. For production servers please configure domain controller with all the security and firewall rules you need.

Log in to the AWS and navigate to the EC2 console. Click on Launch an instance and search for Windows Server 2016 base image. Select the image as shown in the below image.

SQL Server cluster setup

Choose the Instance type. In this case, I choose t2.medium Configure the instance details and add the storage. Choose the security group if you already have one or create a new one. While launching the instance choose the key pair and make sure you have the key pair which will be used to connect the instance later.

key pair to launch domain controller

After four or five minutes the instance will be available for use. For identification purposes, I am naming it as “Domain Controller”.

domain controller

Now select the instance and click on Connect.

domain controller

Click on Get Password. upload the .pem which was used to launch the instance. Click on decrypt the password.

RDP the instance using the public IP and the password of the instance. Launch Server Manager and click on Add roles and features.

SQL Server cluster setup

Click Next and select roles based or feature-based installation and click Next. Select a server from the pool and click Next.

add active directory services

Select Active Directory domain services and click Next, Next and Install.

add active directory services

Once the installation is completed, close the window and click on Promote this server as a domain controller.

promote this server as domain controller

Select Add a new forest and enter the domain name. Click Next.

add forest

Enter the password and click Next.

add forest

Click Next. Enter the NetBIOS name and click Next. Specify the paths for the AD database and logs.

AD DS database and log files

Once the prerequisites check is successful, click on Install. Once the installation completes the server will reboot.

After reboot, RDP to the server again. Launch Active Directory Users and Computers. Create a new user and add it Administrators and Domain Controllers, Domain Computers, Domain Administrators group.

domain controller user

Launch two instances and add them to one domain

In this demo, I am setting up a two-node SQL Server cluster. So, I am launching two new nodes and join them to the domain we created above. These two nodes will be part of the cluster.

There are two ways to launch the instances. One is launching the instances using EC2 console and install SIOS DataKeeper with your license. The other way is subscribing to SIOS DataKeeper in the AWS Market place and launch the instance from there. This way EC2 instance comes with SIOS pre-installed.

In this demo, we will use a subscription model.

To subscribe for SIOS data keeper, Navigate to AWS Market Place subscriptions. Click on Discover products.

san less SQL Server cluster setup

Search for SIOS DataKeeper and click on SIOS DataKeeper on Windows Server 2016.

san less SQL Server cluster setup

Click on Continue to Subscribe and Accept Terms.

Once the subscription is successful, click on Continue to Configuration.

SIOS DataKeeper subscription

Select the region, software version and click on Continue to Launch.

SIOS DataKeeper subscription

Choose Launch through EC2 and click on Launch.

SIOS DataKeeper subscription

Select the instance type. In this case, I choose m5d.large and click on Configure Instance Details.

SIOS DataKeeper subscription instances

Enter the number of instances as 2, select the subnet and click on Add Storage. You can also launch instances individually in multiple availability zones.

SIOS DataKeeper subscription

Add storage, tags, select the security groups and launch the instances.

RDP to the instances we launched, Navigate to Control Panel -> Network and Internet -> Network and sharing center.

Click on the network adaptor -> Properties -> double click on Internet Protocol Version 4.

san less SQL Server cluster setup

IPV4 properties

IPV4 Properties

Open the command prompt and enter ipconfig to know the IP address of the node and modify the ipv4 properties as shown in the below image. Select use the following IP address and enter the same IP address, subnet mask, default gateway of the node. Enter the private IP of the domain controller in the Preferred DNS server.

IPV4 properties to join node to domain

Click ok and apply the changes. Now join the node to the domain.

join node to domain

Similarly, on node2 change the IP address, subnet mask, default gateway, and the preferred DNS server.

Join the node2 to the domain. After joining the node, it restarts.

RDP to node1 and launch failover cluster manager. Add both nodes which will be part of the cluster and click Next.

san less SQL Server cluster setup

Enter the name of the cluster and give it an IP address.

Cluster setup

Uncheck the Add all the eligible storage to the cluster and click Next. We can add storage later using SIOS DataKeeper.

cluster setup

Once the cluster setup is done you would see both nodes in the failover cluster manager.

nodes in failover cluster

Attach a volume to both the instances

We need to create two volumes and associate one to each node. Navigate to AWS EC2 console. Click on volumes and Create Volume.

add volumes to the nodes

Select the created volume and click on Actions dropdown. Click on attach volume and associate it to node1.

add volumes to the nodes

Similarly, create another volume and attach it to the node2 which will be used in the SQL Server cluster.

RDP to node1 and open disk management. The newly attached volume shows up as below. Click ok.

add volume to the nodes

Right-click on the disk and click on New Simple Volume.

New Simple Volume

Assign the letter to drive and Click Next.

New Simple Volume

Format and assign the label. Click Next.

New Simple Volume

Similarly, on node2 make a volume and give the same label and drive letter.

Configure SIOS DataKeeper jobs to replicate the data

On node1 launch the SIOS DataKeeper. Click on Connect to server.

SIOS DataKeeper source and target connection

Enter node1 and click on Connect.

SIOS DataKeeper source and target connection

Similarly, Connect to node2. Once both nodes are connected, click on create the job. Enter the job name and the description.

san less SQL Server cluster setup

Choose the source and the drive you want to mirror. Click Next.

SIOS DataKeeper source and target connection

Choose the target server and the drive. Click Next.

SIOS DataKeeper source and target connection

Choose the synchronization mode and click on Done.

synchronization mode

Once the mirroring is created, it asks if you want to add the volume to the failover cluster. Click Yes.

add storage to failover cluster

Now the disk will be shown in the failover cluster manager. We can see node1 is the owner node of the disk.

If you want to add more disks you need to create jobs for them and add the volume to the failover cluster.

DataKeeper volume in failover cluster

On node2, the disk is locked.

clustered disk on secondary node

Now we have windows failover cluster and the cluster storage needed so that we can proceed and install the SQL Server cluster instance.

Install SQL Server failover cluster instance

Login to the node1 and locate the setup files to install the SQL Server failover cluster.

Please refer to the below articles for step by step installation of the SQL Server failover cluster.

SQL Server 2014 – Install a clustered instance – step-by-step (1/3)

SQL Server 2014 – Install a clustered instance – step-by-step (2/3)

SQL Server 2014 – Install a clustered instance – step-by-step (3/3)

Conclusion

In this article, we explored to launch instances with SIOS subscription and created mirrored drives using SIOS which will be used as a clustered storage. In case you have any questions, please feel free to ask in the comment section below.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
Clustering

About Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies

168 Views