SharePoint 2013: Moving the Usage and Health Database

 

It Begins with a question

In this blog post, I am presenting a question that I recently received from a customer.   The question was:  What steps need to be taken to move the database utilized by the Usage and Health Data Collection Usage Service Application (referred to as the WSS_Logging DB) to a different database server.  Furthermore, the steps need to minimize server downtime and ensure that no historical and current usage data is lost.

The question seems simple enough and something that I initially thought would be well documented in the SharePoint product documentation found in the online TechNet libraries.   However, it turns out the question is not as straight forward as first thought. 

Let the Research Begin

As with most questions like this, the first step is some quick internet searching.  An internet search query (Bing of course, what else?) led me to the following article:

Move service application databases (SharePoint 2013)

https://TechNet.microsoft.com/en-us/library/jj729805.aspx

This article is a great start and appears to provide me all I need to move any of the SharePoint Service Application databases to a new server or instance.  There is a specific article for the Usage and Health Data Collection Service application linked here:

Move the Usage and Health Data Collection service application databases in SharePoint 2013

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

Awesome.  This article seems like it an exact match.  It should everything I need, right?   Well after reading closely, and then actually trying to implement the steps in a test lab, I realized that there are some areas of the article that are ambiguous and misleading.

Highly summarized, the guidance in the article is to perform the following steps:

1. Move the service application database

a. Stop the service application

b. Detach the database from SQL Server

c. Move database files to a new location

d. Attach the database to a new instance of SQL Server

2. Point the Usage and Health Data Collection service application to the moved database. 

The guidance under Step 1a “Stop the service application” contains a link to the following document:

Start or stop a service (SharePoint 2013)

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

Whoa.  Hang on.   The guidance says to “Stop the service application” but the link references steps to “Stop a SharePoint Service”.  A Service Application and a SharePoint Service are two distinctly different things so what gives?  After digging a little deeper, I think the wording in the guidance should say “Stop the Service”. rather than “Stop the Service Application” 

Okay great, stop the SharePoint service that corresponds to the “Usage and Health Data Collection Service Application”, but what service is that exactly?  Unlike many of the other service applications which have clearly defined SharePoint services, the Usage Application does not.

Some Background about the Usage and Health Data Collection Service Application

To better understand what needs to be done to complete the move, it is helpful to take a step back and remind ourselves what the Usage and Health Data Collection is and does.  This service application provides a set of data collectors that, when enabled, collect and aggregate useful information about the health of the SharePoint components of the farm and how they are being used.  These collectors are implemented as timer jobs, and there are collectors for a variety of things, including server event logs, SharePoint diagnostics logs, SharePoint feature usage data, and server performance counter logs.  All of this data is stored in a single database (the Usage or Logging DB) and is specific to this particular farm.  Many of these data collectors are disabled by default but can be enabled by the administrators to collect this data as desired.   Due to the potentially large volume of data that can be collected, the system is designed such that it retains only the last 14 days of data and old data is automatically purged,  This results is a “rolling window” of the last 14 days (by default)..  Because this database can be very active and grow to be very large, it is considered a recommended best practice to place this database on its own SQL server if possible.  See the below link for more information about database sizes:

Database types and descriptions (SharePoint 2013)

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

This database is primarily intended for reporting and manual querying purposes. However, there are a few reports from the Search Service Application that rely on data in this database.

Validation in the Lab

After completing my research and validating these steps in a lab, I have slightly modified the steps provided in the TechNet article below:

1. Perform a complete SQL backup of the Usage and Logging database

2. Stop and disable the timer service

a. Stop and disable the SharePoint Timer Service on all servers in the farm

This can be done via the Service control panel or by running the following command from an elevated command prompt: sc config sptimerv4 start= disabled sc stop sptimerv4 Note: the “space” following start= is required and is not a formatting anomaly

 

3. Move the service application database 

a. Verify the account that is the DBOwner of the WSS_Logging database

b. Make sure that the new instance of SQL server has a login configured for the DBOwner from step #3a.

c. Detach the database from SQL Server

c. Move or copy database files to the new instance of SQL Server

d Attach the database to a new instance of SQL Server specifying account captures in step #3a as the Database Owner 

4.  Configure the Usage and Health Data Collection Service Application

a. Point the Usage application to the new Database

From an elevated SharePoint Management Console, run the following PowerShell command: Get-SPUsageApplication | Set-SPUsageApplication -DatabaseName "<DbName>" -DatabaseServer "<SQLServerName>"

5. Restart the Timer service

a. Re-enable and start the SharePoint Timer Service on all servers in the farm.

This can be done via the Service control panel or by running the following command from an elevated command prompt: sc config sptimerv4 start= auto sc start sptimerv4 Note: the “space” following start= is required is a not a formatting anomaly

b. Re-boot all servers in the farm

Hope you find this information helpful!