Release date: April 11th 2019
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 curve-ball. 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, vmemory, 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.
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.
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.
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.
I add the service-accounts to my user group “View Sql Service Accounts”
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.
Create Failover Cluster
In “Failover Cluster Manager“, I click “Validate Configuration“
This opens the “Validate a Configuration” wizard, Next…
I add my SQL-servers, Next…
I select “Run All tests”, Next…
I confirm my settings, Next…
In the Summary, I click “View Report“
I review the warnings from the report, no critical “show-stoppers”
Next, I select “Create the cluster now using the validated nodes”, Finish…
This will launch the “Create Cluster” wizard, Next…
Prior to starting the configuration of the cluster I created a dns-host entry for my new cluster
I enter the Cluster Name and address that I created for it in DNS above, Next…
I review and confirm the settings, I wont add any storage at this point, Next…
I review the report, Finish…
After the cluster is created I open “Failover Cluster Manager” and review functionality
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
I select “New SQL Server stand-alone installation…..“
I accept the License Terms, Next…
I select “Use Microsoft update…..”, Next…
No updates available, Next…
The Install Rules reports warning on Windows firewall rules. I resolve this issue by running the following command in an administrative command-prompt:
netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAINOnce done, I re-run the test, all green, Next…
I will only need the Database Engine Services, the installation paths are ok, Next…
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…
I add the service-account I created to begin with, Next…
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.
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.
I verify the settings I’ve made, Install…
Complete set up, Close…
Configure SQL Server
I start SQL Server Configuration Manager and verify the TCP/IP is “Enabled“
Next I verify 1433 as port settings in TCP/IP
I also verify connectivity between my SQL servers on port 1433 with telnet
When all tests are OK I select “Enable AlwaysOn Availability Groups” in properties on the SQL Server
Finally I verify that my service accounts has “Log on as a service” permissions after SQL Server installation, using “gpedit.msc“
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.
Once the file is downloaded I launch the installer and click Install…
After the installer finishes, I click Restart…
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.
I Search to find my user group
I click Object Types to add groups to my query and choose “Entire Directory“
I can now enter my group’s name and click “Check Name“
In “Server Roles” I assign my user group the “sysadmin” role
Adjust SQL Server memory options (Usage)
As this is a Lab setup, I will adjust the memory usage for the SQL servers as follows.
Finally, I make sure to replicate sql user accounts to my secondary sql-server, as described by Microsoft here: How to transfer logins and passwords between instances of SQL Server
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.
bjosoren's IT-Tech blog 