Tidy up DotNetNuke database tables

Question

I've inherited the maintenance of a DotNetNuke (v6.2.0.1610) site, and one of the things I'd like to do is to tidy up the database tables being used.

It looks like there might have been two installations of DNN into the same database (I'm guessing, I don't know its history and cannot find out), I'm making this assumption because there are two sets of DotNetNuke tables.

For example, we have:
dbo.Portals, dbo.PortalSettings, dbo.Profile, dbo.Roles, etc.

However, then we also have the same set, prefixed with dnn_ -
dbo.dnn_Portals, dbo.dnn_PortalSettings, dbo.dnn_Profile, dbo.dnn_Roles, etc.

I spent a good while tearing my hair out when I could not get our portal to load, when I discovered it is because I was editing the dbo.PortalAlias table and I needed to be editing the dbo.dnn_PortalAlias table instead.

I wanted to avoid this future maintenance headache, so I backed up the database, and set about deleting all the tables without the dnn_ prefix (web.config specifies objectQualifier="dnn_"). I diligently ensured there was a matching dnn_ table before deleting any.

At first it seemed fine - the portal loaded and all the content was there, I thought I was on to a winner. However when I logged in and accessed the site admin section, that's when I started to get lots of error messages. So I figured I'd deleted too much, I restored the backup, and all is well - portal working again.

However, I really would like to get rid of the unnecessary tables, because no doubt at some point in the future I'll start doing some work on the database, forget about the dnn_ prefix and waste a bunch of time wondering why something isn't working.

So, as a bit of a DotNetNuke newbie, I'm after some help - how can I know what tables are in use, what aren't, and how can I set about tidying up the SQL Server tables? Thanks.

Answer 1

It turns out there was a view, dnn_Lists which was still referencing dbo.Lists without the dnn_ prefix.

I fixed this view and it's fine now.
(PS: Turns out that it's useful to set IsSuperUser = 1 in the users table for who you're logged in as, because then you get the full exception details and can fix it.)

Thanks

Answer 2

I suggest you to delete only the tables which have an equivalent with the "dnn_" prefix.
The DNN database should contain at least the "aspnet_" prefixed tables which are used for the authentication on the portals.
Then, you could have some extensions which could use tables without the "dnn_" prefix. It depends on the sql scripts that those extensions have used during their installation. I hope that those extensions don't run queries on the dnn tables without the "dnn_" prefix. Otherwise it could explain the errors you've encountered.
You could use the SQL Server Profiler to check it.

Answer 3

It would make sense to delete all tables WITHOUT "dnn_", but you said you got a problem.

If you have time and patience and is adamant about tiding things up, I would delete 1 table at a time and test the admin feature it broke last time until you find the culprit. That is a long shot, but that is how I would approach.

What might be happening here is that you may have a 3rd module installed that ignores the objectQualifier and when you deleted those tables, you then broke that module.