USA +1 866 806 2068 | UK +44 203 1292 513 | JAPAN +81 3 45208991 担当:萩原 info@openlm.com

Database Migration: Firebird to MS_SQL 2.0 – AN3008a

OpenLM is a license management tool that provides application usage monitoring for a variety of concurrent license servers. These include (but not limited to) FlexLM / Flexnet, Sentinel RMS (Aspentech SLM), DSLS, IBM LUM, Sentinel HASP, LM-X (Altair), Reprise RLM, Beta-LM, Mathematica LM (MathLM), Easycopy, EPDM, and AMP. A full list of supported server types is available here.

Scope

OpenLM provides an embedded Firebird database  as part of the software installation. It also supports different types of external databases, such as MS-SQL, Oracle and MySQL. This document is intended for system administrators who wish to perform an OpenLM database migration from the default Firebird database to the MS-SQL database. It elaborates the process and tools required for such a migration. This document is closely related to the Guide to OpenLM Data Migration Tool, OpenLM Database Configuration Form  and Backing Up Your OpenLM DB documents, which are recommended for further reading.

Back up the database

Backing-up the database is mandatory, since the database upgrade process is sensitive to hardware and software malfunctions. Upgrade the OpenLM Server without backing up the database file may compromise the accumulated data. Stop the “OpenLM Server” Windows’ Service, and backup the database file, typically located at “C:\Program Files (x86)\OpenLM\OpenLM Server\db”.

Older versions

Migrating older versions of OpenLM (e.g. 1.8.x.x) Firebird Databases to the latest version on MS-SQL is done in 2 stages:
1. Upgrading to the latest Firebird database.
2. Migrating the updated Firebird database to an external database.

Migrating the database

 

1. Creating a new MS-SQL Database (Building the tables):

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

1.1. Please contact OpenLM Support to obtain the DDL scripts package.

The Downloaded zip file contains files for MS-SQL, MySQL and for Oracle servers. In this document we will make use of the MS-SQL files.

1.2. Open the Microsoft SQL Server Management Studio

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

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

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

1.5. Drag the “OpenLM_SQLServer.SQL” file from the DDL downloaded directory over to the Microsoft SQL Management Studio window. The Contents of “OpenLM_SQLServer_2.0.0.SQL” is displayed.

The file can also be opened by clicking “File → Open → File…” and selecting “OpenLM_SQLServer_2.0.0.SQL” from the unzipped folder.

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

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

2. Data Migration

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

2.1. Make sure that the Firebird database is backed up, according to the “Backup the current OpenLM database section” above.

2.2. Open the “OpenLM Data Migration” tool.

2.3. On the Source frame:

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

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

2.3c.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’.

2.4. Target frame actions: There are two alternatives for configuring the target frame:

  • “Windows Authentication mode”.

  • “SQL Server Authentication mode”.

“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 “Creating a new MS-SQL Database (Building the tables)” section above) from the “Databases” drop down menu, and click OK.

“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 “Creating a new MS-SQL Database (Building the tables)” section above) from the “Databases” drop down menu, and click OK.

2.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.

3. Configuring Openlm to work with the SQL Server

Now the OpenLM server must be made to point to the new SQL Server.

3.1. Open the “OpenLM Database Configuration” tool:

(Start → All Programs → OpenLM → Server → OpenLM Database configuration).

3.2. Click the DB provider drop-down menu. Choose one of the options for SQL: “SQL Server Authentication” or “SQL Windows Authentication”, according to the configuration done previously on the data migration tool. The “OpenLM Database Configuration” window changes accordingly:

3.3. Fill in the information in the blank text boxes.

3.4. Click “Check” to check the OpenLM server’s connection to SQL Server.

3.5. Click “Apply” to finish the configuration.

Epilog

That’s it. The database has been upgraded and migrated to the latest version.

It is now recommended to

  • Stop and start the OpenLM Server service and
  • Perform a sanity – check to the OpenLM system’s functionality; Open the EasyAdmin web application, and review the License servers’ status and usage reporting windows.