This book describes the installation of openCRX for MS SQL Server 2005. The book assumes that you are familiar with the installation and configuration of MS SQL Server 2005.

1.3 Tips, Warnings, etc.

We make use the following pictograms:

	Information provided as a “Tip” might be helpful for various reasons: time savings, risk reduction, etc.
	You should carefully read information marked with “Important”. Ignoring such information is typically not a good idea.
	Warnings should not be ignored (risk of data loss, etc.)

2 Prerequisites

As a first step you must ensure that you have a working installation of MS SQL Server(please refer to the appropriate documentation from Microsoft for installation details).

While openCRX supports both MS SQL Server 2000 and 2005, this document is based on MS SQL Server 2005. Differences between the two versions are highlighted in this document.

As an alternative to the full version of MS SQL Server 2005 you can also download and install the Express Edition (free, but with some limitations):

Download MS SQL Server 2005 Express Edition from http://www.microsoft.com/express/

You also need the openCRX distribution for MS SQL Server (this distribution works for both MS SQL Server 2000 and MS SQL Server 2005):

Download and install openCRX SDK Installer. It is available from http://www.opencrx.org/sdk.htm
The SDK contains DB2 scripts required to install the openCRX database in the directory <SDK_Install_Dir>\opencrx-x.x.x\core\src\sql
Download MS SQL Server 2005 JDBC Driver from http://www.microsoft.com/downloads/details.aspx?familyid=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en
You will need the file sqljdbc.jar.

Please ensure that you get the correct JDBC driver (i.e. matching JDK, MS SQL Server version, etc.) and one JDBC driver only! Ignoring this wisdom leads to problems as the connection to the database will fail.

While you can use the JDBC Driver for MS SQL Server 2005 to connect to MS SQL Server 2000 (it is even recommended!), you cannot use the JDBC Driver for MS SQL 2000 to connect to MS SQL Server 2005.

As a next step you must install MS SQL 2005 (please refer to the appropriate documentation from Microsoft for installation details).

This document assumes that you use the Microsoft SQL Server Management Studio Express for database administration.

3 Upgrading from previous versions

If you already have MS SQL for openCRX installed, upgrade the database as explained in this chapter. You can then skip the rest of this document.

	Backup your database before you run any DB scripts!
	Please consult http://www.opencrx.org/faq.htm#upgrade and find out whether there exist specific instructions for your openCRX version. Instructions below are generic and might not cover all steps required to successfully upgrade your openCRX version.
	Please note that you cannot skip versions when upgrading.

3.1 The SQL Script upgrade-from-...

In a first step you must upgrade your database. openCRX distributions provide an SQL script of the form

upgrade-from-<version from>-to-<version to>.sql

If you have installed openCRX 2.0.0, for example, and you want to upgrade to version 2.1.0 you have to run the script upgrade-from-2.0.0-to-2.1.0.sql on your database instance.

3.2 The SQL Script migrate-from-...

In a second step you must migrate your database. openCRX distributions often times provide an SQL script of the form

migrate-from-<version from>-to-<version to>.sql

If you have installed openCRX 2.0.0, for example, and you want to upgrade to version 2.1.0 you have to run the script upgrade-from-2.0.0-to-2.1.0.sql on your database instance.

3.3 The SQL Script drop-from-...

Next you can drop unused tables from your database. openCRX distributions often times provide an SQL script of the form

drop-from-<version from>-to-<version to>.sql

If you have installed openCRX 2.0.0, for example, and you want to drop tables not used by openCRX 2.1.0 you can run the script drop-from-2.0.0-to-2.1.0.sql on your database instance. Alternatively, you can also rename such tables, e.g. from transition_type to _unused_transition_type. Also, it goes without saying that you should never drop a table before you made a backup!

3.4 The SQL Script dbcreate-views.sql

Most new openCRX versions make use of new/changed views, i.e. if an openCRX distribution includes an SQL script of the form

dbcreate-views.sql

then you must run that script. If you have installed openCRX 2.0.0, for example, and you want to upgrade to openCRX 2.1.0 you should run the script dbcreate-views.sql on your database instance. Make sure that old views are indeed dropped and new views properly created.

The script dbcreate-views.sql tries to drop old views before it creates the new ones. In case there are no existing views you need to comment out the drop statement to successfully create new views.

3.5 The SQL Script dbcreate-indexes.sql

Most new openCRX versions make use of new/changed indexes, i.e. if an openCRX distribution includes an SQL script of the form

dbcreate-indexes.sql

then you should run that script. If you have installed openCRX 2.0.0, for example, and you want to upgrade to openCRX 2.1.0 you should run the script dbcreate-indexes.sql on your database instance.

3.6 Populate Preferences

The last step involves deleting old preferences and populating the table with new ones. Run the SQL script populate-preferences.sql to do this.

Make sure that old preferences are indeed removed and news ones loaded. This table contains the configuration of the openMDX database plugin, i.e. openCRX persistency will not work properly if the loaded preferences do not match the version of openCRX.

4 Create the database

As a first step you must create the database. This can be done with the Microsoft SQL Server Management Studio. Start the Management Studio and navigate to the appropriate instance and select Databases. Right-click and select New Database from the pop-up menu as shown below:

Figure 1: Create a new database

Enter CRX_CRX as database name:

Figure 2: Create schema CRX_CRX

Click OK to create the database:

Figure 3: Database CRX_CRX has been created

Next we need to create a new user. Navigate to Security and right-click to bring up the pop-up menu. Select New Login as shown below:

Figure 4: Create New User – step 1

Complete the New Login Dialog as shown below and then click OK:

Figure 5: Create New User – step 2

Make yourself a note of the password as you will need it again for setting up the datasource configuration file (this step is covered in the respective application server installation guide).

Next you navigate back to the Database CRX_CRX and navigate to the subentry Security | Users. Right-click Users to bring up the pop-up menu and select the entry New User as shown below:

Figure 6: Create New User – step 3

Complete the New User Dialog as shown below and then click OK:

Figure 7: Create New User – step 4

Please note that the password of this user are managed by the SQL Server.

You have completed creating the database CRX_CRX.

5 Install the openCRX Database Schema Objects

After creating the database you are now ready to install the openCRX database schema objects. The following scripts must be executed:

dbcreate-tables.sql
dbcreate-views.sql
dbcreate-indexes.sql
populate-preferences.sql

Do not execute any other scripts included in the distribution.

Navigate to the newly created database. Right-click on it and then select the entry New Query from the pop-up menu to open a query window:

Figure 8: Open Query Window

Copy/paste the database script dbcreate-tables.sql and execute by clicking on the button Execute:

Figure 9: Execute script dbcreate-tables.sql

The script must execute without errors. If you get error messages you must investigate and resolve the issue before you continue.

Similarly, execute the remaining scripts in the following order:

dbcreate-views.sql
Hint: you can ignore error messages about views that could not be dropped as the script tries to drop views before creating them.
dbcreate-indexes.sql
populate-preferences.sql

All the scripts should run without errors and after execution you should be able to inspect all the newly created tables, views, and indexes:

Figure 10: Verify creation of tables, views, and indexes

6 Next Steps

If you have completed successfully the database installation you are ready to use the openCRX database CRX_CRX. The application server installation guides explain how to connect the application server to the openCRX database instance.