1

I have our environment setup where we aren't using health data logging however I continue to get messages that state that the health data database is in secondary mode when attempting to update. I get all the way through 9 of 10 processes in PSConfig but it refuses to finish the updates and apply them to my farm because its trying to modify the health data database.

This question is 2 parts:

1) How does PSConfig know where the health database is located? Essentially, how is the connection string generated?

2) Do we even need to worry about this database being available in the HA system? The database wouldn't be located on the secondary server (because its not recommended to be in HA, so when we're on the secondary server, the database won't be able to be found...) so will this be a problem if we have it disabled in the central administration? What happens if we don't include it in the HA, force it to go directly to a single server, then that server is down, how will we be able to use it if we ever enable it again?

Error: An exception of type System.Data.SqlClient.SqlException was thrown. Additional exception information: The operation cannot be performed on database "SP2013_USAGE_HEALTH" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. ALTER DATABASE statement failed.

Everywhere I've seen just says "take it out of mirroring mode", which is fine, but help me understand why it is this database is trying to be altered in the first place and why we shouldn't have it in the HA system and why we don't need to have it backed up or available in secondary mode or Disaster Recovery situation. If we don't need it, why does it exist at all then?

  • I have also found this to be an issue. I can see that other people have already answers, however I also want to point out that I have noticed that the User Profile Service Application can end up being in an unprovisioned state if SharePoint is updated with the UPS database added to High Availability. – Nullldata Apr 9 at 14:09
2

The Usage database is not supported in any form of HA (perhaps SQL Clustering, but that's a bit dated). You should instead put your Usage database on a single SQL Server and reference that SQL Server directly. Unavailability of the Usage database will not impact farm operations.

Usage database consists of transient data -- there's simply no purpose behind having this database as part of any HA or DR scenario. It also generates a high volume of transactions, which puts additional load on your HA/DR of choice.

  • I see, so that circles back to my secondary question though, what happens if the directly referenced server becomes unavailable to be written to? For instance, we take down our SQL01 server, and we're using the SQL02 server for whatever reason and that health database doesn't exist anymore because the SQL01 server where it resides, is gone or unavailable. What impact will this have on our sharepoint farm if it cannot find the database anymore? – Tyler Walton Apr 3 at 17:00
  • I addressed that in my question. There is no operational impact to the farm. But if the SQL server is completely gone, you just create a new usage database (Set-SPUsageApplication). – Trevor Seward Apr 3 at 17:01
  • Ok... So why does it exist at all? Why can't we simply delete the entire database from our SQL server and save the space? If we ever decide to use health data logging will it create a new database for us, and when we're done with it can we just delete it again? – Tyler Walton Apr 3 at 17:02
  • Since you've stated it will have no impact. I'll run a test by dropping the entire DB from the system after backing it up and see if it impacts the environment or not. Since this is our testing environment anyway it shouldn't hurt anything. Right now I see it as 30Gbs of available space we could recapture so if that's the case, it would seem to be a viable response. I'm still not sure where that 30GBs of data came from in the first place since we're not using health data in the environment. So obviously something needed it at some point so I don't know how we can say there is no impact... – Tyler Walton Apr 3 at 17:24
  • I can safely say, there IS impact on the system if it can't access it. We tried to take the database offline and it wouldn't release it because one of our front ends was actively trying to use it even tho we aren't actively using health logging. Additionally I turned off IIS on all our front end servers and it still wouldn't release it. Finally I bounced the box and we took it offline, and when the WFE came back online, it screamed it couldn't connect. So if we ever ended up failing over to secondary the result would be the same. There is impact so unfortunately I can't mark this as answer. – Tyler Walton Apr 4 at 13:51
0

I have written about that topic here for AvailabilityGroups, but also applies to Mirroring-Sessions:

https://sharepoint.stackexchange.com/a/237532/33662.

Doesn't answer all of your questions, but still might help.

  • yes I saw this article and yes we know to pull it out of the AG to do the updates, and sure we can add it back in, but why take it out at all in the first place? Why can't it just be in there? That's the question. If its not something we have to back up and maintain what is the point of having it in the first place? Is it critical or not? Clearly it is by our test today. We took the DB offline and sharepoint screamed bloody murder that it couldn't connect to it even with IIS disabled, and everything turned off in central admin for health usage logging. So SOMETHING needs it for SOMETHING... – Tyler Walton Apr 4 at 13:45
  • The only thing I can come up with is that the database must EXIST on all sql servers in the AG but having it IN the AG isn't what's needed. The next question is if we were to delete all content from the database would it care? My thought is it SHOULDN'T if it truly is just transient data, but so far we can't PROVE that is true. – Tyler Walton Apr 4 at 13:57
  • We know that it is transient data. We know that we can take the SQL Server offline and the farm, as a whole, will continue to function. You can contact PSS, if you want -- they'll tell you just that. – Trevor Seward Apr 4 at 14:40
  • see my comment above on your answer. Just because its functioning doesn't mean errors aren't being generated. Errors in general will cause someone to wake me up and tell me my application is throwing critical errors, and that is an impact on farm operations even if I tell them "its safe to ignore it." When they ignore that one, then they'll also ignore other critical errors they shouldn't. That's not best practice. The whole point of the AG is so when it fails over to the secondary system it continues to operate the same as on the primary node. If its throwing errors that's not the case. – Tyler Walton Apr 4 at 16:22
0

The solution to my problem was a fairly simple, others have contributed to this answer but it wasn't entirely accurate. If you utilize an HA system you cannot have the Health Usage database in the high availability during patching as it causes the PSConfig application to error, this is what others have said and it is accurate. During the patching process the application attempts to set the database into single user mode and this cannot be done while the database is participating in a database mirroring / high availability configuration. This is what was causing my error in the first place.

If your system utilizes multiple SQL servers, if you fail over to your secondary node your system will throw critical errors until you create a new database on the node that does not have it. This is acceptable by some however I felt it was avoidable.

To proactively prevent this, all SQL servers in your high availability farm should have the database setup manually but not participating in the HA. What I did was I generated a CREATE TO script from the primary node SQL server for this database and go to each server and execute it on each server. I got errors regarding maximum key length and index errors but they were all warnings and didn't appear to effect the system that I could tell. I also received stored procedure errors noting that dependencies were missing, again, I believe those can be ignored as well as they were more than likely caused because of the order of generation of the automated script and would be created later. Use this at your own discretion. The alterative is to use the set-SPUsageApplication command however I believe this may cause additional problems if you have another database somewhere else named the same, and if you try to specify the same GUID directly it may not work as well for a duplication error. I was unable to get it to work myself, in either regard so this is why I chose to script it directly to the SQL server, hoping to avoid duplication issues.

SharePoint doesn't know your system uses a high availability so the simple solution is to backup the database and restore it on each server, but in my case I had 30GBs of data in my primary node database so I could not do that either. The content shouldn't matter according to other posters since the data is transient data. Either way, SharePoint doesn't know those servers need it setup and since you cannot utilize the high availability to generate the database on each server (which imo would be far simpler) it must be done manually for each server and this is not outlined anywhere in any whitepapers that I saw so I had to makeshift something that would solve the problem.

While the system will function without the database, you will see critical errors. This is the only method to avoid critical errors and additionally without the database in existence, PSConfig will not run as well until you create the database on a node that it does not exist in. This was the only way I was able to resolve it in an acceptable manner that stopped errors on my sharepoint farm. As this is obviously not officially adopted by Microsoft at the time of this post, use at your own discretion.

So the answers to my questions above:

1) The database connection string is configured in the original starting GUI in the PSConfig. If you use the command line it will pull the database setup from the SharePoint_Config database shown in the central administration under the System Settings -> Manage servers in this farm section. You can utilize an HA listener as long as the health usage database is not in the high availability configuration and accessible to the listener.

2) While this database is not breaking to SharePoint operations if it doesn't exist, the database is critical to proper operations and must exist in your farm, if not for day to day operations and preventing errors but also to properly patch the application as PSConfig will attempt to alter the database to single user mode during patching and will not function if the database does not exist. Additionally it will throw critical errors to your application errors log in the OS if it doesn't exist but as Trevor stated above, it will not bring your system down completely and can be handled without end users noticing any problems on their end regardless of its existence.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.