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:
- Review existing settings
- Create new Events database
- Create Login
- Create Availability Group and Listener
- Change Events database settings in Horizon Administrator
- 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
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.
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
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
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.