Database Upgrade Paths

Follow this chart when planning your Cora SeQuence upgrade.

• Pre-upgrade actions: You need to perform specific tasks before upgrading your database. These tasks can be running SQL scripts or performing manual actions.
  For example, before upgrading the database to V9.x, you need to do some cleanup.
• Post-upgrade actions: You need to perform additional tasks after deploying the database. These tasks are not mandatory for the current version but need to be executed before upgrading to the next version.
  For example, some Email Listener's tables changed in V9.3. So, V9.3 systems can work with the previous tables or the changed ones, but V9.4 systems will work only with the changed tables.

After you upgrade to V10.7.0

To migrate the data in tblLog from the “Process Wall” format to the new “Audit Log” format, you may run the scripts mentioned below after the upgrade is complete.

This migration of data is optional. If skipped, then the audit log data for instances created before the upgrade will not display correctly. Consider skipping this step if you didn't use the Process Wall prior to the upgrade, and don't intend to use the “Audit Log” afterward.

Procedure

1. Download the post-deployment scripts from the repo under CoraSeQuenceFiles/Database/V10.7.0.
   For large databases, these scripts can execute for a long time.
   Consider to run the scripts after the down-time window (and as soon as possible after the upgrade is complete).
2. Run the post-deployment scripts in the following order:
   1. MigrationScript_tbllog.sql
   2. MigrationScript_openInstances.sql 
   3. MigrationScript_closedInstances.sql (Optional, for migrating process wall events of instances in the closed tables).
      For scripts b and c, you need to set up the cut-off migration date at the top of the scripts (in the variable "@UserDate").

Before you upgrade to V10.3.1

To upgrade to V10.3.1 from any previous version, you must upgrade to V10.3 first.

Download the 10.3 dacpac file here. 

After you upgrade to V10.3

After deploying V10.3 (new installation or upgrade), you need to run the following.

• AddPReviewToExistingConversationITems script to add a preview line to the already existing conversation item headers.

DECLARE @Take int = 2
DECLARE @Skip int = 0
DECLARE @maxChars int = 255
CREATE TABLE #TempPreviewBodyTable (fldId int)
INSERT INTO #TempPreviewBodyTable
SELECT fldId FROM tblWorkflowInstanceConversations c
CROSS APPLY OPENJSON(c.fldProperties) j
WHERE j.[key] = 'PreviewBody'
DECLARE @totalLen int= (select count(*) from tblWorkflowInstanceConversations
                        where fldId not in (select fldId from #TempPreviewBodyTable )
                        )
WHILE @Skip < @totalLen 
BEGIN
    update tblWorkflowInstanceConversations
    set fldProperties = JSON_MODIFY(fldProperties, '$."PreviewBody"',
                                    iif(LEN(ISNULL(fldBody, ''))> @maxChars,LEFT( [dbo].[fnRT_StripHTML](ISNULL(fldBody, '')), @maxChars) + '...' 
                                   ,[dbo].[fnRT_StripHTML](ISNULL(fldBody, ''))))
    where fldId not in (select fldId from #TempPreviewBodyTable
                        ORDER BY fldId OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY )
    set @Skip = @Skip + @Take
END
DROP TABLE #TempPreviewBodyTable

• Delete unused languages from [tblLanguages]

delete from tblLanguages
where fldCulture in ('he-IL','ja-JP','nl-NL','pt-PT');

• Replace unused languages in [tblemployees] with a supported language. For example, British English (en-GB).

update tblemployees
set fldLanguage = 'en-GB'
where fldLanguage in ('he-IL','ja-JP','nl-NL','pt-PT');

After you upgrade to V10.2

After deploying V10.2 (new installation or upgrade), you need to run the following SQL scripts.

• Delete unused languages from [tblLanguages]

delete from tblLanguages
where fldCulture in ('fr-FR','he-IL','ja-JP','nl-NL','pt-PT');

• Replace unused languages in [tblemployees] with a supported language. For example, British English (en-GB).

update tblemployees
set fldLanguage = 'en-GB'
where fldLanguage in ('fr-FR','he-IL','ja-JP','nl-NL','pt-PT');

Before you upgrade to V9.9

When you upgrade from V9.8, follow this upgrade path: V9.8.3 → V9.8.4.

Before you upgrade to V9.8

To upgrade to V9.8 from any previous version, you must upgrade to V9.7.1 first.

Download the 9.7.1 dacpac file here.

After you upgrade to V9.5 and V9.5.1

To enable old Email Listener activities to use unified email listener tables tblEmailListenerMessages and tblEmailListenerMessageAttachments instead of UACT tables, download and run the Email Listener post-deployment scripts in the following order:

1. UnifiedEmailListenerMigrationScript.sql
2. PostEmailListenerDatamodelMigrationScript.sql
3. MoveIndexesToRightFilegroups.sql

Download the post-upgrade scripts here.

After you upgrade to V9.4

For the archiving database, configure to run PartitionMaintenancePlan.sql script every month to create an additional partition.
For details on how to configure, see the Deploy archive database section in this article.
In addition, you may run PROCEDURE [dbo].[spArchiving_AddBulkWorkflowInstanceToExclusionList] script to add in bulk, the list of workflow instances to exclude from archiving.

Download the post-upgrade script here.

After you upgrade to V9.3

Run a post-upgrade script that changes the fldrequest and fldresponse column types of the UACT tables from XML to NVARCHAR(MAX). The time required to run the script depends on the number of UACT tables in your database and their size. To estimate the overall time required, run the script on a copy of the production database.

Download the post-upgrade script here.

After you upgrade to V9.0

Download and run the post-upgrade script.