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”

MigrateDB2SqlCluster-01

 

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.

MigrateDB2SqlCluster-02

 

Create new Events database

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

MigrateDB2SqlCluster-03

 

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

MigrateDB2SqlCluster-04

MigrateDB2SqlCluster-05

MigrateDB2SqlCluster-06

 

Create Login

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

MigrateDB2SqlCluster-07

 

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

MigrateDB2SqlCluster-08

 

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

MigrateDB2SqlCluster-09

 

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

MigrateDB2SqlCluster-10

 

Create Availability Group and Listener

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

MigrateDB2SqlCluster-11.PNG

 

Default settings will suffice, as the database is empty

MigrateDB2SqlCluster-12

 

MigrateDB2SqlCluster-13

 

Right click Availability Groups, select New Availability Group Wizard

MigrateDB2SqlCluster-14

 

I give the availability group a somewhat meaningful name, Next

MigrateDB2SqlCluster-15

 

Select the EventsDB database, Next

MigrateDB2SqlCluster-16

 

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

MigrateDB2SqlCluster-17

 

MigrateDB2SqlCluster-18

 

MigrateDB2SqlCluster-19

 

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

MigrateDB2SqlCluster-20

 

Automatic seeding looks good, Next

MigrateDB2SqlCluster-21

 

Validation looks green, great! Next.

MigrateDB2SqlCluster-22

 

Finish

MigrateDB2SqlCluster-23

 

Nice, successfully created the availability group, Close

MigrateDB2SqlCluster-24

 

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

MigrateDB2SqlCluster-25

 

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

MigrateDB2SqlCluster-26

 

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

MigrateDB2SqlCluster-31

 

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

MigrateDB2SqlCluster-27

 

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

MigrateDB2SqlCluster-32

 

The Failover wizard starts, Next

MigrateDB2SqlCluster-33

 

Select new primary, Next

MigrateDB2SqlCluster-34

 

Click Connect and select secondary replica, Next

MigrateDB2SqlCluster-35

MigrateDB2SqlCluster-36

MigrateDB2SqlCluster-37

 

Finish

MigrateDB2SqlCluster-38

 

Everything looks successful and green, Close

MigrateDB2SqlCluster-39

 

The availability group is now Secondary

MigrateDB2SqlCluster-40

 

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

MigrateDB2SqlCluster-41

 

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

MigrateDB2SqlCluster-28

 

MigrateDB2SqlCluster-29

 

MigrateDB2SqlCluster-30

 

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.