Sitecore upgrade from 6.5 to 7 – Sitecore-Item-Buckets

Posted in Sitecore

It’s December and we are in a code freeze period at work, usually these period are used to invest in infrastructure upgrades, version upgrades etc.

So this brings me to blog about our upgrade from Sitecore 6.5 to Sitecore 7.2 – this blog post is specifically to document my findings in regards to upgrading Sitecore’s pre-released Item-buckets to their out of the box implementation in more recent versions.

There were a number of approached we discussed in how to go about the Sitecore-Item-Bucket upgrade, here are the two main ones I remember discussing:

  1. Serialise all item bucket bucket folders and write a parser to upload them after the Sitecore upgrade.
  2. Unbucket folders, perform Sitecore upgrade and rebucket.

We chose to explore the second option.

First thing to do was take a backup of the production and restore it locally. Then configure a local environment to point to the production replica databases.

From this point I could try to understand what was going on under the hood when attempting to unbucket a folder. I was mainly doing this by having SQL Server Profiler turned on and also by taking counts of all the records in each master database table before and after.

 

Unbucketing a media item folder

After unbucking a folder and inspecting the SQL Profiler entries I saw statements that indicated deletes were being performed on media items:

exec sp_executesql N’
INSERT INTO [EventQueue]([Id], [EventType],[InstanceType], [InstanceData], [InstanceName], [RaiseLocally], [RaiseGlobally],[UserName])
VALUES( @id, @eventType, @instanceType, @instanceData, @instanceName, @raiseLocally, @raiseGlobally, @userName)’, N’@id uniqueidentifier,@eventType nvarchar(124),@instanceType nvarchar(124),@instanceData nvarchar(205),@instanceName nvarchar(28),@raiseLocally bit,@raiseGlobally bit,@userName nvarchar(18)’,@id=’9092F727-6156-486E-B30F-D834A098BF3F’,@eventType=N’Sitecore.Data.Eventing.Remote.DeletedItemRemoteEvent, Sitecore.Kernel, Version=6.0.0.0, Culture=neutral, PublicKeyToken=null’,@instanceType=N’Sitecore.Data.Eventing.Remote.DeletedItemRemoteEvent, Sitecore.Kernel,Version=6.0.0.0,Culture=neutral,PublicKeyToken=null’, @instanceData=N'{“ItemId”:”8b3e7972-19df-423e-81e9-16db6d1d4f81″,”ItemName”:”08″,”LanguageName”:”en”,”TemplateId”:”adb6ca4f-03ef-4f47-b9ac-9ce2ba53ff97″,”VersionNumber”:1, “ParentId”:”ca1119c7-302b-4510-ae67-567c444894dd”}’, @instanceName=N’###’,@raiseLocally=0,@raiseGlobally=1,@userName=N’sitecoreAnonymous’
exec sp_executesql N’
DELETE FROM [Items] WHERE [ID] = @itemId
DELETE FROM [SharedFields] WHERE [ItemId] = @itemId
DELETE FROM [UnversionedFields] WHERE [ItemId] = @itemId
DELETE FROM [VersionedFields] WHERE [ItemId] = @itemId’,
N’@itemId uniqueidentifier’, @itemId=’CA1119C7-302B-4510-AE67-567C444894DD’

This lead to investigating the record counts from the various tables in the master database. I found that the bucketing process was deleting content items from the database but it was leaving the actual media file (stored in the blob table) in-tacked in the database.

You can use this query to obtain record counts of all the tables in a db.

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]

image

From this, a script could be created to copy needed records from the backup. The red highlighted tables are the ones that I suspected needed attention and here is the script I pulled together to take data from the backup and create the needed data. Its re-runable, and should allow you to unbucket all need folders and then run the script to re-create everything needed.

NOTE: there is probably a better way to derive the new folder Id for items, it was what I could come up with in the time I had, so please feel free to suggest improvements.
NOTE2: under some situations the unbucketing process worked correctly but under others it didn’t. I didn’t spend time tyring to figure out why.

 



-- Replace with needed GUIDs
DECLARE @ItemBucketTemplateId uniqueidentifier = 'ADB6CA4F-03EF-4F47-B9AC-9CE2BA53FF97'

--******************************************************
-- Manage items table
--******************************************************
INSERT INTO [CCASitecore_Master].[dbo].[Items] 
SELECT i.* 
FROM [CCASitecore_Master_BAK].[dbo].[Items] i
		INNER JOIN [CCASitecore_Master_BAK].[dbo].[Items] parent
			ON i.[ParentID] = parent.[ID]	
WHERE NOT EXISTS (
		SELECT *
		FROM [CCASitecore_Master].[dbo].[Items] newMasterTable
		WHERE newMasterTable.[ID] = i.[ID])
	AND i.[TemplateID] != @ItemBucketTemplateId
 
--******************************************************
-- Fix ParentId for items
--******************************************************
UPDATE newItems
SET ParentId = oldItems.ParentID
FROM [CCASitecore_Master].[dbo].[Items] newItems
	INNER JOIN [CCASitecore_Master_BAK].[dbo].[Items] oldItems
		ON newItems.ID = oldItems.ID
			AND newItems.ParentID != oldItems.ParentID

;WITH Item_ParentFoldersCTE AS 
(
	SELECT ItemsWithDifferentParentIDs.ID 'ItemId', ItemHierarchy.[Ancestor]
	FROM (	SELECT newItems.ID
			FROM [CCASitecore_Master].[dbo].[Items] newItems
				INNER JOIN [CCASitecore_Master_BAK].[dbo].[Items] oldParentItems
					ON oldParentItems.ID = newItems.ParentID 
						AND oldParentItems.TemplateID = @ItemBucketTemplateId
		) ItemsWithDifferentParentIDs
		INNER JOIN [CCASitecore_Master_BAK].[dbo].[Descendants] ItemHierarchy
			ON ItemHierarchy.[Descendant] = ItemsWithDifferentParentIDs.[ID]
		INNER JOIN [CCASitecore_Master_BAK].[dbo].[Items] ItemParents
			ON ItemHierarchy.[Ancestor] = ItemParents.[ID] 
				AND ItemParents.[TemplateID] != @ItemBucketTemplateId
),
Item_ParentFoldersCartesianProductCTE AS
(
	SELECT List1.[ItemId], List1.[Ancestor] AS 'Ancestor1', list2.[Ancestor] AS 'Ancestor2'
	FROM Item_ParentFoldersCTE AS List1
		CROSS JOIN Item_ParentFoldersCTE AS List2
	WHERE List1.[ItemId] = List2.[ItemId]
		AND List1.[Ancestor] != list2.[Ancestor]
), 
Item_ParentFolderHierarchyCountCTE AS
( 
	SELECT cp.ItemId, cp.Ancestor1 AS 'ParentId', count(*) AS 'Item_ParentFolderHierarchyCount_Inverse'
	FROM Item_ParentFoldersCartesianProductCTE cp
		LEFT OUTER JOIN [CCASitecore_Master_BAK].[dbo].[Descendants] d
			ON d.[Ancestor] = cp.[Ancestor1] AND d.[Descendant] = cp.[Ancestor2]
	WHERE d.ID is null
	GROUP BY cp.ItemId, cp.Ancestor1
) 
UPDATE i
SET ParentId = r.ParentId
FROM (	SELECT ItemId, ParentId, RANK() OVER (PARTITION BY cte.ItemId ORDER BY cte.Item_ParentFolderHierarchyCount_Inverse DESC) AS 'Rank'
		FROM Item_ParentFolderHierarchyCountCTE cte
	) r
	INNER JOIN [CCASitecore_Master].[dbo].[Items] i
		ON i.ID = r.ItemId
WHERE r.[Rank] = 1

--******************************************************
-- Manage descendants table
--******************************************************
INSERT INTO [CCASitecore_Master].[dbo].[Descendants]
SELECT DISTINCT d.* 
  FROM [CCASitecore_Master_BAK].[dbo].[Descendants] d
	LEFT OUTER JOIN [CCASitecore_Master].[dbo].[Items] ItemAncestor 
		ON d.Ancestor = ItemAncestor.ID 
	INNER JOIN [CCASitecore_Master].[dbo].[Items] ItemDescendant 
		ON d.Descendant = ItemDescendant.ID 
  WHERE  (ItemAncestor.TemplateID != @ItemBucketTemplateId or d.Ancestor = '00000000-0000-0000-0000-000000000000')
  AND (ItemDescendant.TemplateID != @ItemBucketTemplateId)
  AND NOT EXISTS (
	SELECT *
	  FROM [CCASitecore_Master].[dbo].[Descendants] d2
	  WHERE d2.Ancestor = d.Ancestor 
		AND d2.Descendant = d.Descendant)

--******************************************************
-- Manage SharedFields table
--******************************************************
INSERT INTO [CCASitecore_Master].[dbo].[SharedFields]
SELECT sf.*
  FROM [CCASitecore_Master_BAK].[dbo].[SharedFields] sf
	INNER JOIN [CCASitecore_Master].[dbo].[Items] i
		ON sf.FieldId = i.ID
  WHERE NOT EXISTS ( 
	SELECT *
	  FROM [CCASitecore_Master].[dbo].[SharedFields] sf2
	  WHERE sf2.ItemId = sf.ItemId 
		AND sf2.FieldId = sf.FieldId)

--******************************************************
-- Manage VersionedFields table
--******************************************************
INSERT INTO [CCASitecore_Master].[dbo].[VersionedFields]
SELECT VF.*
  FROM [CCASitecore_Master_BAK].[dbo].[VersionedFields] vf
	INNER JOIN [CCASitecore_Master].[dbo].[Items] i
		ON vf.FieldId = i.ID
  WHERE NOT EXISTS ( 
	SELECT *
	  FROM [CCASitecore_Master].[dbo].[VersionedFields] vf2
	  WHERE vf2.ItemId = vf.ItemId 
		AND vf2.Language = vf.Language
		AND vf2.Version = vf.Version
		AND vf2.FieldId = vf.FieldId)

--******************************************************
-- Manage UnversionedFields table
--******************************************************
INSERT INTO [CCASitecore_Master].[dbo].[UnversionedFields]
SELECT VF.*
  FROM [CCASitecore_Master_BAK].[dbo].[UnversionedFields] vf
	INNER JOIN [CCASitecore_Master].[dbo].[Items] i
		ON vf.FieldId = i.ID
  WHERE NOT EXISTS ( 
	SELECT *
	  FROM [CCASitecore_Master].[dbo].[UnversionedFields] vf2
	  WHERE vf2.ItemId = vf.ItemId 
		AND vf2.Language = vf.Language
		AND vf2.FieldId = vf.FieldId)

 

Unbucketing a content item folder

This was much the same as the media folder unbucketing only that for some reason a far greater amount of items were correctly unbucketed and that there were also records deleted from the UnVersionedFields hence it’s inclusion in the script above.

image

I found the same script worked for content folders as well as media folders.

So now that I have successfully unbucketed all the necessary folders it was time to follow the standard Sitecore upgrade documents.

Published by Kazim Nami