Storing the EdgeSight DB on Windows Azure

Lately, I have been working on a lot of new reports for EdgeSight along my previous posts about building custom reports. This involves writing SQL query against the EdgeSight database and incorporating EdgeSight parameters to react to user input. Recently I have become annoyed by starting my virtual machine for these jobs and thought that I would be really neat to have an EdgeSight database available at all times. So I decided to migrate my EdgeSight database to the cloud.

Instead of comparing the database plans of different vendors, I felt like taking a closer look at Windows Azure. Microsoft offers a free trial of Windows Azure as well as benefits for MSDN members. The latter provides me with SQL Azure Web Edition with 1GB of space - more than enough for an EdgeSight database for a couple of endpoints.

Building the SQL Azure Connection String

In general, the SQL Azure connection string is not very different from those for a local SQL Server. The following connection string has worked as a template in my case:

Server=tcp:[SERVERNAME].database.windows.net;Database=DATABASE;User ID=[USERNAME]@[HOSTNAME];Password=PASSWORD;Trusted_Connection=False;Encrypt=True;

The construct the final connection string you require the following:

To make your job easier, you can also retrieve the connection string directly from the SQL Azure management console.

Method 1: Installing EdgeSight to SQL Azure

My first attempt was to install a new EdgeSight database to SQL Azure. Unfortunately, this fails due to insufficient rights when the installer attempts to create filegroups. The EdgeSight installer expects sysadmin permissions on the SQL server to create filegroups, the database and modify permissions. This is something I have previously ranted about because some customers do not grant those rights due to the possible impact on other databases on the same server. This would be a lot easier if the installer offered to generate scripts for creating the database.

Apparently you need to install EdgeSight to a local database and migrate the database to another server. The step for this process are described in CTX118977. But before you start off configuring a new database you need to migrate the database to SQL Azure.

Method 2: Migrating using the SQL Server Import/Export Wizard

The SQL Server Import/Export Wizard is the next logical step. After installing EdgeSight to a local database the Import/Export Wizard connects to both the source and the target database to transfer the objects. That’s how it works in theory. In this case, it fails to create the database on SQL Azure - no problem, we can do this manually in the Windows Azure management console. But this still does not do the trick because the Import/Export Wizard “cannot get the supported data types due to a missing stored procedure”.

Although the Import/Export Wizard supports SQL Azure beginning with SQL Server 2008 R2, it seems there is something special about the EdgeSight database.

Method 3: Migrating using the SQL Azure Migration Wizard

Fortunately, there is a open source project called SQL Azure Migration Wizard (available on CodePlex) which does an awfully good job at migrating the EdgeSight database to SQL Azure. Now here’s the steps to do this:

  1. Stop the Citrix RSSH Admin Service and the website in IIS (step 1 from CTX118977)
  2. Open SQL Azure Migration Wizard and connect to the source database. Make sure you select “Script all database objects”. In the advanced settings, set “Script Table / Data” to “Table Schema with Data”
  3. The SQL Azure MW takes some time to create a SQL script containing all objects from the source database
  4. Connect to your database in SQL Azure using the connection string built above and let SQL Azure MW import the data using the previously created SQL script
  5. Modify web.config using the connection string built above (step 5 from CTX118977)
  6. Modify the connection string in the registry (step 6 from CTX118977)
  7. Start the Citrix RSSH Admin Service and start the website in IIS (step 7 from CTX118977)

This leaves you with a working EdgeSight database in SQL Azure. But read on to find out about the issues you will probably encounter.

Issues with EdgeSight on SQL Azure

The first set of issues applies to EdgeSight with a remote databases in general:

The seconds set of issues applies to EdgeSight with a database server and restricted permissions:

Concerning these last issues, I’d like to repeat my plea towards Citrix: Please provide SQL scripts for the installation as well as updates of EdgeSight. I can provide real life use cases from customer environments where the SQL Server administrator does not allow the necessary permissions.

Summary

Was it worth it? Yes, I now know about SQL Azure.

Does it serve my needs? Yes, I can now work on SQL statements for custom EdgeSight reports without powering on my VM

Does it work well? It suffices for a demo environment but there are database timeouts on a moderately used internet uplink (at home).

Do I recommend it outside demo environments? No, because the update issue may cause a mismatch between the version of your EdgeSight server and the database.