Configure the TVP or staging tables execution strategy for the xDB Collection SQL provider

Current version: 10.2

Applies to

xConnect Collection service, xConnect Collection Search service

In Sitecore 9.3 and later, you can choose between two execution strategies for the xDB Collection SQL provider:

  • The TVP (Table-Value Parameters) execution strategy, which was introduced in 9.3 and significantly improves read/write performance. If you use the TVP strategy, you cannot use Always Encrypted.

  • The staging tables executions strategy. You can use staging tables together with Always Encrypted.

In Sitecore 9.3 and later, the TVP strategy is used by default. Sitecore 9.0 - 9.2 use staging tables.

Note

You can and should still combine TVP with TDE (Transparent Data Encryption) and TLS (Transporty Layer Security).

Configure the TVP execution strategy

To configure the TVP execution strategy:

  • In the <webroot>\App_Data\Config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.xml, set the contact, device profile, and interaction execution strategies to TVP:

    RequestResponse
    <Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IContactsExecutionStrategy>
                  <Type>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.ContactsTvpExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</Type>
                  <As>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IContactsExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</As>
                  <LifeTime>Singleton</LifeTime>
              </Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IContactsExecutionStrategy>
              <Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IInteractionsExecutionStrategy>
                  <Type>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.InteractionsTvpExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</Type>
                  <As>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IInteractionsExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</As>
                  <LifeTime>Singleton</LifeTime>
              </Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IInteractionsExecutionStrategy>
              <Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IDeviceProfilesExecutionStrategy>
                  <Type>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.DeviceProfilesTvpExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</Type>
                  <As>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IDeviceProfilesExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</As>
                  <LifeTime>Singleton</LifeTime>
              </Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IDeviceProfilesExecutionStrategy>

Configure the staging tables strategy

If you want to use Always Encrypted with the xDB Collection database in 9.3 and later, you must switch back to the staging tables strategy. To configure the staging tables strategy:

  • In the <webroot>\App_Data\Config\Sitecore\Collection\sc.Xdb.Collection.Data.Sql.xml, set the contact, device profile, and interaction execution strategies to staging tables:

    RequestResponse
    <Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IContactsExecutionStrategy>
                  <Type>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.ContactsStagingTableExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</Type>
                  <As>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IContactsExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</As>
                  <LifeTime>Singleton</LifeTime>
              </Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IContactsExecutionStrategy>
              <Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IInteractionsExecutionStrategy>
                  <Type>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.InteractionsStagingTableExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</Type>
                  <As>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IInteractionsExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</As>
                  <LifeTime>Singleton</LifeTime>
              </Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IInteractionsExecutionStrategy>
              <Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IDeviceProfilesExecutionStrategy>
                  <Type>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.DeviceProfilesStagingTableExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</Type>
                  <As>Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IDeviceProfilesExecutionStrategy, Sitecore.Xdb.Collection.Data.SqlServer</As>
                  <LifeTime>Singleton</LifeTime>
              </Sitecore.Xdb.Collection.Data.SqlServer.Managers.ExecutionStrategy.IDeviceProfilesExecutionStrategy>

Do you have some feedback for us?

If you have suggestions for improving this article,