Using Microsoft SQL Server 2005 Instead of Ketura’s Built-In Database

Ketura stores all of its data in its own, built-in, database. The built-in database is perfectly suitable for the majority of Ketura deployments and, in most cases, there is therefore no need to consider using an alternative. However, an organization might still wish to use an alternative database if, for example:

• It already has a database server infrastructure and wishes to use this in preference to the built-in Ketura database so that database administration activities (backup, restore, etc) can be managed centrally;
• It wishes to make the data in the Ketura database directly available (read-only) to other applications;
• The Ketura database is very large (many gigabytes) or the Ketura system has many users, and it is suspected that using a dedicated database server might give better performance.

To address these concerns, Ketura can be configured to work with Microsoft SQL Server 2005 (SP2 or higher) or Microsoft SQL Server 2000 (SP4 or higher).

Caution

Configuring Ketura to work with a third-party database system is an advanced operation, and should only be attempted by someone thoroughly familiar with system and database administration. Araxis recommends that most customers use Ketura’s built-in database.

Please note that Araxis is not able to support customers in administering third-party databases.

Prerequisites

Assuming that you have the necessary expertise, you should verify the following before attempting to migrate a Ketura system to use SQL Server:

• That you have access to the Ketura system’s admin account;
• That Ketura is currently using its built-in database;
• Your chosen version of Microsoft SQL Server is installed on a machine accessible to the computer upon which Ketura is installed;
• If SQL Server 2005 is installed, SQL Server 2005 SP2 or later is also installed; or if SQL Server 2000 is installed SQL Server 2000 SP4 or later is also installed;
• That the SQL Server installation is configured to use Mixed Mode authentication (see How to Set Up Mixed Mode Security  for details);
• That the SQL Server installation is configured to support TCP/IP networking;
• That you know the hostname and port number required to contact the SQL Server installation;
• That you know the administrative username and password of the SQL Server installation.
• That you have read through and understand the step-by-step instructions below.

Step-by-step instructions

1. Download and install the Microsoft JDBC driver

Ketura uses a JDBC driver to communicate with a database. Microsoft provides a suitable JDBC driver for SQL Server.

To download the Microsoft JDBC driver:

• Visit Microsoft SQL Server 2005 JDBC Driver website  and download the latest SQL Server 2005 Driver for JDBC. At the time of writing, the latest driver available that Araxis has tested is Microsoft SQL Server 2005 JDBC Driver 1.2 . This driver can be used to access SQL Server 2005 or SQL server 2000.
• Follow Microsoft’s instructions for installing the driver.

To make the Microsoft JDBC driver available to Ketura:

• Locate the file sqljdbc.jar in the directory into which the Microsoft JDBC driver was installed.
• For Windows, copy the aforementioned file into <Ketura install directory>\WebApps\Ketura\WEB-INF\lib.

• For Mac OS X, copy the aforementioned file into /Applications/Araxis Ketura Manager.app/Contents/Resources/Araxis/WebApps/Ketura/WEB-INF/lib.

  To display the directories inside the Araxis Ketura Manager application, locate the Araxis Ketura Manager application icon in the Applications folder using Finder. Then Ctrl-click or right-click on the icon and select Show Package Contents from the menu.

2. Create the new SQL Server Ketura database

Before the database backup that you have made can be imported into SQL Server, a minimal SQL Server Ketura database must first be created. Ketura comes with a command-line utility to do this.

To create the new SQL Server database on Windows:

• Open a command prompt and change to the Ketura installation directory.

• Run the following command (indentation shows a continuation of the line):

  CreateSqlDb --adminpw=<AdminPassword>
    --dbname=<DatabaseName>
    --dbuser=<DatabaseUsername>
    --dbpw=<DatabasePassword>
    --hostname=<Host>
    --port=<PortNumber>

  where:

  <AdminPassword>	The password of the admin account in the Ketura database that will be created.
  <DatabaseName>	The name of the database to be created. For example, Ketura.
  <DatabaseUsername>	The username of an account on your SQL Server DBMS that is authorized to create new databases. For example, sa.
  <DatabasePassword>	The password associated with the username you have specified.
  <Host>	The name of the machine hosting the SQL Server DBMS. For example, localhost.
  <PortNumber>	The port on the host machine on which the SQL Server instance is listening. For example, 1433.

  The database creation utility will take a few moments to complete. A new database with the given name will then exist in SQL Server.

To create the new SQL Server database on Mac OS X:

• Start the Terminal application, found in the Utilities folder in the Applications folder.

• In the Terminal window, enter and run the following two commands (indentation shows a continuation of the line; press Return after each command):

  cd /Library/Araxis/Ketura/Script

./CreateSqlDb.sh <AdminPassword>
    <DatabaseName>
    <DatabaseUsername>
    <DatabasePassword>
    <Host>
    <PortNumber>

  where:

  <AdminPassword>	The password of the admin account in the Ketura database that will be created. Use "" if no password exists.
  <DatabaseName>	The name of the database to be created. For example, Ketura.
  <DatabaseUsername>	The username of an account on your SQL Server DBMS that is authorized to create new databases. For example, sa.
  <DatabasePassword>	The password associated with the username you have specified. Use "" if no password exists.
  <Host>	The name of the machine hosting the SQL Server DBMS. For example, localhost.
  <PortNumber>	The port on the host machine on which the SQL Server instance is listening. For example, 1433.

  The database creation utility will take a few moments to complete. A new database with the given name will then exist in SQL Server DBMS.

3. Add a new SQL Server database login for Ketura to use

For security reasons, it is undesirable for Ketura to use a highly privileged account when accessing a SQL Server DBMS; there may be other databases in the DBMS and Ketura has no need to access them. Consequently, it is necessary to create a user account in the SQL Server DBMS for Ketura to use.

Background information about adding SQL Server logins is available in the articles How to: Create a SQL Server (2005) Login  and How to Add a SQL Server (2000) Login  on the Microsoft website.

To create a new SQL Server login for use by Ketura:

• On a machine hosting SQL Server 2005, run Microsoft SQL Server Management Studio. On the machine hosting SQL Server 2000, run Enterprise Manager.
• In the tree view, select the server group containing the newly created Ketura database. Navigate to Logins under Security.
• Create the new login here. Be sure to specify SQL Server Authentication (that is, Mixed Mode). Choose a username, and a good password that isn’t used for anything else.
• The default database for the login should be set to the newly created Ketura database, not the master database.
• Ensure that the new login has no Server Roles, other than the default public.
• Grant the login access to the new Ketura database only, with db_owner as the database role. This configuration ensures that no other databases on the server can be accessed by the new login.

4. Backup your existing Ketura database

To migrate the database to SQL Server, a backup of the existing Ketura database must first be created. This backup will be used to populate the new SQL Server database, so it is essential that it be absolutely up to date.

To backup the Ketura database in preparation for the migration:

• Instruct all Ketura users to refrain from using the system until further notice. You might like to enforce this by, for example, disconnecting the Ketura server from the network.

  This step is necessary to ensure that users do not make changes to the Ketura database after you have taken the backup that will be used for the migration.

• Log into Ketura as admin user (or another user with permission to backup the system).
• Browse to the Database Backup and Restore page and perform a backup. For further information about backing up a Ketura system, see Backing Up a Ketura System.
• Record the location and name of the newly created backup file, as these will be needed later in the migration.

5. Stop the Ketura server

Before continuing, the Ketura server must be stopped.

To stop the Ketura service on Windows:

• Run the Windows Services administrative tool, from the Administrative Tools folder in the Windows Control Panel.
• Locate and select the service with the name Araxis Ketura yyyy.xxx, where yyyy.xxx is the Ketura version number of the Ketura installation whose database you are migrating.
• Stop the service.

To stop the Ketura server on Mac OS X:

• Run the Araxis Ketura Manager application, located in the Applications folder.
• Select the Status tab. If the server is running click the Stop Server button.

6. Configure Ketura to use the new SQL Server database

Ketura now needs to be told to use the newly created database in SQL Server.

To configure Ketura to use SQL Server as its database:

• Take a copy of the file <Ketura install directory>\Ketura\WEB-INF\web.xml (on Windows) or /Applications/Araxis Ketura Manager.app/Contents/Resources/Araxis/WebApps/Ketura/WEB-INF/web.xml (on Mac OS X). You will be modifying this file. If, for some reason, your changes do not work, you can use the copy to revert this file back to its original state.
• Open the original web.xml file (not your copy) in your preferred text editor.

• Locate the following lines:

  <context-param>
    <param-name>DatabaseClassName</param-name>
    <param-value>com.araxis.application.db.MckoiDatabaseImpl</param-value>
    <description>The name of the database abstraction class to be used by the application.</description>
</context-param>

• In the above text, change MckoiDatabaseImpl (shown above in red) to MSJDBCDatabaseImpl.
• Find the line <param-name>DatabaseName</param-name>. Change the line following to <param-value>DbName</param-value>, where DbName is the name of your new SQL Server database, as given to CreateSqlDb.
• Find the line <param-name>DatabaseUser</param-name>. Change the line following to <param-value>DbUser</param-value>, where DbUser is the username of the SQL Server login you created.
• Find the line <param-name>DatabasePassword</param-name>. Change the line following to <param-value>DbLoginPassword</param-value>, where DbLoginPassword is the password of the SQL Server login you created.

• Find the line <param-name>DatabaseURL</param-name>. Change the line following to (indentation shows a continuation of the line):

  <param-value>
    jdbc:sqlserver://<DbHost>:<DbPort>;
    DatabaseName=<DbName>;SelectMethod=cursor
</param-value>

  where:

  <DbHost>	The name of the machine hosting the SQL Server DBMS. For example, localhost.
  <DbPort>	The port on the host machine on which the SQL Server instance is listening. For example, 1433.
  <DbName>	The name of the Ketura SQL Server database to be created. For example, Ketura.

  A completed URL string may look something like:

  jdbc:sqlserver://localhost:1433;DatabaseName=Ketura;SelectMethod=cursor

7. Configure service dependencies (Windows only)

To ensure that Ketura is able to communicate with the SQL Server database when it is first started (for example, following a reboot), it is necessary to make certain that the SQL Server DBMS is started before the Ketura service. If the DBMS is running on a separate machine from Ketura, a system administrator will have to enforce this manually, by checking that the database server is available before attempting to start Ketura. However, if a single machine is hosting Ketura and its SQL Server database, it is possible to configure the Ketura service to be dependent upon the SQL Server service, thus ensuring the correct startup order.

To make the Ketura service dependent upon the SQL Server service (only if Ketura and SQL Server are on the same machine):

• Start the Windows Registry Editor by running regedt32.
• Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\.
• Select the key with the same name as the Ketura service that you stopped earlier (of the form Keturayyyyxxxx).
• Under that key, add a new Multi-String Value named DependOnGroup to the key. Leave its value empty.
• Add another new Multi-String Value named DependOnService to the key. Change the value to: MSSQLSERVER, where MSSQLSERVER is the default name of the Microsoft SQL Server service. If the relevant SQL Server instance has been installed with an alternative service name, use that name instead of MSSQLSERVER.
• Close the registry editor.
• Check that the SQL Server service appears in the Dependencies tab of the Ketura service's properties, in the Windows Services administrative tool.

8. Start the Ketura server

Ketura must now be started. All being well, it will now use the SQL Server database that you created.

To start the Ketura service on Windows:

• Using the Windows Services administrative tool, find and start the Ketura service that you stopped earlier.
• Check that the service has started before proceeding. If it fails to start, it might be possible to find diagnostic information in the log files contained in the <Ketura install directory>\Tomcat\logs directory.

To start the Ketura server on Mac OS X:

• Run the Araxis Ketura Manager application, located in the Applications folder.
• Select the Status tab. If the service is not running click the Start Server button.
• Check that the service has started before proceeding. If it fails to start, it might be possible to find diagnostic information in the log files contained in the /Library/Logs/AraxisKetura folder.

9. Restore the database backup

The final step in the migration is to restore the database from the backup you made at the start.

To restore the database:

• Log on to Ketura as the admin user, using the admin account password that you specified when you created the Ketura SQL Server database with the CreateSqlDb command.
• Go to the Database Backup and Restore page and select the Restore tab.
• Select the backup that you made earlier from the list of backups, then click the Restore Database... button.
• When prompted, confirm that you wish to continue by entering the password of the admin account with which you are currently logged on (that is, the account password that you specified when you created the Ketura SQL Server database with the CreateSqlDb command). Click Restore Database to proceed.
• Wait while the database is restored. This may take some time, possibly tens of minutes for a very large database or a slow machine.
• Once the restore has completed, check that the Ketura database is as you expect. All being well, the system is now ready again for use.

Backing up a Ketura SQL Server database

The built-in Ketura backup and restore mechanism works with all the databases that Ketura supports. However, if you are using a SQL Server database with Ketura, you may wish to rely upon your SQL Server backup procedures for day-to-day backup and disable the daily built-in Ketura backup. This will be especially advisable if Ketura is installed on a separate machine from the SQL Server DBMS; in such situations, the built-in Ketura backup mechanism is likely to be very inefficient as it will have to transfer the entire contents of your Ketura database over the network each time it runs.

Upgrading a Ketura system that uses a SQL Server database

Please see Upgrading an existing Ketura installation configured to use SQL Server for important information about upgrading a Ketura system that has been configured to use a SQL Server database.