Updating an existing database

[This is preliminary documentation and is subject to change.]

This section describes how to update Data Model Version is existing databases - using command line scripts, when a new framework version is put to use.

Note
If the version of the framework is 4.25 or later, it is recommended to use the Administration Module to update data model. This is much easier than using command line scripts.

Updating data model version is typically done when an application is updated to a new framework version. Updating data version is not absolutely necessary - it may be necessary in order to make use of new functionality in the new framework version.

This topic contains the following sections.

Updating existing databases
Update scripts - data model version 4.21 and earlier
Update scripts - data model version 4.22 and later

Updating existing databases

Each new data model version comes with script(s), that may be used to upgrade from the previous data model version. That is:

Data model version 4.22 comes with scripts to upgrade from 4.21 to 4.22.
Data model version 4.21 comes with scripts to upgrade from 4.20 to 4.21.
Data model version 4.20 comes with scripts to upgrade from 4.19 to 4.20.
etc.

So if you need to upgrade an existing database from a data model version older than the previous one, you must upgrade in several steps - one version at a time.

Say you have a database in data model version 4.19, and you need to upgrade it to 4.22 (because you need the new functionality X introduced in 4.22) - then you will have to upgrade in these steps:

Upgrade from 4.19 to 4.20
Upgrade from 4.20 to 4.21
Upgrade from 4.21 to 4.22

Caution
Even if upgrading is done using prefabricated scripts, it may be a complicated process. The upgrade scripts require, that the database being upgraded contains exactly the objects that it is supposed to do, corresponding to its data model version number. If this is not the case, errors in the upgrade process may happen. This could happen if the existing database lacks an object - either because of an error in the scripts that originally built this database, or because these objects have been deleted (or altered) afterwards for some reason.

Caution

Even if upgrading is done using prefabricated scripts, it may be a complicated process. The upgrade scripts require, that the database being upgraded contains exactly the objects that it is supposed to do, corresponding to its data model version number. If this is not the case, errors in the upgrade process may happen. This could happen if the existing database lacks an object - either because of an error in the scripts that originally built this database, or because these objects have been deleted (or altered) afterwards for some reason.

When upgrading to a new release, best practice is as described in Update to New Version

Update scripts - data model version 4.21 and earlier

In release 4.21 and all earlier releases, the tools to upgrade a database from the previous version is one (or a few) SQL-scripts,

Each SQL-script must be run "manually" using SQL Server Management Studio.

There is one script for the "core" data model (excluding add-ons) and one script per add-on (add-ons: DocRef, Blog, Accr).

To locate these scripts, look in folder Database/SQLScripts/<version>/Core/Update.sql (update script for the core data model).

It is the responsibility of the deployer (doing the upgrade) to:

Launch SQL Server Management Studio.
Log in as suitable user (a SQL Server login or using Windows authentication with sufficient permission to create and drop tables etc., and with default schema "dbo").
Set the correct database as current.
Open and run each update script - in designated sequence!

Update scripts - data model version 4.22 and later

In release 4.22, a culture name is specified as a parameter to the scripts creating a new database - see creating new database on SQL Server 2008.

Depending on the culture specified as parameter, the database will contain different objects. For example, the number, name and contents of tables implementing lookup values for patient properties, will depend on culture.

Databases built in data model 4.21 and earlier are meant to be used in danish culture ("da-DK") only. Therefore, upgrading a database in data model 4.21 to data model 4.22 will result in a database containing the same objects as a database built in data model 4.22 for danish culture (from scratch).

As result, the process of updating from 4.21 to 4.22 is more complicated than earlier. For this reason, upgrading is done by running a command file from a command prompt (just like when creating databases).

Open a command prompt and navigate to the SQLServer2008 folder. In this command prompt, enter the command Update (with no parameters). The command prompt will display:

Copy

*******************************************************************************************************
Update - Update TOPICA database from previous data model version to current data model version
*******************************************************************************************************
Missing parameter(s) - expected:
    Server Username Password Database
Server:
    Servername (. for current machine).
Username:
    AdminLogin for the database in question.
Password:
    Password for the AdminLogin for the database in question.
Database:
    Database to update.

That is, the Update command takes 4 parameters:

1	Server	The naming of the server follows the rules used in SQL Server, including sqlcmd: If you are creating a database in the default SQL Server instance on the current computer, use ".". If you are creating a database on a named SQL Server instance on the current computer, use ".\<instancename>". If you are creating a database in the default SQL Server instance on some other computer, use "<servername>". If you are creating a database on a named SQL Server instance on some other computer, use "<servername>\<instancename>".
2	Username	When using Windows integrated authentication, use "*". May be used when you are installing on the current computer, or on some other computer in the same domain as the current computer. When using SQL Server login, enter the name of that login - typically "sa".
3	Password	When using Windows integrated authentication, use "*". When using SQL Server login, enter the password for that login.
4	Database	The name of database to update. Must correspond to an existing database.

Validations:

The specified login/user (specified by username and password) is able to login.
The specified login/user (specified by username and password) has default schema "dbo".
The specified database must exist.
The specified database must have a data model version equal to the data model version immediately before the current one. E.g., when the current data model version number is 4.23, the database to update must be in data model version 4.22.

Examples:

Copy

Update MyServer sa sapassw MyApp

Update the existing database "MyApp", logging in with an SQL Server. The database will (still) be meant for danish culture.

Copy

update MyServer * * MyApp

Update the existing database "MyApp", logging in with Windows authentication. The database will (still) be meant for danish culture.

The update process generates a lot of output in the console window. To check for errors, it may be useful to "pipe" the output to a text file:

Copy

Update MyServer * * MyApp  >update.txt

The generated text file may then be inspected using Notepad or any other text file editor.