Upgrade path from 2sxc v11.22.1 to v16

[10PoundGorilla-ChrisBehling]

Hey all, I'm struggling to get a website on Evoq (v9.10.6) to upgrade 2sxc past v15. We are currently on 2sxc v11.22.1 and I've tried a few scenarios of upgrading the module that all get stuck with a SQL error at v15.00/15.02/15.03. I've read the release notes that recommend installing v15.02.00 (instead of 15.00) if you are on a version 14.00 or below, but it still runs into the error.

The last few attempts I've tried have been doing multiple updates:

• Starting at v11.22.1
• v12.08.01
• v13.11.00
• v14.07.04
• v14.12.03
• v15.02.00

I've tried with and without v14.12.03 and still get stuck on v15.02.00. I've tried multiple attempts of v15.02.00. I've tried v15.03.00 as well with no luck.

When running into the issue, the App module shows the latest version, but the Content module remains at the last version before the SQL error (v14.12.03)

[iJungleboy]

So the version differences are because

1. the content fails - so DNN tries to roll-back the installation
2. The App is basically an empty module, just pointing to content, so it "succeds" but doesn't do much

Basically you should be able to just upgrade directly from anything after 9.x to the latest 15 or 16.

Can you give any insights about the SQL which bombs? My guess is something was customized on the site, which changes the tables or something.

[10PoundGorilla-ChrisBehling]

Here is the full install report (screen shot + text variation). This was when attempting to upgrade from v11.22.1 to v15.02.00. This appears to be the same error I am seeing when trying to do incremental upgrades, getting to 14.12.03 and trying to install v15.02.00.

error-log.txt

[iJungleboy]

It appears that SQL is failing on some line.
Since this runs on many systems, it must be something unique to your SQL.

What SQL are you using? Azure or "real"? what version?

[10PoundGorilla-ChrisBehling]

Info pulled from the DNN Database Information:

• Database Version: 12.0.6444.4
• Service Pack: SP3
• Product Edition: Standard Edition (64-bit)
• Software Platform: Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB5021045) - 12.0.6444.4 (X64) Dec 23 2022 00:46:32 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

[iJungleboy]

Based on the code it looks like it's making some important changes to the Timeline-table. My guess is that the table has grown incredibly large in the last few years and times out.

My suggestion is to

1. increase timeout time on SQL
2. flush the timeline table (you lose the history of changes - but it's probably not used much)

We can also support you on this - probably in ca. 2h of paid support - see 2sxc.org

[10PoundGorilla-ChrisBehling]

I tried truncating the ToSIC_EAV_DataTimeline table and running the upgrade to v15.02.00, and it still generates the same error. There is a line in the error "min Sql Server 2016 DROP TRIGGER IF EXISTS".... Does this version of 2sxc and above require SQL Server 2016 and above?

According to the display in this DNN site, it is indicating the site is using SQL Server 2014.

[iJungleboy]

@10PoundGorilla-ChrisBehling I'm not sure, I didn't write the code ;)

Let's ask @tvatavuk

Apparently they have a SQL 14 and are running into problems in the upgrade SQL (see image above) which seems to have a comment min SQL Server 2016. Could you tell us what this is about? and if it's necessary, or if they need some custom support to work around this?

Thx

[tvatavuk]

In SQL Server 2014, there is no native syntax for DROP TRIGGER IF EXISTS and DROP PROCEDURE IF EXISTS, we need to first check if it exists and then drop it. The same goes for ALTER TABLE ... DROP COLUMN IF EXISTS, the IF EXISTS clause is not supported.

You can modify SQL script in ToSic.Sxc.Dnn.16.01.00_Install.zip\SqlDataProvider\15.00.00.SqlDataProvider file and use that modified ZIP in your upgrade process.

Here's your modified SQL script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- make sure sql rolls back automatically in case of error.
SET XACT_ABORT ON
GO

-- 1. remove sql trigger functionality that store data in 'ToSIC_EAV_DataTimeline' table 
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'AutoLogAllChangesToTimeline_EntityRelationships')
    DROP TRIGGER [dbo].[AutoLogAllChangesToTimeline_EntityRelationships];
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'AutoLogAllChangesToTimeline_Values')
    DROP TRIGGER [dbo].[AutoLogAllChangesToTimeline_Values];
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'AutoLogAllChangesToTimeline_ValuesDimensions')
    DROP TRIGGER [dbo].[AutoLogAllChangesToTimeline_ValuesDimensions];
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'ToSIC_EAV_LogToTimeline')
    DROP PROCEDURE [dbo].[ToSIC_EAV_LogToTimeline]
GO

-- 2. first install case (execute when [ToSIC_EAV_DataTimeline] is empty)
IF NOT EXISTS (SELECT * FROM [dbo].[ToSIC_EAV_DataTimeline])
BEGIN
    -- add CJson column to 'ToSIC_EAV_DataTimeline'
    IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = 'CJson' AND Object_ID = OBJECT_ID('ToSIC_EAV_DataTimeline'))
    BEGIN
        ALTER TABLE [dbo].[ToSIC_EAV_DataTimeline] ADD [CJson] varbinary(max) NULL;
    END
    
    -- drop NewData column from 'ToSIC_EAV_DataTimeline'
    IF EXISTS (SELECT * FROM sys.columns WHERE Name = 'NewData' AND Object_ID = OBJECT_ID('ToSIC_EAV_DataTimeline'))
    BEGIN
        ALTER TABLE [dbo].[ToSIC_EAV_DataTimeline] DROP COLUMN [NewData];
    END
END
GO

-- 3. remove columns not in use
IF EXISTS (SELECT * FROM sys.columns WHERE Name = 'SortOrder' AND Object_ID = OBJECT_ID('ToSIC_EAV_AttributeGroups'))
    ALTER TABLE [dbo].[ToSIC_EAV_AttributeGroups] DROP COLUMN [SortOrder];
IF EXISTS (SELECT * FROM sys.columns WHERE Name = 'SortOrder' AND Object_ID = OBJECT_ID('ToSIC_EAV_Entities'))
    ALTER TABLE [dbo].[ToSIC_EAV_Entities] DROP COLUMN [SortOrder];
IF EXISTS (SELECT * FROM sys.columns WHERE Name = 'Description' AND Object_ID = OBJECT_ID('ToSIC_EAV_AttributeSets'))
    ALTER TABLE [dbo].[ToSIC_EAV_AttributeSets] DROP COLUMN [Description];
GO 

This should work with SQL Server 2014. Please make sure you have a backup before running these commands as they permanently alter your database.