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
On the SiteScope server, remove the
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.