Scope

This document is intended for system administrators who wish to perform an OpenLM database migration from Firebird to MS SQL. It elaborates the process and tools required for such a migration. This document is closely related to the
“Guide to OpenLM Data Migration Tool”,
“Upgrade guide of OpenLM Version 1.6 to Version 1.7”,
“OpenLM Database Configuration Form”  and
“Backing Up Your OpenLM DB”
documents, which are recommended for further reading.

Backup the current OpenLM database

It is good practice to begin with backing up the OpenLM database prior to migrating its content. In order to do so:
1. Navigate to the windows services (you can do that by searching for services.msc in the start menu’s search line).
2. Select the “OpenLM Server” service, and Click the Stop icon.

3. Backup the OpenLM database (typically located on:
C:\Program Files\OpenLM\OpenLM Server\db). Please refer to the “Backing Up Your OpenLM DB” document for a more detailed explanation.

Creating a new SQL Server Database: Building the tables

The first stage in migrating the OpenLM database is to create new empty database with OpenLM tables in SQL Server. In order to do so, OpenLM supplies dedicated DDL scripts (*.SQL files) to be applied in the SQL Server application.

1. The DDL scripts for the SQL Server is available for for download on the OpenLM site.

After filling in your name and email, you would be directed to the download section. Select the OpenLM Version 1.7 section:

2. Now choose the DDL scripts section:
The Downloaded file contains two files for MS-SQL, and 2 for Oracle servers. In this document we would make use of the MS-SQL files.

3. Open the Microsoft SQL Server Management Studio
(Start → All Programs →  Microsoft SQL Server → SQL Server Management Studio).

4. Create a new DB: Right click the  “Databases” icon. select  “New Database” from the pop-up menu, as shown below.

5. Enter a new name for the database (e.g.: db_name) and click OK.

6. Drag the “OpenLM_SQLServer.SQL” file from the downloaded directory (e.g.: ddl_170) over to the Microsoft SQL Management Studio window. The Contents of “OpenLM_SQLServer.SQL” is displayed. (The file can also be opened by clicking “File → Open → File…” and selecting “OpenLM_SQLServer.SQL” from the unzipped folder).

7. Enter the new database name after the word “use” as shown below (e.g.: db_name) and click “Execute”

8. Wait until you see the message  “Query executed successfully” as shown below:

Data Migration

Now we get to the actual migration of data from Firebird to SQL Server.

1. Make sure that the original (Firebird) database is backed up, according to the Backup the current OpenLM database section, above.

2. Open the “OpenLM Data Migration” tool.

3. On the Source frame:

a. Select “Firebird”. The “Database Credentials” window appears.

b. Enter the Firebird database path in the appropriate text box.

c.Select your database(*.FDB) and enter the user name and password. By default, the user name is set to ‘sysdba’ and the password is ‘masterkey’

4. Target frame actions:

There are two alternatives for configuring the target frame. One is set for users who use the “Windows Authentication mode”. The 2nd alternative is for users who use the “SQL Server Authentication mode”.

For “Windows Authentication mode”:

a. Select the “MS SQL Server” radio button. The “Database Credentials” window appears.

b. Check the “Integrated security” box and click the “Test connection“ button. A  “Connection successful” message box appears upon completion of the test.

c. Select the database name (i.e. db_name from the “..building the tables” section above) from the “Databases” drop down menu, and click OK.

For “SQL Server Authentication mode”:

a. Select the “MS SQL Server” radio button. The “Database Credentials” window appears

b. Check the “Integrated security” box.

c. Enter the User ID and password as configured in the SQL server.

d. Click the “Test connection” button. A  “Connection successful” message box appears upon completion of the test.

e. Select the database name (i.e. db_name from the “..building the tables” section above) from the “Databases” drop down menu, and click OK.

5. After configuring the Source and Target databases, the migration process may begin. Click the “Transfer” button. At the end of the migration process, a notifier window appears.

Please refer to the “Guide to OpenLM Data Migration Tool” document for further information.

Configuring Openlm to work with the SQL Server

Now the OpenLM server must be made to reference the new SQL Server.
1. Open the “OpenLM Database Configuration” tool:
(Start → All Programs → OpenLM → Server → OpenLM Database configuration)

.

2. Click the DB provider drop-down menu. Choose one of the options: “SQL Server Authentication” or “Windows Authentication mode”. The “OpenLM Database Configuration” window changes accordingly:

3. Fill in the information in the blank text boxes.
4. Click “Check” to check the OpenLM server’s connection to SQL Server.
5. Click “Apply” to finish the configuration.

Please refer to “OpenLM Database Configuration Form” for further information.

Revision table