none
Why Usage and Health Data Collection database is not recommended for high availability? RRS feed

  • Question

  • Hello colleagues,

    I have encountered that the aforementioned database must be off the availability group when SharePoint updates are provisioned. On the TechNet article this database is labeled as not recommended for HA:

    https://technet.microsoft.com/en-us/library/jj841106.aspx

    Can anyone explain why? Should we only remove the database temporary when the SharePoint is being upgraded or there are reasons to keep it off the cluster all the time?


    Monday, June 12, 2017 8:24 AM

All replies

  • the database is used to store usage and health data. you can configure HA for it, but it is not necessary.

    Kally

    Thursday, June 15, 2017 1:27 AM
  • Three reasons:

    1) You cannot run the Config Wizard while the database is in a highly available state, such as mirroring or AlwaysOn.

    2) The data is transient. There is nothing to preserve in the Usage database. Your farm will continue working without a usage database present, e.g. if you fail over.

    3) There is a significant amount of data being loaded into the Usage database. This could possibly impact your replication technology of choice.


    Trevor Seward

    Office Servers and Services MVP



    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, June 15, 2017 2:23 AM
    Moderator
  • Hi Trevor,

    I realise this is an old thread, but was just wondering with you following statement and referring to the recommendation in this MS Article:

    2) The data is transient. There is nothing to preserve in the Usage database. Your farm will continue working without a usage database present, e.g. if you fail over.

    What are the implications of failing over to the secondary SQL instance in the AG and the Health & Usage DB not being there?

    Will the system create one?

    Does one need to be provisioned manually?

    Thanks

    Thursday, October 17, 2019 1:59 PM
  • SharePoint won't be able to transfer the data to the Usage database. There is no end user impact. The system will not create another database for you. You can provision one of the same name, if you wish, but then you can't combine the data.

    Trevor Seward

    Office Apps and Services MVP



    Author, Deploying SharePoint 2019

    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, October 17, 2019 2:36 PM
    Moderator
  • Hi Aleksandr,

    This is my analysis on this:

    1. If we remove Usage database(log db) from availability group there is no direct impact to the business users.
    2. We can can complete the SharePoint configuration wizard until we remove this db from AG.
    3. This is a heave database used for logging purpose which consumes huge resources. 

    Thanks&Regards,

    Habibur Rahaman

    MCSA,MCP,MCTS

    My Certificates

    Note: If you feel the proposed answer is helpful, please mark as so or if this answered your question,please mark as answer.

    Thursday, October 17, 2019 5:36 PM
  • Hi Trevor,

    I really appreciate your speedy response!

    So if I can just confirm a couple of assumed recommendations based on your guidance:

    1. The health and usage DB should not be sync'd via the AG
    2. If an auto failover to the secondary SQL instance happens or is manually triggered, a DB of the same name could be manually provisioned within the secondary SQL instance and be used by the farm, but this will not allow it be merged with the old data
    3. If old usage data is required, restore a known good backup of the Health and Usage DB from the primary SQL instance in to the secondary SQL instance
    4. If no Health and Usage DB is present in the secondary SQL instance, the farm will function with no end user impact:

      But I am assuming no farm health information would be available in CA & no usage information will be to users via their sites.

    I really appreciate your help with helping me get this clear in my own head.

    Thanks.

    Thursday, October 17, 2019 11:29 PM
  • 1) Correct

    2) Correct

    3) Correct

    4) Correct

    The health information would go _stale_ as the timer job on the servers wouldn't be able to upload it to the database. Once the database became available again, the timer job would function as expected.

    Usually what I do, since an AOAG should generally be available 99% of the time is to use one of the two SQL Servers specifically with the Usage database. That is, point the Usage SA to a *specific* SQL Server.


    Trevor Seward

    Office Apps and Services MVP



    Author, Deploying SharePoint 2019

    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Proposed as answer by Dean808 Friday, October 18, 2019 12:48 AM
    Thursday, October 17, 2019 11:35 PM
    Moderator