How-To
Connecting View Composer to a Remote Database
Tom Fenton shows you two ways to overcome the gotchas when setting up an ODBC connection to a remote database.
This article is a follow-up to one I wrote in August 2017, "How To Set Up a SQL Express Database for Horizon View," in which I detailed how to configure a SQL Server Express database for use with View Composer, with the database and View Composer residing on the same system. A database is required with View Composer as it holds information regarding the linked clones that View Composer creates.
Recently, I had to configure a SQL Server Express database that resided on a remote server. While the process of connecting to the remote database was fairly simple, I did encounter a few issues that weren't documented very well. In this article, I outline how I configured View Composer to work with a remote SQL Server Express database, plus detail some of the issues (of which you should be aware) that there were encountered along the way and how I overcame them.
Setup
If you're unfamiliar with the basics of setting up a SQL Server Express database, I suggest that you start by reading the article I mentioned earlier, as this current article assumes that you've already installed a SQL Express database and created a database for View Composer.
The setup that I used had a View Connection server (sa-ConnServer), a View Composer server (sa-CompServer) and a virtual machine (VM) hosting the database (AppVolMgr). Figure 1 shows that relationship and the IP addresses of these three VMs.
[Click on image for larger view.]
Figure 1. The relationship of the three virtual machines in my initial setup.
Testing Connections to a Local Database Server
The first thing that I did was verify the name of the SQL server and database by going to the server that was hosting the database and logging on to the SQL Server Management Studio (SSMS). From the SSMS, I right-clicked the View Composer database and selected Properties, and then clicked View connection properties. From the properties page, I verified the names of the database (ViewComposerDB), the authentication method (Windows Authentication) and the server (APPLOVMGR\SQLEXPRESS), see Figure 2.
[Click on image for larger view.]
Figure 2. The SQL Server Management Studio interface.
After gathering the pertinent information about my database, I verified that I could connect to the database from the local system by bringing up the setup wizard for a 64-bit ODBC data source (Figure 3), selecting System DSN, Add and then SQL Server Native Client 11.0 (Figure 4).
[Click on image for larger view.]
Figure 3. Setting up the ODBC data source.
[Click on image for larger view.]
Figure 4. The new ODBC data source.
From the ODBC wizard, I entered LocalTest for the data source, as well as the name of the SQL server (AppVolMgr\SQLEXPRESS). I clicked Next, selected SQL Server authentication, entered the username sa and clicked Next. I then selected the ViewComposerDB from the dropdown menu (Figure 5), selected Next and then Finish.
[Click on image for larger view.]
Figure 5. The default database.
When I clicked
Test Data Source, I received a message that the test was successful (
Figure 6).
[Click on image for larger view.]
Figure 6. A successful test.
Testing Connections from a Remote Database Server
Once I verified that I could connect to the database locally, I logged in to my View Composer server system and brought up the 64-bit ODBC data source setup wizard and configured it similar to the local connection. However, when I tried to select ViewComposerDB from the dropdown menu, I received an error indicating that the connection failed (Figure 7).
[Click on image for larger view.]
Figure 7. Connection failed when trying to select ViewComposerDB.
To debug the issue, I went back to the database server to see what port the database was using by entering netstat -a -b from an administrator command prompt. Upon examining the output, I noticed that the SQL was listening in on port 59821 (Figure 8), not the standard SQL port of 1433.
[Click on image for larger view.]
Figure 8. The SQL Server port.
To correct this issue, I brought up the SQL Server Configuration Manager and looked at the TCP port for the SQL server; it wasn't the standard 1433 port, but had a dynamically assigned port number (Figure 9). I clicked the dynamic port number and changed it to the standard port number of 1433 and clicked Apply, and then used the SSMS to restart the database (Figure 10).
[Click on image for larger view.]
Figure 9. The port number.
[Click on image for larger view.]
Figure 10. Restarting the database.
I then went back to my View Composer VM and tried to select the database from the dropdown menu again, but it failed with the same error message that I received before. I went back and changed the name of the SQL server from AppVolMgr\SQLEXPRESS to AppVolMgr (Figure 11); after doing so, I was able to select the View Composer database and successfully set up an ODBC data source to the remote SQL Express database (Figure 12).
[Click on image for larger view.]
Figure 11. Changing the name of the SQL Server.
[Click on image for larger view.]
Figure 12. Success connecting to a remote database.
Another way that I could have corrected this issue would have been to input a comma after the server and append the port number that was being used when I configured the ODBC connector (Figure 13). In the end, however, I've found that changing the database port number to the standard port leads to less confusion.
[Click on image for larger view.]
Figure 13. Appending the port number.
Wrapping Up
In this article, I showed two ways in which I was able to overcome the gotchas I encountered while trying to set up an ODBC connection to a remote database. These fixes were simple; however, I couldn't find them documented anywhere and you should be aware of them when setting up your own connection to a remote database for your Horizon environment.
About the Author
Tom Fenton has a wealth of hands-on IT experience gained over the past 30 years in a variety of technologies, with the past 20 years focusing on virtualization and storage. He previously worked as a Technical Marketing Manager for ControlUp. He also previously worked at VMware in Staff and Senior level positions. He has also worked as a Senior Validation Engineer with The Taneja Group, where he headed the Validation Service Lab and was instrumental in starting up its vSphere Virtual Volumes practice. He's on X @vDoppler.