SiteScope Logging to a SQL AlwaysOn Availability Group

SiteScope can log monitor status and response data to a couple of locations – flat files and databases, primarily.  While useful, it does fail when you attempt to send those logs to a Microsoft SQL Server Availability Group (AlwaysOn). If we use a new driver, however, we can make it work.

Versions:  SiteScope 11.32, SQL 2014, JDBC driver 4.2

The first thing to do is download the latest JDBC driver from Microsoft’s website – https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774
I downloaded the sqljdbc_6.0.7507.100_enu.exe version since it was the latest at the time of this post. Download the file and open the exe. It will prompt you for an extraction location. You will only need 2 files from this extract, so a temp location is fine. Grab the latest sqljdbc file (sqljdbc42.jar in this case) and copy it to the \WEB-INF\lib directory. From the extract, you also need to grab the sqljdbc_auth.dll file and copy it to the c:\windows\system32 directory on the SiteScope server.

On the SiteScope server, remove the \WEB-INF\lib\sqljdbc.jar file (delete or move it out of the SiteScope directories entirely). Be advised that any previous database connections that use this driver can fail, so test those after removing the file and restarting SiteScope. Launch SiteScope, navigate to Preferences->Log Preferences, and enter “jdbc:sqlserver://:1433;databaseName=;IntegratedSecurity=true;MultiSubnetFailover=true” (without the quotes) as the database connection URL. Enter “com.microsoft.sqlserver.jdbc.SQLServerDriver” (without the quotes) as the database driver. Leave the username and password entries blank.

The important pieces in play here – the updated driver supports the connection property MultiSubnetFailover. That tells the connection it can fail over to another member of the AG. The entry for IntegratedSecurity=true tells the driver that the user that is running SiteScope will be making the connection. It is important that the SiteScope service is running as a user that has rights to Create and Insert on the database.

Restart the SiteScope service. It will attempt to connect to the database in the URL, create a table called SiteScopeLog, and start logging after a few monitor runs (default 20).

Bonus – you can update the table that SiteScope logs to – for example, if you want multiple SiteScopes logging to a single database but have different tables, you can have each log to a table that is unique for that SiteScope. This support article details how you can change the master.config file – specifically the CREATE and INSERT statements.

Hope this article helps. This took way to long to figure out how to do, so I hope it saves you some time.