Rebuild the reporting database

Abstract

How to rebuild the reporting database.

Rebuilding the reporting database reprocesses the interactions that have already been aggregated into the reporting database. The rebuild process can be summarized as follows:

  • Create and configure a secondary reporting database.

  • Rebuild the reporting database.

  • Reconfigure reporting database connection strings.

  • Verify that the rebuild was successful.

Note

Rebuilding the reporting database does not rebuild the search index - you must do this separately.

Before you can rebuild the reporting database, you must manually add a secondary reporting connection string to the processing server or servers.

Note

The reporting and secondary reporting databases can take up significant disk space so you might want to plan for extra storage requirements.

To create and configure a secondary reporting database:

  1. Take a clean copy of the DACPAC file for the Sitecore_Reporting database from your Sitecore distribution to use as your secondary reporting database. For best results, always use a clean copy.

  2. Create an empty database for the Reporting secondary database. If you are using Azure SQL then you must create a new SQL Azure database in the Azure Portal. You must update the Firewall settings on the associated Azure SQL Server with your Client IP Address (and you must remove this IP address from the Firewall when the rebuild process is complete).

  3. In SQL Server Management Studio, connect to the SQL Server or Azure SQL database instance and deploy the Sitecore_Reporting DACPAC.

    Note

    If you are running any Sitecore modules, such as WFFM, run the SQL script that adds the Fact tables for those modules against the secondary reporting database.

  4. On your processing and content management servers, add the following connection string, substituting your own server details and the name you have chosen for your secondary database:

<addname="reporting.secondary"connectionString="user id=_sql_server_user_;password=_user_password_;Data Source=_sqlserver_;Database= Sitecore_Reporting_Secondary"/>

Note

If you have multiple processing servers, all active processing servers must have access to the secondary reporting database, even if you have a dedicated server for history processing. This is because live data is being aggregated into the secondary reporting database, which means that agents responsible for interaction aggregation and contact processing need access.

The rebuild process is semi-automated but also requires an administrator to attach, detach, or replace databases in SQL Server, as well as modify some configuration files.

To rebuild the reporting database:

  1. In a web browser window, open the rebuild reporting database history processing page using the following path, where <sitename> is the URL of your content management server:

              <sitename>/sitecore/admin/RebuildReportingDB.aspx
            
  2. To begin rebuilding the reporting database (synchronization processing), click Start .

When the rebuild process is complete, you must manually swap your reporting and reporting.secondary databases. This means that the ‘live’ database is the Sitecore_Reporting_Secondary database and the original Sitecore_Reporting database is commented out.

To reconfigure reporting database connection strings:

  1. Swap the reporting and reporting secondary databases.

  2. Comment out or remove the reporting.secondary connection string.

The following table shows the connection string for each database before, during, and after the rebuild process.

Important

You must swap reporting connection strings on all roles that reference the xDB Reporting database.

Database

Before rebuild

During rebuild

After rebuild

reporting

Sitecore_Reporting

Sitecore_Reporting

Sitecore_Reporting_Secondary

reporting.secondary

NA

Sitecore_Reporting_Secondary

<– Commented Out –>

Important

If you do not comment out or remove the reporting.secondary connection string, data is continually written to both databases.

To verify that the reporting database was successfully rebuilt:

  • Open the Experience Analytics UI and check that your graphs and tables are populated with data.