How to migrate from Oracle to SQL Server

<< Click to Display Table of Contents >>

Navigation:  Bizagi Studio > How To´s > Useful how-to's >

How to migrate from Oracle to SQL Server

Overview

When building process applications, you start by working with Bizagi Studio and relying on a database repository to save your processes.

As mentioned in Database requisites, you can choose Oracle or SQL Server as your database engine.

There is no significant difference between choosing one or another.

 

Whenever you want to publish your processes in the cloud (use Automation Service), one of the requisites is to have you database project running in SQL Server.

This article describes how you can migrate your project database from Oracle to SQL Server.

 

Important

Take into account the following recommendations:

 

1. Always work with a replicated database instead targeting the currently operating one.

This means always creating a database backup first, and restoring it into an alternate database so that you can work with that replica instead of affecting the operating one.

This measure serves as a best practice to avoid any issues that may affect your operating databases.

 

2. Always carry out procedures first in your development environment.

In this exercise, it is recommended to carry out the described procedures and perform unit tests to verify that existing processes behave as expected, first in the development environment.

Once this is done, carrying out the procedures in your test environment and performing user-acceptance tests on this environment should be done as well.

Only after verifying that processes work OK both in the development and in the test environment, then you can carry out the procedures in your production environment.

 

3. Plan the migration and coordinate it with your team.

A migration should be carried out with prior coordination, so that all stakeholders are aware and aligned with the plan.

This way, you can communicate planned downtime.

For instance, whenever starting to upgrade an environment, you should make sure that people will not work on such environment for that given time frame by issuing a notification beforehand.

 

4. Plan the production environment's migration and coordinate to be carried out at non-working hours.

As hinted above, a migration will imply that there is planned downtime.

A migration should be carried out with prior coordination, and during a time frame which is best and implies non-busy hours.

 

Before you start

The procedure described below applies to each of your environments separately.

This means that you will need to make sure that both prerequisites are met and that you carry out the procedure separately for each of your environment; and as hinted above, by considering first your development environment, then your test environment and finally, your production environment.

 

Prerequisites

Migrating the database from Oracle to SQL Server requires the following:

Your project must be in version 11.1 or higher.

You need to have created a database replica of your current environment's database.

You need to install a SQL Server instance (versions 2016, 2014 or 2012) complying to the following:

oHaving free disk space equivalent to at least twice the size occupied by your current Oracle database.

oBeing installed on a server within the same network segment of the Oracle database (having a low latency between these two servers).

You need to install Oracle Provider for OLE DB at the SQL Server server.

Download Oracle Provider for OLE DB  from Oracle's official web page.

You need to install either Bizagi Studio (for development environment) or the Bizagi Management Console (for test and production environments) that has access to both database servers.

You need to download the scripts for the migration, as provided by Bizagi Ltd.

Download the scripts here.

 

What you need to do

The following procedure must be carried out by the admin user of your Oracle and SQL Server databases:

 

1. Prepare SQL Server to support the migration

1.1. Set up a linked server

1.2. Create the target database

2. Prepare Oracle to support the migration

2.1. Configure the source database in development mode

2.2. Validate that the database is read only

3. Copy the data structures from Oracle to SQL Server

3.1. Export a .bex file from your source database

3.2. Import the .bex file in the target database

4. Run data migration scripts.

4.1. Create temporary objects in the target database

4.2. Disable database constraints

4.3. Create the Linked Server

4.4. Migrate metadata

4.5. Migrate data

5. Perform verifications and activations in SQL Server

5.1. Verify the database structure

5.2. Validate of data integrity

5.3. Enable constraints

5.4. Delete temporary objects

6. Reconfigure the Bizagi environment

7. Perform tests

 

Procedure

1. Prepare SQL Server to support the migration.

In this step, you prepare the target database to consult and add information from the Oracle database.

 

1.1 Set up a linked server

To prepare SQL Server to support the migration, make sure the Allow inprocess parameter is active in the target database server. Activate it using SQL Server Management Studio, right-click OraOLEDB.Oracle node located in [DB_Server] > Server Objects > Linked Servers > Providers and select Properties.

 

 

MigrateOra2SQLS_20

 

Check the Allow inprocess check box to enable this property and click OK.

 

MigrateOra2SQLS_21

 

1.2. Create the target database

In the target server (where SQL Server is installed) create an empty Bizagi database where the data will be migrated, to do this run the CreateDatabase application. For more information refer to Advanced Deployment.

The CreateDatabase application and its configuration files come in by default installed where the Management Console is installed (at C:\Program Files\Bizagi\Bizagi Studio\MC\).

 

1.2.1. Edit the CreateDatabase configuration file

Set up the following configuration in your target server to create the database:

 

MigrateOra2SQLS_03

 

To configure it, you need to make sure that you specify the connection and provider as described below.

 

<add key="DSNDB" value="Persist Security Info=True;User ID=[SQL_Login];Password=[Login_password];Data Source=[DB_Server]\[Named_instance];Initial Catalog=[Database];" />

<add key="PROVIDERTYPE" value="MSSqlClient" />

Consider:

[SQL_Login]: The login account used to connect to the target SQL Server database instance.

[Login_password]: The password for the above login. It is strongly recommended to encrypt the password using Password Encryption feature.

[DB_Server]: Name or IP address of the target database server. Use \[Named_instance] when applies, if your database instance is not the unnamed default one.

[Database]: The name of the target's database that will be created.

 

note_pin

When configuring the executable, the SQL Server login used will require sysadmin rights.

 

1.2.2. Run CreateDatabase executable file

Once the connection to the target database has been configured. Run CreateDatabase.exe:

 

MigrateOra2SQLS_04

 

Select Create Bizagi Test Database. And then click Yes in the confirm window.

 

MigrateOra2SQLS_05

 

A confirmation message appears to inform you that the procedure has finished successfully.

 

MigrateOra2SQLS_06

 

2. Prepare Oracle to support the migration.

We recommend to generate a backup of the project schema. To do this, follow the procedure explained in How to create backups in Oracle.

If you took the backup, restore it in the current oracle server or in other in the same network segment. To do this, follow the procedure explained in How to restore backups in Oracle.

 

note_pin

The log configured in this step before must not show any error related to the execution of the backup. In case that any error is displayed, fix it and repeat the given process until successfully completed.

 

2.1. Configure the source database in development mode

This step is required for Test and production environments. Avoid this step if you are migrating development database.

To be able to perform the export process, the source database must be set in Development mode. This step is performed by running the script 01_Environment_ConfigurationORACLE.sql in the source database.

 

Make sure the variable @EnvName has Development as its value.

 

MigrateOra2SQLS_01

 

Once the script has been executed. Run the following script to validate if the database is now in development mode:

 

SELECT * FROM BIZAGIINFO WHERE bainfo = 'Environment';

 

The expected result must be the following:

 

MigrateOra2SQLS_02

 

2.2. Validate that the database is read only

Before moving on, make sure that nobody is working in this database, so no changes in its structure or data are made. Therefore, the database must be isolated from external application that may modify it during the migration procedure.

 

3. Copy the data structures from Oracle to SQL Server

In this step, the basic structure of the Bizagi project migrates from Oracle to SQL Server.

 

3.1. Export a .bex file from your source database

To export the project's structure from the Oracle database, run the Export application. For more information refer to Advanced Deployment.

The Export application and its configuration files come in by default installed where the Management Console is installed (at C:\Program Files\Bizagi\Bizagi Studio\MC\).

 

3.1.1. Edit the Export configuration file

Set up the connection to the source database to generate the export package:

 

To configure this file, add the following key under the ProofConcept_Utility key:

 

<add key="ExportCompleteCatalog" value="True" />

 

MigrateOra2SQLS_07

 

Furthermore, make sure you specify the connection and provider as described below.

 

<add key="DSNDB" value="Data Source=[DB_Server]:[Port_number]/[Service];User ID=[User_schema];Password=[User_schema_password];Unicode=True;" />

<add key="PROVIDERTYPE" value="Oracle" />

Consider:

[DB_Server]: Name or IP address of the source database server.

[Port_number]: The TCP port used for the connection to the source database service.

[Service]: The service identification for the source Oracle instance.

[User_schema]: The name of the source project environment's database.

[User_schema_password]: The password for that user schema. It is strongly recommended to encrypt the password using Password Encryption feature.

 

3.1.2. Run Export executable file

Once the connection to the source database has been configured. Run Export.exe:

 

MigrateOra2SQLS_08

 

Select ALL the processes and ALL the elements in the Experience tab. Then, click Advanced.

 

MigrateOra2SQLS_09

 

Check the Export complete catalog checkbox and click OK.

 

MigrateOra2SQLS_10

 

Click Export and save the .bex file in a path of your choice.

 

MigrateOra2SQLS_11

 

A confirmation message appears to inform you that the procedure has finished successfully.

 

MigrateOra2SQLS_12

 

3.2. Import the .bex file in the target database

To import the project's structure to the target database, run the CreateImport application. For more information refer to Advanced Deployment.

The CreateImport application and its configuration files come in by default installed where the Management Console is installed (at C:\Program Files\Bizagi\Bizagi Studio\MC\).

 

3.2.1 Edit the CreateImport configuration file

Set up the following configuration in your target server to import the database schema:

 

MigrateOra2SQLS_13

 

To configure this file using connection properties to the target database used in step 5.

 

3.2.2 Run CreateImport executable file

Once the connection to the target database has been configured. Run CreateImport.exe:

 

MigrateOra2SQLS_14.

 

Click Load File. Select the .bex file containing the deployment package generated in the previous step.

 

MigrateOra2SQLS_15

 

Once the file is selected. Click Apply package.

 

MigrateOra2SQLS_16

 

A confirmation message appears to inform you that the package has been applied successfully.

 

MigrateOra2SQLS_17

 

4. Run data migration scripts

These steps lets you migrate the data from the Oracle database to the target SQL Server database.

 

4.1. Create temporary objects in the target database

This step is performed by running the script 02_FunctionsAndUserTypes.sql in the target database.

 

MigrateOra2SQLS_18

 

4.2. Disable database constraints

This step is performed by running the script 03_SQL-DisableAllConstraints.sql in the target database.

 

MigrateOra2SQLS_19

 

4.3. Create the Linked Server

This step is performed by running the script 04_CreateLinkedServerSQLToOracleDB.sql in the target database. Make sure the variables has the following values:

 

DECLARE @LinkName VARCHAR(120) = 'ORACLEDB_ORIGIN'

DECLARE @OracleSource VARCHAR(120) = N'[DB_Server]:[Port_number]/[Service]'

DECLARE @UserName VARCHAR(120) = N'[User_schema]'

DECLARE @UserPassword VARCHAR(120) = N'[User_schema_password]'

 

Consider:

[DB_Server]: Name or IP address of the source database server.

[Port_number]: The TCP port used for the connection to the source database service.

[Service]: The service identification for the source Oracle instance.

[User_schema]: The name of the source project environment's database. This user name must be in upper-case.

[User_schema_password]: The password for that user schema.

 

MigrateOra2SQLS_22

 

4.4. Migrate metadata

This step is performed by running the script 05_CopyBizagiCatalogTables.sql in the target database. This may take several minutes depending on your database size.

 

MigrateOra2SQLS_23

 

The query result shows the number of records migrated from the source database.

 

4.5 Migrate data

This step is performed by running the script 06_CopyAllTablesExceptCatalog-WithColumnsValidation.sql in the target database. This may take several minutes depending on your database size.

 

MigrateOra2SQLS_24

 

5. Perform verifications and activations in SQL Server.

In this step, you can verify whether the structure of the project is correct in SQL Server and activate the constraints in the target SQL Server database.

 

5.1. Verify the database structure

This step is performed by running the script 07_SchemaValidation.sql in the target database. This query validates that the schema of the target database is corresponds to the schema in the source.

This script also migrates the index created by you which are not included in Bizagi's metadata.

 

MigrateOra2SQLS_25

 

The first list in the results displays the source tables that was not migrated because they were created manually. If you need any table with any purpose, create and populate them manually.

To proceed with the next step, other lists must be empty.

 

5.2. Validate data integrity

This step is performed by running the script 08_compare_data.sql in the target database. This may take several minutes depending on your database size.

The results displays the tables in the database and the differences with their values.

 

MigrateOra2SQLS_26

 

note_pin

No differences should appear in the results. Nevertheless, if any differences appears, use run the script 10_Generate_Query_for_data_Comparison.sql in the target database. Set the table name in the variable @Table_name as required.

 

Some differences are related to differences with decimals.

 

Consider that any additional Table, View, Index, Stored Procedures, or Functions which were not created by Bizagi and which you included as bespoke within Bizagi's database, will not be migrated.

 

5.3. Enable constraints

This step is performed by running the script 09_SQL-EnableAllConstraints.sql in the target database.

 

MigrateOra2SQLS_27

 

At this point, if no error was presented during the migration, you can proceed with the next step.

 

5.4. Delete temporary objects

In this step, you delete the temporary objects used for the migration procedure. This step is performed by running the script 12_Delete_Temporary_Objects.sql in the target database.

 

MigrateOra2SQLS_29

 

 

6. Reconfigure the Bizagi environment

This step is performed by running the script 11_Environment_ConfigurationSQLServer.sql in the target database.

 

Make sure the variable @EnvName has the corresponding value according to the environment name ('Development', 'Test' or 'Production') of your Bizagi project.

Once the script has been executed, the result BAValue column must display the same value of the variable @EnvName.

 

MigrateOra2SQLS_28

 

7. Perform tests

Once this procedure is completed, the SQL Server database has all the data and tables of your database project. You can now update the connection of your project to this migrated database.

For more information about configuring the database in Studio (development environment) or Automation Server (Testing and Production environments). refer to managing your projects.