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. Whenever you want to publish your processes in the cloud (using Automation Service), your database project must be 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:

 

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.

 

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.

 

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.

 

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 the same server, preferable, or within the same network segment of the Oracle database (having a low latency between these two servers).

oInstall SQL Server Management Studio.

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

2. Prepare Oracle to support the migration

3. Copy the data structures from Oracle to SQL Server

4. Run data migration scripts

5. Perform verifications and activations in SQL Server

6. 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 as an administrator.

 

Select ALL the processes to export.

 

MigrateOra2SQLS_32

 

Select ALL the elements in the Experience tab.

 

MigrateOra2SQLS_33

 

Select the Advanced menu, and select the Export all catalog check box and click Export.

 

MigrateOra2SQLS_34

 

Click Export and save the .bex file in a path of your choice. A confirmation message appears to inform you that the procedure has finished successfully.

 

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 as an administrator. Click the Browse button, and select the BEX file exported in the previous step.

 

MigrateOra2SQLS_35

 

Once the file is selected. Click Apply package.

 

MigrateOra2SQLS_36

 

This process might take several minutes. After finished, a confirmation message appears to inform you that the package has been applied successfully.

 

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. No errors must be displayed on the output messages.

 

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.Run the script to 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 or service name 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

 

After running the script, a connection test is executed. If the credentials are not correct, or you do not have access to the server, Oracle displays the following message.

 

MigrateOra2SQLS_39

 

If you have an error message that says "Cannot create an instance of OLE DB provider", you can follow the troubleshooting procedure explained here.

 

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

 

This script validates the number of each record of each migrated table. If there are differences, the script displays a message, displaying that not all rows were migrated. If this error occurs, could happen because there might be inconsistent constraints, like primary keys, foreign keys, or unique constraints. You can troubleshoot it by verifying the structure of the database. See next step.

 

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.

 

If there are objects with numbers that are not considered by the Bizagi standard, a normalization process is executed. If there are errors displayed, you must validate that these objects exist in the origin and target database.

 

MigrateOra2SQLS_40

 

If there not migrated tables, it means that those tables were created externally, and do not correspond to the Bizagi database structure. 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

 

No differences should appear in the results. Nevertheless, if any differences appear, SQL displays the following message. Some differences are related to differences with decimals.

 

MigrateOra2SQLS_41

 

Run the script 09_Generate_Query_for_data_Comparison.sql in the target database. Set the table name in the variable @Table_name as required.

 

MigrateOra2SQLS_42

 

The results display the inconsistent data within the table.

 

MigrateOra2SQLS_43

 

In the Messages tab, the queries are generated. This can be copied to execute the comparison manually.

 

MigrateOra2SQLS_44

 

You can replace the identifier:

 

idPRIMARYKEY = ?

 

Using an ID of a primary key value listed in the results. For example, using the idWorkitem:

 

MigrateOra2SQLS_45

 

Consider that any additional Table, View, Index, Stored Procedures, or Functions that 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 10_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 Set the Bizagi's environment

After the migration, you must define the environment of the database, considering the environment configured before starting the migration (development, test, or production). This step is performed by running the script 11_Environment_ConfigurationSQLServer.sql. Before running the script consider as follows:

 

Set the environment in the @envName parameter. This must have any of the following values: 'Development', 'Test' o 'Production'

 

MigrateOra2SQLS_46

 

After running this script, validate that the environment is configured right running the following query:

 

SELECT * FROM BIZAGIINFO

 

MigrateOra2SQLS_47

 

 

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