SharePoint Usage Database in SQL Server AlwaysOn

SharePoint has a very handy service-application that’s normally running called the “SharePoint usage & health” application. It’s actually a very useful to have under normal circumstance as it logs pretty much everything you could ever want to know about the operational health of the SharePoint farm; page-load times & metrics along with the developer-dashboard data, search performance statistics, etc, etc. All great data for SharePoint administrators, but this is a quick post about why you shouldn’t add it to your AlwaysOn cluster.

TL;DR: Don’t bother adding the usage application database in AlwaysOn. If SharePoint can’t reach it there’s very little impact.

Want a longer explanation? Read-on…

Why Is It a Bad Idea to Have Usage Database in AlwaysOn?

The problem with the usage application database is that it’s a very fluid database in terms of sheer number of updates to it, by design. Every page-load, every user-action and every admin job is basically another entry, and these updates quickly add-up. The problem that this causes is that the contention issues with the AlwaysOn data-synchronisation as the usage updates will be competing with, say, content database updates too, and obviously the two are in entirely different categories of importance.

Also there’s the small detail that for databases to be used with AlwaysOn they have to be in “full recovery” model. Full-recovery means every commit is also logged in the LDF file so the database file-size will balloon very quickly in the mode.

But What Happens if SharePoint Usage Application Dies?

Not much actually; SharePoint doesn’t need it to run core functionality – pages will still render as they did before, all the important processes will carry on no problem. Unless you look in the logs you basically won’t notice anything’s wrong, with the one exception of the developer-dashboard being mysteriously silent.

Here’s an example of a SharePoint server which can’t access the usage databases

You’d be forgiven for thinking these really are critical events being logged, and as far as the usage application is concerned they are. But in reality, again, using SharePoint (with the exception of some admin views), you’re not going to notice.

So Should I Mount the Usage Database in SQL Server AlwaysOn?

No, is the short answer. “Definitely not” is the longer answer, unless you have boat-loads of network bandwidth & disc-space to burn. Should you have a failover, SharePoint will complain about not seeing the database any more but life will go on.

That’s it!

 

Cheers,

// Sam Betts