VMware Horizon – Move ComposerDB to AlwaysOn SQL Cluster

Release date: May 25th 2019

Welcome to my VMware Horizon series. In this session I will describe how I moved the Horizon Composer database to my AlwaysON SQL Cluster. I wont backup the old database and move it to the new SQL Cluster, as this is a lab-setup. Instead I will setup a new database and reinstall Horizon Composer

This exercise will involve the following tasks:

  • Disable provisioning in Horizon Administrator
  • Uninstall Vmware Horizon Composer
  • Create new Composer database
  • Create Login
  • Create Availability Group and Listener
  • Edit ODBC-settings on composer server
  • Install Composer
  • Update Composer settings in Horizon Administrator
  • Verify functionality and test failover

 

Disable provisioning in Horizon Administrator

In Horizon Administrator – View Configuration – Servers – vCenter Servers, Disable Provisioning

MoveComposerDB2SqlCluster-01

 

Uninstall Vmware Horizon Composer

Log into Composer server desktop, uninstall VMware Horizon 7 Composer from the control panel

MoveComposerDB2SqlCluster-02

 

Create new Composer 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 it will be the Composer-database, I’ll call it ComposerDB

MoveComposerDB2SqlCluster-04

MoveComposerDB2SqlCluster-05

MoveComposerDB2SqlCluster-06

 

Create Login

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

MigrateDB2SqlCluster-07

 

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

MoveComposerDB2SqlCluster-08

 

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

MoveComposerDB2SqlCluster-09

 

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

MoveComposerDB2SqlCluster-10

 

Create Availability Group and Listener

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

MoveComposerDB2SqlCluster-11

 

Default settings will suffice, as the database is empty

MoveComposerDB2SqlCluster-12

MoveComposerDB2SqlCluster-13

 

Right click Availability Groups, select New Availability Group Wizard

MigrateDB2SqlCluster-14

 

I give the availability group a somewhat meaningful name, Next

MoveComposerDB2SqlCluster-15

 

Select the ComposerDB database, Next

MoveComposerDB2SqlCluster-16

 

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

MoveComposerDB2SqlCluster-17

 

MoveComposerDB2SqlCluster-18

MoveComposerDB2SqlCluster-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

MoveComposerDB2SqlCluster-20

 

Automatic seeding looks good, Next

MoveComposerDB2SqlCluster-21

 

Validation looks green, great! Next.

MoveComposerDB2SqlCluster-22

 

Finish

MoveComposerDB2SqlCluster-23

 

Nice, successfully created the availability group, Close

MoveComposerDB2SqlCluster-24

 

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

MoveComposerDB2SqlCluster-25

 

Edit ODBC-settings on composer server

I choose my DSN, ViewComposer, Configure

MoveComposerDB2SqlCluster-26

 

I enter my listener-address, Next

MoveComposerDB2SqlCluster-27

 

Enter SQL user-info, Next

MoveComposerDB2SqlCluster-28

 

I check and modify the default database setting, Next

MoveComposerDB2SqlCluster-29

 

Finish

MoveComposerDB2SqlCluster-30

 

I test the new ODBC settings

MoveComposerDB2SqlCluster-31

 

Successful change, OK

MoveComposerDB2SqlCluster-32

MoveComposerDB2SqlCluster-33

 

Reinstall Composer

MoveComposerDB2SqlCluster-34

VMware Horizon 7 Composer installation wizard launches, Next

MoveComposerDB2SqlCluster-35

 

Accept the terms, Next

MoveComposerDB2SqlCluster-36

 

Default destination folder is a good a location as any, Next…

MoveComposerDB2SqlCluster-37

 

I enter the ODBC-settings I defined during the ODBC-config above, Next…

MoveComposerDB2SqlCluster-38

 

I will use the default SOAP-port, Next…

MoveComposerDB2SqlCluster-39

 

Install…

MoveComposerDB2SqlCluster-40

 

When the installer completes, Finish

MoveComposerDB2SqlCluster-41

 

After the installer finish, I click Yes to restart the server.

MoveComposerDB2SqlCluster-42

 

Verify Horizon Composer service status after reboot

MoveComposerDB2SqlCluster-43

 

Update Composer settings in Horizon Administrator

In Horizon Administrator, I verify that the Composer has an error, most likely due to new self signed certificate after I reinstalled the composer. I click my composer-URL

MoveComposerDB2SqlCluster-45

 

I click “Verify” to check the new untrusted certificate

MoveComposerDB2SqlCluster-46

 

Click “View Certificate”

MoveComposerDB2SqlCluster-47

 

I click “Accept”

MoveComposerDB2SqlCluster-48

 

This generates this benign error, OK

MoveComposerDB2SqlCluster-49

 

The status now changes to “No problem detected”

MoveComposerDB2SqlCluster-50

 

The Dashboard is now without Composer-problems

MoveComposerDB2SqlCluster-51

 

Finally, I re-enable provisioning in Horizon Administrator

MoveComposerDB2SqlCluster-44

 

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

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

MoveComposerDB2SqlCluster-52

 

To test failover for my ComposerDB, I click “Failover” on my Sql-ag-compdb Availability Group

MoveComposerDB2SqlCluster-53

 

The Failover wizard starts, Next. Select new primary, Next

MoveComposerDB2SqlCluster-54

 

Finish

MoveComposerDB2SqlCluster-55

 

Everything looks successful and green, Close

MoveComposerDB2SqlCluster-56

 

The availability group is now Primary

MoveComposerDB2SqlCluster-57

 

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

MoveComposerDB2SqlCluster-58

 

Next I log in to my composer server, restarts Composer service and checks Event logs.

 

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.