Technical debt is like an overloaded truck, it might function – but it slows you down and exposes you to undue risk.  After (too many!) years of development, I’m finally able to dig in and split the application database structures off of the DotNetNuke system database.  With an entire web application platform built out in OpenWebStudio – that’s a lot of rewriting!  (or not, because I’m too lazy to do that)

Technical Debt

Maybe you inherited it, maybe you created it – but either way, you’re living with it.  It’s not like being a great developer will keep you from creating technical debt because technical debt can arise from a business changing course, leaving an array of unused code to rot in its wake.  Technical debt can arise from being a “good developer” and responding to business needs quickly, building out new features too quickly to build them together in a cohesive fashion.

You might recognize technical debt more easily from the phrase, “we should do this __\_, but we can’t because ____ is in the way.” Technical debt will rear its ugly head during upgrades, future integrations, and the time you open your project after letting it sit for 2 years only to recoil in fright.

[][1]

[][2]

[][3]

**I have a decent amount of technical debt that needs tending.  **Decent as in plenty of, not as in it is ok.

To knock back technical debt takes a certain amount of focus and a willingness to take 3 steps back so you can go 5 steps forward.  This week we’re slaying a big piece of technical debt – the DotNetNuke system database being housed with all of our application data.  We’re talking about 150 tables and over 1000 Open Web Studio modules.

Looking back in time, I can identify 1 other moment when I really should have taken care of this – but looking forward in time, I see no such opportunity.  Today’s the day!

What am I moving?

I created a table to list all of the objects in the database that I want to move.  I populated this table by cruising sys.objects for U, P, V, FN (tables, procedures, views, and scalar functions).  I went through each of those types individually to scan for outliers, but the general insert was:

[sql]
INSERT INTO TEAMIT_APPTABLES
select NAME, 1, TYPE
from sys.objects
where type = ‘P’ AND (NAME LIKE ‘Team%’ OR NAME LIKE ‘STN%’)
ORDER BY NAME
[/sql]

For scalar functions, I prepended ‘dbo.’ to the name in this table.

OpenWebStudio configurations

The table OPENWEBSTUDIO_SETTINGS contains a text column for the JSON configuration, but if you cast it as NVARCHAR(MAX) you can use replace a few times to grab SQL object names and append the new database name.

[sql]

DECLARE @TABLENAME NVARCHAR(100)

DECLARE db_cursor CURSOR FOR
SELECT tablename
FROM TEAMIT_APPTABLES
WHERE CONVERTME = 1

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TABLEname

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE OSS
SET SETTINGVALUE = REPLACE(REPLACE(REPLACE(REPLACE(CAST(SETTINGVALUE AS NVARCHAR(MAX)),’ [DNN_IEX].[dbo].[‘+@TABLENAME+’]’,’ ‘+@TABLENAME),’ [‘+@TABLENAME+’]’,’ ‘+@TABLENAME),’ DNN_IEX.dbo.’+@TABLENAME,’ ‘+@TABLENAME),@TABLENAME,’APP_IEX.dbo.’+@TABLENAME)
FROM OPENWEBSTUDIO_SETTINGS_temp OSS

FETCH NEXT FROM db_cursor INTO @TABLEname
END

CLOSE db_cursor
DEALLOCATE db_cursor

[/sql]

Based on our code conventions, I knew that I could get away with the replacements for no database specified, DNN_IEX.dbo.TABLENAME, and [DNN_IEX].[dbo].[TABLENAME].

Database Data

In SSMS, I am able to bring restore the application database from the DotNetNuke database directly in about 30 minutes.  Our uptime requirements are dependent on the time of year and I am at a point in the year where an hour or 2 of downtime on the weekend during daytime hours is totally reasonable.  I plan for a Friday evening to be able to complete the database restore.

When restoring the whole database, it brings a ton of baggage with it – all the DNN stuff.  Fortunately, INFORMATION_SCHEMA, sys.objects, and sys.sql_modules  can help me clean it up.

Database Procedures

The 200(!) stored procedures are the last item that has to be cleaned up before the DNN site can come back online.  I want to be able to:

1. alter the procedures that I need in the application database to reflect the use of the DNN users and roles tables
2. remove the procedures that I don’t need in the application database

I want these:

[sql]

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES R
LEFT JOIN TeamIT_AppTables AP ON R.SPECIFIC_NAME = AP.TABLENAME
WHERE AP.TYPE = ‘P’ AND R.ROUTINE_TYPE = ‘PROCEDURE’

[/sql]

I don’t want these:

[sql]

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES R
LEFT JOIN TeamIT_AppTables AP ON R.SPECIFIC_NAME = AP.TABLENAME
WHERE AP.TYPE IS NULL AND R.ROUTINE_TYPE = ‘PROCEDURE’

[/sql]

So what does removing the unnecessary procedures look like? (note – it’s easier to create this in SSMS with “results to text”)

[sql]

SELECT
‘USE APP_IEX’+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10)+’DROP PROCEDURE dbo.’+NAME+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10)
FROM SYS.SQL_MODULES R
LEFT JOIN SYS.OBJECTS O ON R.object_id = O.object_id
LEFT JOIN TeamIT_AppTables AP ON O.NAME = AP.TABLENAME
WHERE AP.TYPE IS NULL

[/sql]

For safety’s sake, I included a USE statement before each drop to make sure I wasn’t modifying my original DNN database.

Similarly, the query to DROP and CREATE the handful of procedures that have a true DNN table in them that I need to point back to the DNN database.  The results of this were processed with Notepad++ for a quick find/replace with the database name.

[sql]

SELECT
‘USE APP_IEX’+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10)+’DROP PROCEDURE dbo.’+NAME+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10)+
DEFINITION +CHAR(13) +CHAR(10)+’GO’+
FROM SYS.SQL_MODULES R
LEFT JOIN SYS.OBJECTS O ON R.object_id = O.object_id
LEFT JOIN TeamIT_AppTables AP ON O.NAME = AP.TABLENAME
WHERE AP.TYPE = ‘P’
AND (R.DEFINITION LIKE ‘%USERS%’
OR DEFINITION LIKE ‘%ROLES%’
OR DEFINITION LIKE ‘%USERROLES%’
OR DEFINITION LIKE ‘%USERPORTALS%’)

[/sql]

**
Extra Tables
**
I don’t need to keep the duplicated tables in each database, so I can script a DROP on the extra application tables from the DNN database and the DNN tables from the application database.

[sql]

SELECT ‘USE DNN_IEX’+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10)+’DROP TABLE dbo.’+NAME+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10)
FROM SYS.OBJECTS O
LEFT JOIN TeamIT_AppTables AP ON O.name = AP.TABLENAME
WHERE AP.TYPE = ‘U’ AND O.TYPE = ‘U’

UNION SELECT ‘USE APP_IEX’+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10)+’DROP TABLE dbo.’+NAME+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10)
FROM SYS.OBJECTS O
LEFT JOIN TeamIT_AppTables AP ON O.name = AP.TABLENAME
WHERE AP.TYPE IS NULL AND O.TYPE = ‘U’

[/sql]

Removing these extra tables will not only clean things up from an OCD sense, but it frees up space in the database files and server.

Freeing Up Space

A hot topic is shrinking a SQL database.  For the first time in my relatively short career with SQL Server, I have a legitimate case for shrinking the database(s).  I don’t have the opportunity to add space to the RAID array on this server anytime soon and I do have time to rebuild all of the indexes after the shrink.

Final Execution

The actual execution of these changes took right about 30 minutes, and another 30 minutes for me to go through the warehousing procedures by hand. This was on a Friday, no major issues.

Through similar magic of the previous find and replace in the OWS configs, we also turned on debugging for all users for about 2 business days. We already have built-in interfaces to display recent errors and were able to stay on top of any errant configurations.