1. The xDB databases

Sitecore XP 9.0.0 and later

Version: 10.5

The following table lists the databases that you must upgrade for the Sitecore XP topology.

For some databases the table lists the scripts that you must run against the xDB database. For other databases, you can find more details after the table.

DatabaseSitecore XP version
10.0.X10.1.X10.2.X10.3.X10.4.X
processing engine storageNo actionNo actionNo actionNo actionNo action
processing engine tasksSXP_processing_engine_tasks.sqlNo actionNo actionNo actionNo action
messagingSXP_Messaging.sql*SXP_Messaging.sql*SXP_Messaging.sql*SXP_Messaging.sql*SXP_Messaging.sql*
exm masterNo actionNo actionNo actionNo actionNo action
processing tasksNo actionNo actionNo actionNo actionNo action
processing poolsUpgrade following section belowUpgrade following section belowUpgrade following section belowUpgrade following section belowNo action
reportingUpgrade following section belowUpgrade following section belowUpgrade following section belowUpgrade following section belowNo action
reference dataNo actionNo actionNo actionNo actionNo action
collection smmNo actionNo actionNo actionNo actionNo action
collection shardsUpgrade following section belowNo actionNo actionNo actionNo action
marketing automationSXP_marketingautomation.sqlNo actionNo actionNo actionNo action
DatabaseSitecore XP version
9.0.09.0.1 – 9.0.29.1.09.1.1 – 9.2.09.3.0
processing engine storageNew**New**SXP_processing_engine_storage.sqlSXP_processing_engine_storage.sqlNo action
processing engine tasksNew**New**SXP_processing_engine_tasks.sqlSXP_processing_engine_tasks.sqlSXP_processing_engine_tasks.sql
messagingNew**SXP_Messaging.sql*SXP_Messaging.sql*SXP_Messaging.sql*SXP_Messaging.sql*
exm masterNew**Upgrade following section belowUpgrade following section belowNo actionNo action
processing tasksSXP_processing_tasks.sqlSXP_processing_tasks.sqlSXP_processing_tasks.sqlSXP_processing_tasks.sqlNo action
processing poolsUpgrade following section belowUpgrade following section belowUpgrade following section belowUpgrade following section belowUpgrade following section below
reportingUpgrade following section belowUpgrade following section belowUpgrade following section belowUpgrade following section belowUpgrade following section below
reference dataUpgrade following section belowUpgrade following section belowUpgrade following section belowUpgrade following section belowNo action
collection smmUpgrade following section belowUpgrade following section belowUpgrade following section belowUpgrade following section belowNo action
collection shardsUpgrade following section belowUpgrade following section belowUpgrade following section belowUpgrade following section belowUpgrade following section below
marketing automationSXP_marketingautomation.sqlSXP_marketingautomation.sqlSXP_marketingautomation.sqlSXP_marketingautomation.sqlSXP_marketingautomation.sql

* If you are using the Azure Service Bus, you do not need to upgrade the messaging database.

After you execute SXP_Messaging.sql, the messaging database is no longer aligned with a clean Sitecore XP 10.4.0 installation. The following tables are still present, but no longer in use:

  • [dbo].[Sitecore_CT_ModelTrainingTaskStatusSubscriber]
  • [dbo].[Sitecore_PE_TaskCancellationConsumer]
  • [dbo].[Sitecore_PE_TaskCancellationProducer]
  • [dbo].[Sitecore_PE_TaskProgressConsumer]
  • [dbo].[Sitecore_PE_TaskProgressProducer]
  • [dbo].[Sitecore_PE_TaskRegistrationConsumer]
  • [dbo].[Sitecore_PE_TaskRegistrationProducer]
  • [dbo].[Sitecore_PE_TaskStatusPublisher]

This ensures backward compatibility if you need to rollback to a previous version. Alternatively, you can remove them yourself when you are confident that you do not need to roll back to a previous version.

** All the new databases are installed when you install the new instance of Sitecore XP.

The following connection strings are added for the newly deployed databases:

  • processing.engine.tasks – the connection string is required on the Processing engine service and on the Cortex Processing service.
  • processing.engine.storage – the connection string is required on the Cortex Processing, and Processing engine services.
  • messaging – the connection string is required on the CM and CD server roles and on the following xConnect roles:
    • collection
    • collection search
    • marketing automation
    • automation engine service
  • exm.master – the connection string is required on the CM servers.

Messaging

To upgrade the Messaging database:

  • Run the SXP_Messaging.sql script for the Messaging database.
Note

In Sitecore XP 10.5, additional schema changes have been introduced in the Messaging database due to updates in the messaging implementation.

The IDX_RECEIVE index definition (column ordering) has changed and might not be aligned in upgraded databases.

The Sitecore_DataBus table now requires [Id] VARCHAR(400) NOT NULL with a primary key constraint.

Running the SXP_Messaging.sql script helps ensure that the Messaging database schema is aligned with these changes and remains compatible with the updated platform.

Exm.master

To upgrade the exm.master database :

  1. If you are upgrading from Sitecore XP 9.0.X, run the SXP_exm_master_dispatchqueue.sql script.
  2. If you are upgrading from Sitecore XP 9.0.X - 9.1.0, run these scripts in the following order:
    • SXP_exm_master_suppressions.sql
    • SXP_exm_master_save_suppressions.sql
    • SXP_exm_master_update_suppressions.sql

Processing pools

To upgrade the Processing pools database, follow the steps below:

  1. If you are upgrading from Sitecore XP 9.2.0 or earlier, run the SXP_processing_pools.sql for the Processing pools database.
  2. Run the SXP_processing_pools_improvements.sql script for the Processing pools database to apply improvements that are done in Sitecore XP 10.4.

Reporting

Important

When upgrading to Sitecore XP 10.4.0, the index IX_Fact_TestOutcomes_TestSetId will be dropped if it exists.

The index IX_Fact_TestOutcomes_TestSetId is a redundant index that was inadvertently included in previous versions.

To upgrade the Reporting database, follow the steps below:

  1. Run the SXP_reporting.sql script for the Reporting database.

  2. If you are upgrading from Sitecore XP 9.2.0 or earlier, run the SXP_reporting_migrate_experience_optimization_data.sql script for the Reporting database.

  3. Rebuild the Reporting database.

    If you are upgrading from Sitecore XP 9.0.0 - 9.2.0, we recommended that you Rebuild the Reporting database when the upgrade process is complete. For more information see the section Rebuild the Reporting database.

    In Sitecore XP 9.3.0, two new columns – Converted and FirstImpressionCount - were added to the Reporting database artifacts to improve the accuracy of conversion and bounce rate calculations. If you wish to retrieve the historical values with accurate calculations for these columns, rebuild the Reporting database.

    If you decide not to rebuild the Reporting database, after the SXP_reporting.sql script execution it will set the default value of the conversion and the bounce rate of old data to zero and these values will only be calculated for new interactions after the upgrade.

xDB Reference Data

We recommend that you make a copy of the database before you upgrade the database.

Important

If you are upgrading from Sitecore XP 9.0.0 or later and it is deployed in the Azure App Service, you must change the collation of the xDB Reference Data database from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CS_AS before you upgrade the database.

To upgrade the xDB Reference Data database:

  1. Run the SXP_referencedata_Part1.sql script.

  2. Review the results of running the script. If you receive the following message:

    # definitions cannot be migrated. Length of # definition monikers exceeds 300 characters. Consider truncate, change or remove them. Upgrade stopped. Fix all the issues and run this script again. Upgrade (part 1) has not been completed.

    Fix all the issues as described in this message and run the SXP_referencedata_Part1.sql script again.

    Repeat this step until you receive the Upgrade (part 1) has been done successfully. message.

  3. Run the SXP_referencedata_Part2.sql script.

Collection

Important

Since Sitecore XP 10.1.0, the MongoDB provider is no longer supported. You must use the SQL server provider. Familiarize yourself with the data migration options that are available.

The SQL Collection databases are deployed with the new Sitecore XP installation. If you want to create a custom shard cluster, use the SQL Sharding Deployment Tool.

This section describes how to upgrade SQL Collection databases.

To upgrade the Shard Map Manager database:

  • Run the SXP_collection_smm.sql script.

    This script registers the schemas for the shard maps.

You must also upgrade the collection shards database.

The upgrade path for the collection shards database depends on the Always Encrypted (AE) feature and whether it is configured (On) on your collection database or not (Off).

To upgrade the collection shards database:

  • Run the upgrade script for every shard:

    • If AE is On, run the SXP_collection_AE_Part1.sql script.
    • If AE is Off, run the SXP_collection_Part1.sql script.

  • If you are upgrading from Sitecore XP 9.0.x and AE in On, grant permissions and configure encryption for the new [UnlockContactIdentifiersIndex_Staging] table.

    In Sitecore XP 9.1.0, the new [UnlockContactIdentifiersIndex_Staging] table was introduced. The collection user in every shard must be granted SELECT and INSERT permissions for this table.

    To grant the collection user these permissions:

    • Open the SXP_collection_GrantPermissions.sql script and edit it to specify the user name in the following lines:

      
GRANT SELECT ON [xdb_collection].[UnlockContactIdentifiersIndex_Staging] TO [collectionuser]
GRANT INSERT ON [xdb_collection].[UnlockContactIdentifiersIndex_Staging] TO [collectionuser]

    • Run the script.

      If AE is On, in the new [UnlockContactIdentifiersIndex_Staging] table, configure deterministic encryption for the [Identifier] and [Source] columns, for every shard. To simplify the configuration process, SQL Server Management Studio provides an Encryption Wizard.

If you have suggestions for improving this article, let us know!