Monday, 18 July 2016

Sitecore error while rebuilding reporting database

Leave a Comment
Recently I stuck with following issue while rebuilding reporting database: (FYI, Reporting database can be rebuild by visiting url <sitename> /sitecore/admin/RebuildReportingDB.aspx)
Connection string used by aggregation/historyTaskManager/ReportingStorage is missing.
Sitecore xDB uses SQL Server and MongoDB which need to be kept in sync to provide accurate data for various Sitecore reporting applications.
  • MongoDB collection database - This stores all captured experience information from your website in a loosely structured way.
    MongoDB collection database connection string:
    <add name="analytics" connectionString="mongodb://localhost/analytics"/>
  • SQL Server primary reporting database - stores information extracted from the collection database in a form suitable for reporting.
    Primary reporting database connection string:
    <add name="reporting" connectionString="user id= _sql_server_user;password= _user_password_@123;Data Source=(local);Database=Sitecore_Analytics"/>
By default Sitecore xDB must always keep the reporting database in sync with the collection database but there are certain circumstances when you may need to perform a complete rebuild of the reporting database. Check here various reasons for rebuilding reporting database.

When you rebuild the reporting database, to avoid disruption during the rebuild process, you connect a Secondary reporting database (SQL Server) to store all aggregated data as it is reprocessed. Secondary reporting database stores historical data during the rebuilding of the reporting database.

Secondary reporting database was not configured in my scenario therefore I was facing above error. Perform below steps 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. 
  2. Attach this database to SQL server instance and name it Sitecore_ Analytics_Secondary.
  3. Open your connectionstrings.config file and add a reference to you secondary reporting database. Use the name reporting.secondary in the connection string.
    <add name="reporting.secondary" connectionString="user id= _sql_server_user;password= _user_password_@123;Data Source=(local);Database= Sitecore_ Analytics_Secondary "/>
Rebuild the reporting database and above error should be gone now. Comments and suggestions are most welcome. Happy coding!

Related articles:
  1. Rebuilding the reporting database 
  2. Reasons for rebuilding the reporting database 
  3. xDB Processing overview
  4. Troubleshooting xDB data issues
Read More...