Preparing for an upgrade

Today I had to upgrade an old DNN 3.2.2 multiportal installation to DNN 4.8.1. There are 3 major areas that changed in the past DotNetNuke versions:

1. New contstraints and unique indexes on Files table
2. DNN Membership provider
3. Consolidation of http handlers in one assembly

In order to smoothly pass the third change, I always do a 2-step upgrade. First to DNN 4.5.5, then to the last available version, 4.8.1 in this case. The consolidation of http handlers happened in DNN 4.6.0.

When upgrading from DNN versions prior to 3.3.x / 4.3.x, the upgrade process will upgrade the data in the DNN Database from almost pure asp.net membership, to the more loosely coupled DNN Membership. Major changes are: switch from unique ApplicationID / Name per portal to one ApplicationId / name per DNN installation, and DNNMembership no longer uses the aspnet roles and profile tables. However, even though all data is upgraded, no essential data is deleted. In order to finalize the upgrade, you can manually delete the old data using the following SQL:

DELETE FROM dbo.aspnet_UsersInRolesDELETE FROM dbo.aspnet_ProfileDELETE FROM dbo.aspnet_RolesDELETE FROM dbo.aspnet_MembershipWHERE ApplicationId <> (SELECT ApplicationId FROM dbo.aspnet_Applications WHERE ApplicationName='DotNetNuke')
DELETE FROM dbo.spnet_UsersWHERE ApplicationId <> (SELECT ApplicationId FROM dbo.aspnet_Applications WHERE ApplicationName='DotNetNuke')

You can run this script from SQL Management Studio, or from Host > SQL (select run as script). NB: Always make a backup of your database prior to executing this SQL!

The reason upgrades fail mostly have to do with the first change: new constraints and unique indexes on the files table. Problems arise from duplicate files in the files table, and files in the files table without a corresponding folder in the folders table. Even though the DNN upgrade sql tries to handle a few cases, in my experience, there are often cases that are not handled. Today i created the following sql which fixed all upgrade issues for me:

ALTER TABLE [dbo].Files ADD FolderID int NOT NULL CONSTRAINT DF_Files_FolderID DEFAULT 0GOUPDATE [dbo].FoldersSET    FolderPath = FolderPath + '/'WHERE  FolderPath <> '' AND RIGHT(FolderPath, 1) <> '/'GOUPDATE [dbo].FilesSET    FolderID = FO.FolderIDFROM   [dbo].Files F, [dbo].Folders FOWHERE F.Folder = FO.FolderPathAND ( ( F.PortalID = FO.PortalID ) OR ( F.PortalID is NULL AND  FO.PortalID is NULL ) )
GOINSERT INTO [dbo].[Folders] (PortalId, FolderPath)
SELECTTOP 100 PERCENT F.PortalId, F.FolderFROM         [dbo].Files F LEFT OUTER JOIN
[dbo].Folders FO ON F.FolderID = FO.FolderIDWHERE     (FO.FolderID IS NULL)
GROUP BY F.Folder, F.PortalIdORDER BY F.PortalId, F.FolderGO-- delete duplicates from folders tableDELETE FROM [dbo].FoldersWHERE FolderId in (
SELECT FolderIdFROM [dbo].Folders FOjoin (SELECT PortalId, FolderPathFROM [dbo].FoldersGROUP BY PortalId,FolderPathHAVING COUNT(*) > 1) DUPON DUP.FolderPath = FO.FolderPath AND (DUP.PortalId = FO.PortalId OR ( FO.PortalID is NULL AND  DUP.PortalID is NULL ))
WHERE FO.FolderId NOT IN 
(SELECT FolderId=MIN(FolderId)
FROM [dbo].FoldersGROUP BY PortalId,FolderPathHAVING COUNT(*) > 1))
GODelete from FilesWHERE FileID in (
SELECT FileIDFROM [dbo].Files Fjoin (SELECT FolderId, FileNameFROM [dbo].FilesGROUP BY folder,FolderId,FileNameHAVING COUNT(*) > 1) DUPon DUP.FolderId = F.FolderID and DUP.FileName = F.FileNameWHERE F.FileID NOT IN
(SELECT FileID = MIN(FileId)
FROM [dbo].FilesGROUP BY folder,FolderId,FileNameHAVING COUNT(*) > 1))
ALTER TABLE [dbo].FilesDROP CONSTRAINT DF_Files_FolderIDGOALTER TABLE [dbo].Files DROP COLUMN FolderId
GO

This script will do the following:

• make sure that all foldernames in the files table exist in the folders table (add if neccesary)
• delete duplicates from folders table
• delete duplicates from files table.

Of course.. there is a risk. Because the final step is to delete duplicate files, links to that files will seize to work. It will be necessary to do a manual check of your site, to see if this happened on your site.

You can run this script from SQL Server management studio, or from Host >SQL (if you are using a non standard databaseowner and/or an object qualifier, you will need to edit this script to meet your needs) and again: Always make a backup of your database prior to executing this SQL!

(this post is crossposted at dotnetnuke.com)