Walkthrough: Rebuilding the reporting database


Follow the steps in this walkthrough to rebuild the reporting database.

The Sitecore Experience Database (xDB) uses two database systems (SQL Server and MongoDB) that need to be kept in sync to provide accurate data for Sitecore reporting applications:

  • MongoDB collection database - stores all experience data

  • SQL Server reporting database - stores aggregated reporting data

Sitecore automatically keeps these databases in sync but there are certain circumstances when you may need to perform a complete rebuild of the reporting database. For example, after you have run the Sitecore Analytics Conversion Tool and have additional historical data that you want to include in your reporting applications. There are also several other reasons for rebuilding the reporting database.

When you rebuild the reporting database, to avoid disruption during the rebuild process, you connect a secondary reporting database to store all aggregated data as it is reprocessed.

This walkthrough describes how to:


To rebuild the reporting database in xDB Cloud, see REST API reference for xDB Cloud service.

Before you start the rebuild process, first check that you have the MongoDB analytics database and the SQL Server reporting database connected. These databases are part of a standard Sitecore installation. You only need to create and configure a secondary reporting database if you intend perform the rebuild process.

Each database has a separate connection string that you can configure in the App_Config/ConnectionStrings.config file:

  • Collection database (MongoDB) – can contain historical data from a converted Sitecore DMS SQL Server database and new data collected from your website (live data).

    MongoDB collection database connection string:

    <add name="analytics" connectionString="mongodb://localhost/analytics"/>
  • Primary reporting database (SQL Server) – contains all the live data collected on your website after upgrading to a newer version of Sitecore.

    Primary reporting database connection string:

    <add name="reporting" connectionString="user 
    Source=_sqlserver_;Database=Sitecore_Reporting" />
  • Secondary reporting database (SQL Server) stores historical data during the rebuilding of the reporting database. See the next section for detailed instructions on how to create and connect a secondary reporting database.

    Secondary reporting database connection string:

    <add name="reporting.secondary" connectionString="user 
    Source=_sqlserver_;Database= Sitecore_Reporting_Secondary" />

The first step in the rebuild process is to create and then connect a secondary reporting database.

To connect and configure a secondary reporting database:

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

  2. In SQL Server Management Studio, attach the copied database to your SQL Server instance. Use the name reporting.secondary in the connection string.


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

  3. 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. Add or edit the reporting.secondary connection string to point to the newly created database. For example:

    <add name="reporting.secondary" connectionString="user 
    Source=_sqlserver_;Database=Sitecore_Reporting_Secondary" />


    If you have installed the Sitecore upgrade package, you may have already added a secondary reporting database, and if so, you do not need to perform this step twice.

  5. If you are configuring a dedicated server, check that you have enabled the history processing pool in the appropriate configuration file.

    For example, to enable history processing on a dedicated content delivery server, navigate to the following processing configuration file:


    In this config file, make sure the <Enabled> parameter is set to true: /configuration/sitecore/aggregationProcessing/processingPools/history/Enabled

    <history type="Sitecore.Analytics.Data.MongoDb.ProcessingPool.MongoDbProcessingPool, 
      Sitecore.Analytics.MongoDb" singleInstance="true" >
      <param desc="connectionStringName">tracking.history</param>

In the latest version of Sitecore xDB, the primary SQL Server reporting database contains some additional marketing definition tables that you need to copy to the secondary reporting database. When you run the rebuild reporting database page ensure that you allow more time for the clear storage process while these tables are being copied.

The default time to clear storage setting is 1 minute. Change this setting to a time interval appropriate for your Sitecore solution, for example, 10 minutes.

To change the TimeToClearStorage setting:

  1. Open the Sitecore.Analytics.Processing.Aggregation.config file.

  2. Change the TimeToClearStorage setting to an appropriate time. For example 10 minutes.

    <reportingStorageManager type="Sitecore.Analytics.Aggregation.History.ReportingStorageManager" singleInstance="true">

To rebuild the reporting database:

  1. In a web browser window, open the rebuild reporting database history processing page using the following path:



    You can only run the RebuildReportingDB.aspx and RedeployMarketingData.aspx pages from a content management server.

  2. Click Start to begin rebuilding the reporting database (synchronization processing).

  3. In the Rebuild Reporting Database page, when you see Waiting to receive to data status, copy the following marketing definition tables from the primary to the secondary reporting database:

    • CampaignActivityDefinitions

    • GoalDefinitions

    • OutcomeDefinitions

    • MarketingAssetDefinitions

    • Taxonomy_TaxonEntity

    • Taxonomy_TaxonEntityFieldDefinition

    • Taxonomy_TaxonEntityFieldValue


    It is important that you add these additional tables to the secondary reporting database while the Waiting to receive to data status is displayed in Rebuild Reporting Database page. Ensure that you have also changed the TimeToClearStorage setting in the Sitecore.Analytics.Processing.Aggregation.config file to allow sufficient time for these tables to be added.

    After you have added these tables the Rebuild Reporting Database page resumes the rebuilding process.

  4. The rebuild reporting database tool provides feedback while it is processing until the rebuild process is completed.



When you run the rebuild process (synchronization), this erases all information in the Sitecore_Reporting_Secondary database. Therefore, you should always use a clean copy of the database when you rebuild the reporting database because this speeds up the rebuild process and uses fewer resources.

After the rebuild process has finished, you need to reconfigure the reporting and reporting secondary connection strings:

  1. Navigate to the Website/App_Config folder, and change the names in the ConnectionStrings.config file Database= attribute, so that the reporting connection string points to the Sitecore_Reporting_Secondary database (this has replaced the Sitecore_Reporting database).

  2. Comment out the reporting.secondary connection string.

    The following samples show the connection strings after swapping the database names and commenting out the reporting.secondary connection string:

    <add name="reporting" connectionString="user 
    Source=_sqlserver_;Database=Sitecore_Reporting_Secondary" />
    <add name="reporting.secondary" connectionString="user 
    Source=_sqlserver_;Database=Sitecore_Reporting2" />

    The xDB now uses the Sitecore_Reporting_Secondary database as its primary reporting database to collect new, live data from your website. The Sitecore_Reporting2 database is disabled until you need to enable it again the next time you rebuild the reporting database.


    Sitecore_Reporting2 is an example name that you can use for the clean reporting database next time you rebuild the reporting database.

  3. To verify that the rebuild has been successful, open the Experience Analytics reporting application and view reports that show the most recent visits to your website. The visits shown in these reports should correspond to the historical data that came from the MongoDB collection database.


    If you encounter problems, you can easily swap back the connection strings to restore the previous version of the reporting database.