VMware Horizon – Move EventsDB to AlwaysOn Sql Cluster

Release date: April 26th 2019

Welcome to my VMware Horizon series. In this session I will describe how I moved the Horizon View Connection Server’s Events database to my new Always-On SQL Cluster. I wont be backup the old database and move it to the new SQL Cluster, as this is a lab-setup.

This exercise will involve the following tasks:

1. Review existing settings
2. Create new Events database
3. Create Login
4. Create Availability Group and Listener
5. Change Events database settings in Horizon Administrator
6. Verify functionality and test failover

Review existing settings

To review my existing Event Database setup, I log into Horizon Administrator and go to View Configuration – Event Configuration. In the Events Database section I click Edit…

I can now see that my existing database is running on my old database-server DB-01. If I were to back up the old database, I would log into DB-01 and do that there.

Create new Events database

Open SQL Server Management Studio on one of the nodes in SQL cluster, New Database…

Give the database a name, in this case this will be the Events-database for the Connection-server, so I’ll call it EventsDB

Create Login

To setup SQL permissions for the EventsDB database, right-click Logins – New Login…

Enter a new Login name, EventsDB-adm, deselect “Enforce password policy” and set EventsDB as Default database

The Server Role “Public” will be sufficient for my new login

I map my user to my newly created EventsDB database and give the user db_owner membership for this database.

Create Availability Group and Listener

Before we can create an availability group, taking a backup is a prerequisite.

Default settings will suffice, as the database is empty

Right click Availability Groups, select New Availability Group Wizard…

I give the availability group a somewhat meaningful name, Next…

Select the EventsDB database, Next…

I add my replica and check “Automatic Failover”, click the Listener tab…

I enter a Listener DNS Name and Port. I click Add to enter a static ip-address. This will automatically be added to DNS, Next…

Automatic seeding looks good, Next…

Validation looks green, great! Next…

Finish…

Nice, successfully created the availability group, Close…

Change Events database settings in Horizon Administrator

I log in to Horizon Administrator again and change the Event Database settings to my new listener address, database and username/password

As I can now see in Events in Horizon Administrator, events-logging is now operational with the new AlwaysOn SQL Cluster.

Verify functionality and test failover

Prior to testing failover, 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

I verify that I can make a connection on port 1433 to my new listener

In SQL Management Studio, I verify that my new database is synchronized.

To test failover for my EventsDB I click Failover on my SQL-ag-eventsdb Availability Group

The Failover wizard starts, Next…

Select new primary, Next…

Click Connect and select secondary replica, Next…

Finish…

Everything looks successful and green, Close…

The availability group is now Secondary

I re-test the connection to make sure I have database connectivity after failing over to my secondary replica, all good!

I finally take my old EventsDB offline on my old SQL Server

VMware Horizon Product Page

VMware Horizon planning, deployment etc.

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.