Configure the TVP or staging tables execution strategy for the xDB Collection SQL provider
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.
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>