The App-V Reporting database for App-V 5 and above is great for monitoring all usage of your virtual applications. This is quite helpful when it comes to license compliance. While it won’t prevent (accidental) usage of unlicensed apps, it is very useful for detecting usage as part of a program to “true up” your license allocations.
The Reporting Server is independent of how you deploy the virtual apps. It doesn’t matter whether you use the App-V Server System Center Configuration Manager, or deploy using the stand-alone client. You can still use the reporting server.
If you do, I highly recommend that you use the App-V Client GPO to configure the clients to use the reporting server. Setting this as an enforceable policy makes sure that every client will report in usage data each night. Only with non-persistent clients (like some VDI clients or PVS booted XenApp servers) is there a concern of not getting the data, but those can be solved by adding a report sync to the user logoff scripts using the App-V Client Powershell API.
There is always a but…
But this blog post is to warn you about a small flaw in the App-V Reporting Database setup. By default, it will not clean out old data. EVER! I am not referring to the temporary table data (removed when records get consolidated each night), but the record data on each and every virtual application run by a user. The App-V 4.* server would automatically clean this out at some point. But not version 5. While Microsoft added logic in the form of stored procedures to clean old data out, it isn’t used by default.
The stored procedure, spEnforceSizeLimit, is designed to check the size of the current database and compare it to a configurable maximum size. There are also two additional configured settings, a low water mark and high water mark. These are stored as percentage factors and are interpreted as relative to the configurable max size. So the current value can be in one of thee states against these settings:
- If the current size of the database is below the low water mark it decides that things are OK. If logs a WARNING message to the Sql database log (“WARNING The database size limit cannot be enforced“) and exits. I guess it is a warning that maybe you aren’t collecting any data and maybe you should check on that.
- When above the low water mark and below the high mark , it will seek to purge the oldest application usage data. The logic is simple (much simpler than the extensive logic used in the original App-V server) but sufficient. It will take a ratio of the current size to that of the low water mark, look at the date of the record about that far into the usage table (when sorted by date) and remove anything older than that date. It adds a message to the Sql database log (“WARNING: The database size limit has been enforced“). This message is a good thing and indicates that the purging is working.
- When above the high water mark, it logs a different message to the Sql database log (“WARNING: The database is cleaning up a large percentage of data“), and then processes just as in the previous case.
So here are the two problems in the default install preventing this purging from working:
- It depends on the maximum size, and low and high water marks being present in the SystemOptions table of the database, and these are not set by default, as shown in the image below.
This is treated by the stored procedure as an error, however no error message is logged and no purging occurs. This means that the stored procedure will do nothing when called of there isn’t a row in this table. - There is nothing that triggers the stored procedure to run anyway.
The image above shows the default jobs in the database, one to handle the daily processing of received client reports, and the other a standard database job. No job to run the cleanup script though.
And I wouldn’t be writing this if there wasn’t a solution…
To solve these issues and ensure that your database doesn’t infinitely grow (well, or until your DBA notices), the DBA should do the following:
- Ensure that Sql Server Agent is running on the database server. (If it isn’t, the stored procedure to process the client data from the temporary tables isn’t being triggered to run and you won’t get reporting data anyway).
- Add a row to the empty SystemOptions table. Set the max_db_size column with a value for the maximum database size in Megabytes. Each record in the Application Usage table takes around 200bytes of storage. Multiply that by the number of users, then by the number of average app launches per day, then by the number of days of data you want to keep. Then add some overhead for the temporary and other tables (maybe 50MB?) and spare capacity and you have your setting for the maximum size. Set the low and high water percentage mark appropriately, but be aware that in spite of the name they are really factors, so 0.80 and 0.90 might be good numbers. Make sure to monitor the logs after a while. The warnings I mentioned provide details on the numbers calculated to help you adjust these settings.
- Add a SQL job to run that calls the spEnforceSizeLimit stored procedure. There is already a job to call the spProcessClientReport stored procedure once a day, so mimic that job.
- Play the logic game on when things happen, and possibly adjust timers.
- The App-V GPO schedules the reports to be uploaded, typically once a day at night. The upload from a client is fairly quick, but there is a configured random delay when the scheduled time occurs. If the random delay is 30 minutes, then an hour after the scheduled time is plenty of time for this to complete. This should be allowed to complete before the spProcessClientReport is scheduled.
- The job for spProcessClientReport should be scheduled next. The amount of time for this job depends on the number of users*applaunches for the day. But if you assumed that it can complete in an hour you would be safe.
- Finally, the spEnforceSizeLimit should be scheduled last. It won’t hurt anything if it runs while either of the other two activities are happening, but it makes sense to make this last.
Now I’m pretty certain that your DBA does not follow my blog, so if you are the App-V guy and are reading this, pass it on to him or her.
