Upgrading a Database Schema

Notice
CM4D Database schema upgrades should only be performed by a Corporate IT Professional, DBA or a CM4D user that very knowledgable about CM4D databases.

After upgrading a CM4D Installation, you may have databases from one or more schema versions back that you want to access. To access those databases, you need to migrate the databases to the latest schema version using the upgrade path(s) below.

Reference
Database scripts are included with each CM4D Enterprise installation, but you may also download them from our website.

Before you begin the upgrade process, you will need to answer the following questions:

Are you upgrading a Site Database?

1. Yes - Along with upgrading the CM4D Site database schema, you will also need to upgrade any CM4D DataSource databases that are managed by the CM4D Site database to the corresponding CM4D DataSource database Schema.

2. No - Upgrade your unmanaged CM4D DataSource databases to the correct CM4D DataSource database Schema.

What is the version of your current Database(s)?

1. Single-Schema Upgrades - If you are upgrading to the newest version of CM4D from the previous version of CM4D, you will only need to run the latest upgrade script(s) on your database(s).

2. Multiple-Schema Upgrades - If you are upgrading to the newest version of CM4D from a version older than one release, you will need to run all intermediate update scripts on your database(s) consecutively before updating to the latest Schema version. Refer to the Database Schema Reference for a list of versions and schema.

How to Update a Database Schema

Backup Existing Database(s)

Before executing any update scripts on your database(s), it is very important that you create a backup of your existing database(s). Once you have confirmed that the backup process was successful and complete, continue on to the next step.

Notice
You should always test an upgraded copy of your production database(s) in a test environment before upgrading your production databases to a new schema.

Determine Current Database Schema

All Schema upgrades must start with your current database Schema. If you do not know your current Schema, look at the SiteSchemaVersion and SchemaVersion Stored Procedures in your Database.

b. The Schema version will follow the equal sign in single quotes (e.g., '19a').

Run Update Scripts

To upgrade the CM4D Schema version that is used by the new version of CM4D, you must run the appropriate Update scripts in sequential order. The update scripts will be provided along with the CM4D installation when you receive your upgrade.

Notice
If you receive any errors while executing an update script, you must correct the issue(s) before continuing to the next script.

Example
To see an example of upgrading a SQL database from Schema 14a to v19a, click here

Example SQL Database Schema Upgrade

To upgrade the Site Schema version from v14 to v19, run the Update Site scripts on the Site database in the specified order:

1. Run UpdateSite15a.sql to upgrade the Site database Schema from 14a (v14.1) to 15a (v15.1).

2. Run UpdateSite16a.sql to upgrade the Site database Schema from 15a (v15.1) to 16a (v16.1).

3. Run UpdateSite17a.sql to upgrade the Site database Schema from 16a (v16.1) to 17a (v17.1).

4. Run UpdateSite18a.sql to upgrade the Site database Schema from 17a (v17.1) to 18a (v18.1).

5. Run UpdateSite19a.sql to upgrade the Site database Schema from 18a (v18.1) to 19a (v19.1).

6. Run Site Grant.sql to grant the Public role the appropriate privileges on the newly created objects in the Site Schema.

To upgrade the CM4D Schema version from v14 to v19, run the Update scripts on the CM4D database in the specified order:

1. Run Update15a.sql to upgrade the database Schema from 14a (v14.1) to 15a (v15.1).

2. Run Update16a.sql to upgrade the database Schema from 15a (v15.1) to 16a (v16.1).

3. Run Update17a.sql to upgrade the database Schema from 16a (v16.1) to 17a (v17.1).

4. Run Update18a.sql to upgrade the database Schema from 17a (v17.1) to 18a (v18.1).

5. Run Update19a.sql to upgrade the database Schema from 18a (v18.1) to 19a (v19.1).

6. Run Grant.sql to grant the Public role the appropriate privileges on the newly created objects in the CM4D Schema.

Reference
Refer to the topic Database Schema Reference to more information on upgrade paths and versions.

Site Schema Upgrade Path

Use the following upgrade path(s) to upgrade your Site Database(s). Starting with your current Site database Schema version, run each script sequentially until you reach the latest Schema version.

CM4D DataSource Schema Upgrade Path

Use the following upgrade path(s) to upgrade your CM4D DataSource(s). Starting with your current CM4D DataSource database Schema version, run each script sequentially until you reach the latest Schema version.

Check Database Connection

Once your database Schema has been upgraded, check that CM4D can connect to the database.

If you have successfully connected to the database, your Schema upgrade is complete.