Microsoft SQL Server – AlwaysOn SQL Cluster – Setup

Welcome to my Microsoft Tips & Tricks section. In this session I will describe how I setup an Always On SQL Cluster for my Horizon SQL Databases.

From time to time, my customers throw me a curveball. This time the question was, how can we ensure High Availability for App Volumes?

Well, first of all, setting up two App Volumes Manager is a nice start. Make sure to set up an anti-affinity rules in DRS to ensure that these servers are newer on same esxi-host. Next, load balance the connections from the App Volumes agent inside the VDI’s to the App Volumes managers, typically done through a loadbalancer, F5, Nextscaler etc. Finally, cluster the database that App Volumes uses. This last task is what this session will cover.

As I’m in no sense a DBA or SQL-expert, I needed to do some googling before setting up this. I have added a couple of links under recommended reading at the bottom to the sites I used to accomplish this task.

I have divided this session into the following tasks

  • Create two vm’s, scale vcpu, memory, storage according to need.
  • Install server os and join to domain. Configure partitions.
  • Create admin user, group and service-accounts
  • Install required Windows Features
  • Create Failover Cluster
    • Adjust AD-permission for SQL-Cluster Account
  • Install SQL Server
    • Adjust Windows Firewall settings
  • Configure SQL Server
  • Install SQL Server Management Studio
  • Give sysadmin-permission to service-accounts on the SQL Servers
  • Adjust SQL Server memory options (Usage)

 

Create two vm’s, scale vcpu, memory, storage according to need

I wont go into the detail about provisioning the necessary VM’s, giving the servers ip addresses and joining to the domain, as I believe my audience are proficient in accomplishing this. I have attached a screenshot of the Virtual Hardware I setup for my lab SQL-servers below.

SQL-Cluster-01

 

In Disk Management I partition my disk as follows. As we can see, this is not very large partitions, but my Horizon databases wont need a very large disks either. Also, this way of partitioning for SQL Server, isn’t best practice, I know. In a production environment I would follow VMware’s best practice guide for settings up SQL Servers in vSphere.

SQL-Cluster-04

 

Create admin user, group and service-accounts

I will create a separate SQL Admin user that I will use when installing SQL Server. In a production setup, I would probably set up a SQL Admins Group for these SQL-servers, and give this group the Admin permission during installation. The SQL Admin account is a normal Domain User, but I add the account to the Administrator group locally on both servers. This could also be done by using the Restricted Groups setting in a GPO.

SQL-Cluster-02

SQL-Cluster-03

 

I will also create separate service-accounts for each server and group these in a group that is given sysadmin-permissions in SQL, below. I name the service-accounts: svc_view-sql01 & svc_view-sql02.

SVC-Accounts-01

SVC-Accounts-02

SVC-Accounts-03

 

I add the service-accounts to my user group “View Sql Service Accounts”

SVC-Accounts-04

SVC-Accounts-05

 

Install required Windows Features

I will need to add both .Net 3.5 and the Failover Clustering feature on both servers, from Server Manager I add these features first.

SQL-Cluster-05

SQL-Cluster-06

SQL-Cluster-07

SQL-Cluster-08

 

Create Failover Cluster

In Failover Cluster Manager, I click “Validate Configuration”

SQL-Cluster-09

 

This opens the “Validate a Configuration” wizard, Next

SQL-Cluster-10

 

I add my SQL-servers, Next

SQL-Cluster-11

 

I select “Run All tests”, Next

SQL-Cluster-12

 

I confirm my settings, Next

SQL-Cluster-13

 

In the Summary, I click “View Report”

SQL-Cluster-14

 

I review the warnings from the report, no critical “show-stoppers”

SQL-Cluster-15

 

Next, I select “Create the cluster now using the validated nodes”, Finish.

SQL-Cluster-16

 

This will launch the “Create Cluster” wizard, Next

SQL-Cluster-17

 

Prior to starting the configuration of the cluster I created a dns-host entry for my new cluster

SQL-Cluster-18

 

I enter the Cluster Name and address that I created for it in DNS above, Next

SQL-Cluster-19

 

I review and confirm the settings, I wont add any storage at this point, Next

SQL-Cluster-20

 

I review the report, Finish

SQL-Cluster-21

 

After the cluster is created I open Failover Cluster Manager and review functionality

SQL-Cluster-22

 

Install SQL Server

As I now have my cluster up and running, I can now start the SQL server installations. This setup will be the same on both servers. I start the setup from my ISO and select Installation from the menu on the left

SQL-Cluster-23

 

I select “New SQL Server stand-alone installation…..”

SQL-Cluster-24

 

I accept terms, Next

SQL-Cluster-25

 

I select “Use Microsoft update…..”, Next

SQL-Cluster-26

 

No updates available, Next

SQL-Cluster-27

 

The Install Rules reports warning on Windows firewall rules. I resolve this issue by running the following command in an administrative cmd-promt: ” netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN”. I re-run the test, all green, Next

SQL-Cluster-28

SQL-Cluster-29

 

I will only need Database Engine Services, the installation paths are ok, Next

SQL-Cluster-30

 

Using a named instance was, in hindsight, an error. This caused errors when I later set up the Availability groups. For future reference I should have used Default Instance, Next

SQL-Cluster-31

 

I add the service-account I created to begin with, Next

SQL-Cluster-32

 

I select Mixed Mode and enter a password. In my lab, I choose to use my SQL Admin user as SQL Server Administrator. In a production environments, this would be a user group with SQL Admins.

SQL-Cluster-33

 

I customize the Data directories to match my configured partitions, Next. Using the instance’s name in the paths, caused errors when setting up Availability groups, this would have been avoided if I for instance used “D:\Microsoft SQL Server\MSSQL\Data” as User database directory path.

SQL-Cluster-34

 

I verify the settings I’ve made, Install

SQL-Cluster-35

 

Complete set up, Close

SQL-Cluster-36

 

Configure SQL Server

I start SQL Server Configuration Manager and verify the TCP/IP is Enabled

SQL-Cluster-40

 

Next I verify 1433 as port settings in TCP/IP

SQL-Cluster-41

 

I also verify connectivity between my SQL servers on port 1433 with telnet

SQL-Cluster-42

 

When all tests are OK I select “Enable AlwaysOn Availability Groups” in properties on the SQL Server

SQL-Cluster-43

 

Finally I verify that my service accounts has “Log on as a service” permissions after SQL Server installation, using gpedit.msc

SQL-Cluster-44

 

Install SQL Server Management Studio

Now that the SQL Server is installed and configured, I will proceed with installing SQL Server Management Studio. Back in SQL Server Installation Center, I click “Install SQL Server Management Tools”, which takes me to a web-page where I can download the Management Studio installation media.

SQL-Cluster-37

 

Once the file is downloaded I launch the installer and click Install

SQL-Cluster-38

 

After the installer finishes, I click restart

SQL-Cluster-39

 

Give sysadmin-permission to service-accounts on the SQL Servers

As this is going to be two clustered instances, they will need to be able to talk together. In order to get this working, the service accounts will need to be sysadmins on the SQL Servers. To achieve this, I will use the user group I created at the beginning of this session.

 

I will give this user group sysadmin permissions by creating a new login for the user group on each server in SQL Server Management Studio.

SQL-Cluster-46

 

I Search to find my user group

SQL-Cluster-47

 

I click Object Types to add groups to my query and choose Entire Directory

SQL-Cluster-48

SQL-Cluster-49

 

I can now enter my group’s name and click Check Name

SQL-Cluster-50

SQL-Cluster-51

SQL-Cluster-52

 

In Server Roles I assign my user group the sysadmin role

SQL-Cluster-53

 

Adjust SQL Server memory options (Usage)

As this is a Lab setup, I will adjust the memory usage for the SQL servers as follows.

SVC-Accounts-06

 

Now that I have my Always On cluster in place, I can migrate my App Volumes Database to my new cluster, covered here: VMware App Volumes – Migrate database to AlwaysOn SQL Cluster

 

Recommended reading:

Brent Ozar’s excellent post: How to Set Up SQL Server 2012 Always On Availability Groups

 

Microsoft Tips & Tricks section

Disclaimer: Every tips/tricks/posting I have published here, is tried and tested in different it-solutions. It is not guaranteed to work everywhere, but is meant as a tip for other users out there. Remember, Google is your friend and don’t be afraid to steal with pride! Feel free to comment below as needed.