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_UsersInRoles

DELETE FROM dbo.aspnet_Profile

DELETE FROM dbo.aspnet_Roles

DELETE FROM dbo.aspnet_Membership
WHERE ApplicationId <> (SELECT ApplicationId FROM dbo.aspnet_Applications WHERE ApplicationName='DotNetNuke')

DELETE FROM dbo.spnet_Users 
WHERE 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 0

GO

UPDATE [dbo].Folders
SET    FolderPath = FolderPath + '/'
WHERE  FolderPath <> '' AND RIGHT(FolderPath, 1) <> '/'

GO

UPDATE [dbo].Files
SET    FolderID = FO.FolderID
FROM   [dbo].Files F, [dbo].Folders FO
WHERE F.Folder = FO.FolderPath
    AND ( ( F.PortalID = FO.PortalID ) OR ( F.PortalID is NULL AND  FO.PortalID is NULL ) )
GO

INSERT INTO [dbo].[Folders] (PortalId, FolderPath)
SELECT     TOP 100 PERCENT F.PortalId, F.Folder
FROM         [dbo].Files F LEFT OUTER JOIN
                      [dbo].Folders FO ON F.FolderID = FO.FolderID
WHERE     (FO.FolderID IS NULL)
GROUP BY F.Folder, F.PortalId
ORDER BY F.PortalId, F.Folder

GO



-- delete duplicates from folders table
DELETE FROM [dbo].Folders
  WHERE FolderId in (
    SELECT FolderId  
    FROM [dbo].Folders FO 
      join (SELECT PortalId, FolderPath
            FROM [dbo].Folders 
            GROUP BY PortalId,FolderPath 
            HAVING COUNT(*) > 1) DUP 
      ON 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].Folders 
         GROUP BY PortalId,FolderPath 
         HAVING COUNT(*) > 1))

GO

Delete from Files 
WHERE FileID in (
    SELECT FileID
    FROM [dbo].Files F 
      join (SELECT FolderId, FileName
            FROM [dbo].Files 
            GROUP BY folder,FolderId,FileName 
            HAVING COUNT(*) > 1) DUP
      on DUP.FolderId = F.FolderID and DUP.FileName = F.FileName
    WHERE F.FileID NOT IN
     (SELECT FileID = MIN(FileId)
            FROM [dbo].Files 
            GROUP BY folder,FolderId,FileName 
            HAVING COUNT(*) > 1))

ALTER TABLE [dbo].Files
    DROP CONSTRAINT DF_Files_FolderID
GO

ALTER 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!

[edit]I edited this post as for some reason the script ended up all garbled[/edit]