Switching from TDS to Unicorn – some useful verifying SQL scripts

On of my project we switched from TDS to Unicorn. After we configured Unicorn and set up dependencies between configurations I wanted to verify that every item was migrated from TDS (*.item) to Unicorn (*.yml) in the database successfully.

I wanted to check by the following points:

  • Is there any difference between field values?
  • Detect missing items
  • Detect missing field values

First I needed to setup one Sitecore instance for TDS (tds910) and one for Unicorn (unicorn910). I deployed (TDS) and syncronized (Unicorn) the items and then ran the following few scripts.

In most cases I needed to compare 2 tables – SharedFields or VersionedFields. In your case maybe you need to also run the scripts above for UnversionedFields.

Detect difference between field values

I ignored the following fields to give me meaningful results:

  • Updated
  • Revision
  • Created
  • Updated by
  • Owner
  • Created by
  • Lock
  • Blob

You can add more filters or remove these from the script below.

PRINT 'Different values on the same items - Shared Fields'
select tds.[ItemId],
tds.[FieldId],
tds.[Value] as 'TDS Value',
unicorn.[Value] as 'Unicorn Value'
from [tds910_Master].[dbo].[SharedFields] as tds
join [unicorn910_Master].[dbo].[SharedFields] unicorn
on tds.ItemId = unicorn.ItemId
and tds.FieldId = unicorn.FieldId
and tds.FieldId != 'D9CF14B1-FA16-4BA6-9288-E8A174D4D522' /* __Updated */
and tds.FieldId != '8CDC337E-A112-42FB-BBB4-4143751E123F' /* __Revision */
and tds.FieldId != '25BED78C-4957-4165-998A-CA1B52F67497' /* __Created */
and tds.FieldId != 'BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A' /* __Updated by */
and tds.FieldId != '52807595-0F8F-4B20-8D2A-CB71D28C6103' /* __Owner */
and tds.FieldId != '5DD74568-4D4B-44C1-B513-0AF5F4CDA34F' /* __Created by */
and tds.FieldId != '001DD393-96C5-490B-924A-B0F25CD9EFD8' /* __Lock */
and tds.FieldId != '40E50ED9-BA07-4702-992E-A912738D32DC' /* Blob */
and tds.Value != unicorn.Value
PRINT 'Different values on the same items - Versioned Fields'
select tds.[ItemId],
tds.[FieldId],
tds.[Language],
tds.[Value] as 'TDS Value',
unicorn.[Value] as 'Unicorn Value'
from [tds910_Master].[dbo].[VersionedFields] as tds
join [unicorn910_Master].[dbo].[VersionedFields] unicorn
on tds.ItemId = unicorn.ItemId
and tds.Language = unicorn.Language
and tds.FieldId = unicorn.FieldId
and unicorn.Value != 'sitecore\unicorn'
and tds.FieldId != 'D9CF14B1-FA16-4BA6-9288-E8A174D4D522' /* __Updated */
and tds.FieldId != '8CDC337E-A112-42FB-BBB4-4143751E123F' /* __Revision */
and tds.FieldId != '25BED78C-4957-4165-998A-CA1B52F67497' /* __Created */
and tds.FieldId != 'BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A' /* __Updated by */
and tds.FieldId != '52807595-0F8F-4B20-8D2A-CB71D28C6103' /* __Owner */
and tds.FieldId != '5DD74568-4D4B-44C1-B513-0AF5F4CDA34F' /* __Created by */
and tds.FieldId != '001DD393-96C5-490B-924A-B0F25CD9EFD8' /* __Lock */
and tds.FieldId != '40E50ED9-BA07-4702-992E-A912738D32DC' /* Blob */
and tds.Value != unicorn.Value

Detect missing items

This is quite obvious: I would like to have the list of items which are in TDS but not in Unicorn accidentally.

PRINT 'Items which are in TDS but not in Unicorn'
select tds.ID as 'TDS ItemId',
unicorn.ID as 'Unicorn ItemId'
from [tds910_Master].[dbo].[Items] as tds
left join [unicorn910_Master].[dbo].[Items] unicorn
on tds.ID = unicorn.ID
where unicorn.ID is null
view raw missing-items.sql hosted with ❤ by GitHub

Detect missing field values

This one also quite obvious: I would like to have the list of field which are in TDS but not in Unicorn accidentally.

PRINT 'Fields values which are in TDS but not in Unicorn - Shared Fields'
select tds.[ItemId],
unicorn.[ItemId],
tds.[FieldId],
tds.[Value] as 'TDS Value',
unicorn.[Value] as 'Unicorn Value'
from [tds910_Master].[dbo].[SharedFields] as tds
left join [unicorn910_Master].[dbo].[SharedFields] unicorn
on tds.ItemId = unicorn.ItemId
and tds.FieldId = unicorn.FieldId
where unicorn.FieldId is null
and tds.FieldId != 'D9CF14B1-FA16-4BA6-9288-E8A174D4D522' /* __Updated */
and tds.FieldId != '8CDC337E-A112-42FB-BBB4-4143751E123F' /* __Revision */
and tds.FieldId != '25BED78C-4957-4165-998A-CA1B52F67497' /* __Created */
and tds.FieldId != 'BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A' /* __Updated by */
and tds.FieldId != '52807595-0F8F-4B20-8D2A-CB71D28C6103' /* __Owner */
and tds.FieldId != '5DD74568-4D4B-44C1-B513-0AF5F4CDA34F' /* __Created by */
and tds.FieldId != '001DD393-96C5-490B-924A-B0F25CD9EFD8' /* __Lock */
and tds.FieldId != '40E50ED9-BA07-4702-992E-A912738D32DC' /* Blob */
PRINT 'Field values which are in TDS but not in Unicorn - Versioned Fields'
select tds.[ItemId],
unicorn.[ItemId],
tds.[FieldId],
tds.[Value] as 'TDS Value',
unicorn.[Value] as 'Unicorn Value'
from [tds910_Master].[dbo].[VersionedFields] as tds
left join [unicorn910_Master].[dbo].[VersionedFields] unicorn
on tds.ItemId = unicorn.ItemId
and tds.FieldId = unicorn.FieldId
and tds.Language = unicorn.Language
where unicorn.FieldId is null
and tds.Value != ''
and tds.FieldId != 'D9CF14B1-FA16-4BA6-9288-E8A174D4D522' /* __Updated */
and tds.FieldId != '8CDC337E-A112-42FB-BBB4-4143751E123F' /* __Revision */
and tds.FieldId != '25BED78C-4957-4165-998A-CA1B52F67497' /* __Created */
and tds.FieldId != 'BADD9CF9-53E0-4D0C-BCC0-2D784C282F6A' /* __Updated by */
and tds.FieldId != '52807595-0F8F-4B20-8D2A-CB71D28C6103' /* __Owner */
and tds.FieldId != '5DD74568-4D4B-44C1-B513-0AF5F4CDA34F' /* __Created by */
and tds.FieldId != '001DD393-96C5-490B-924A-B0F25CD9EFD8' /* __Lock */
and tds.FieldId != '40E50ED9-BA07-4702-992E-A912738D32DC' /* Blob */

If you would switch from Unicorn to TDS, you can use the same scripts, you just need to change the table and alias names in scripts above. I hope it helps a bit! If you have any question or remark let me know!

3 thoughts on “Switching from TDS to Unicorn – some useful verifying SQL scripts

  1. Why use something you don’t already use daily as a Sitecore dev? Once you serialized your stuff with unicorn and reserialized it on a fresh instance, you can just connect tds to that instance and you should see no changes when the sync is complete. Your approach is probably working just fine, but in the future the DBs might change and TDS should be updated to handle it if it needs to.

    Like

  2. Why use something you don’t already use daily as a Sitecore dev? Once you serialized your stuff with unicorn and reserialized it on a fresh instance, you can just connect tds to that instance and you should see no changes when the sync is complete. Your approach is probably working just fine, but in the future the DBs might change and TDS should be updated to handle it if it needs to.

    Like

Leave a Reply to Jan Bühler Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s