Sitecore 9: Mongo Migration Tips

Here's a few tips that will help you survive a large mongodb migration into SQL Server.

Indexes on the SQL Server

I highly recommend creating some indexes on the SQL databases that will help increase the the speed of the migration process.

Indexes for the Verfication database:

create index VerificationLog_TargetType on VerificationLog (TargetType)
create index VerificationLog_Status on VerificationLog (Status)
create index VerificationLog_OperationType on VerificationLog (OperationType)
create index VerificationLog_Source_Target on VerificationLog(SourceIdentifier, TargetIdentifier)

Indexes for the shards:

create index ContactIdentifiers_ContactId on [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard0].xdb_collection.ContactIdentifiers (ContactId)
create index ContactIdentifiers_ContactId on [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard1].xdb_collection.ContactIdentifiers (ContactId)
create index ContactIdentifiers_ContactId on [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard2].xdb_collection.ContactIdentifiers (ContactId)
create index Contacts_ContactId on [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard0].xdb_collection.Contacts (ContactId)
create index Contacts_ContactId on [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard1].xdb_collection.Contacts (ContactId)
create index Contacts_ContactId on [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard2].xdb_collection.Contacts (ContactId)
create index ContactIdentifiersIndex_ContactId on [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard0].xdb_collection.ContactIdentifiersIndex (ContactId)
create index ContactIdentifiersIndex_ContactId on [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard1].xdb_collection.ContactIdentifiersIndex (ContactId)
create index ContactIdentifiersIndex_ContactId on [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard2].xdb_collection.ContactIdentifiersIndex (ContactId)

Here's a query to see the results of a migration. It's useful to compare these numbers to those in MongoDb.

select b.[Started] as BatchStartDate, 
	format(count(vl.id), 'N0') as RecordCount,
	format(sum(case when vl.TargetType = 'Contact' then 1 else 0 end), 'N0') as ContactCount,
	format(sum(case when vl.TargetType = 'DeviceProfile' then 1 else 0 end), 'N0') as DeviceProfileCount,
	format(sum(case when vl.TargetType = 'Interaction' then 1 else 0 end), 'N0') as InteractionCount
	from [Sitecore.DataExchange.Verification].dbo.VerificationLog vl
	left join [Sitecore.DataExchange.Verification].dbo.[Batches] b on vl.BatchId = b.id
	group by b.[Started]

Here's a query that counts the contacts in the shards themselves. It's a nice sanity check against the verification database:

select format(sum(records), 'N0') as TotalContactsInShards from (
select count(*) as records
	from [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard0].xdb_collection.Contacts s0
union all
select count(*) as records
	from [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard1].xdb_collection.Contacts s1
union all
select count(*) as records
	from [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard2].xdb_collection.Contacts s2
) shards

If you need to clear out SQL Server so that you can re-run the migration process, use this script:

use [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard0]
go
DELETE FROM [xdb_collection].ContactIdentifiers
DELETE FROM [xdb_collection].ContactFacets
DELETE FROM [xdb_collection].InteractionFacets
DELETE FROM [xdb_collection].Interactions
DELETE FROM [xdb_collection].ContactIdentifiersIndex
DELETE FROM [xdb_collection].DeviceProfileFacets
DELETE FROM [xdb_collection].DeviceProfiles
DELETE FROM [xdb_collection].Contacts
go
use [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard1]
go
DELETE FROM [xdb_collection].ContactIdentifiers
DELETE FROM [xdb_collection].ContactFacets
DELETE FROM [xdb_collection].InteractionFacets
DELETE FROM [xdb_collection].Interactions
DELETE FROM [xdb_collection].ContactIdentifiersIndex
DELETE FROM [xdb_collection].DeviceProfileFacets
DELETE FROM [xdb_collection].DeviceProfiles
DELETE FROM [xdb_collection].Contacts
go
use [Sitecore.Xdb.Collection.Database.Sql.ShardMapManagerDb.Shard2]
go
DELETE FROM [xdb_collection].ContactIdentifiers
DELETE FROM [xdb_collection].ContactFacets
DELETE FROM [xdb_collection].InteractionFacets
DELETE FROM [xdb_collection].Interactions
DELETE FROM [xdb_collection].ContactIdentifiersIndex
DELETE FROM [xdb_collection].DeviceProfileFacets
DELETE FROM [xdb_collection].DeviceProfiles
DELETE FROM [xdb_collection].Contacts
go