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.
Bearing this in mind, the following article describes how you can migrate your project database from Oracle to SQL Server. This procedure allows exporting from an Oracle database those tables, views, indexes, stored procedures and functions created in Bizagi Studio as a result of a process implementation. Hence, those elements added to the database outside of Bizagi Studio (with the exception of indexes) will not be included in the migration, since they were created outside of the Bizagi environment.
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 on a 11.2.x Bizagi version. If you are on a previous version, the project database must be migrated to version 11.2 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
5. Perform verifications and activations in SQL Server
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.
Check the Allow inprocess check box to enable this property and click OK.
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:
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.
|
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:
Select Create Bizagi Test Database. And then click Yes in the confirm window.
A confirmation message appears to inform you that the procedure has finished successfully.
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.
|
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.
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:
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" />
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.
Select ALL the elements in the Experience tab.
Select the Advanced menu, and select the Export all catalog check box and click Export.
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:
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.
Once the file is selected. Click Apply package.
This process might take several minutes. After finished, a confirmation message appears to inform you that the package has been applied successfully.
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.
4.2. Disable database constraints
To optimize data transfer speed during migration, database constraints must be disabled by running the script 03_SQL-DisableAllConstraints.sql in the target database.
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.
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.
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.
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.
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.
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.
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.
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.
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.
The results display the inconsistent data within the table.
In the Messages tab, the queries are generated. This can be copied to execute the comparison manually.
You can replace the identifier:
idPRIMARYKEY = ?
Using an ID of a primary key value listed in the results. For example, using the idWorkitem:
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.
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'
After running this script, validate that the environment is configured right running the following query:
SELECT * FROM BIZAGIINFO
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.
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.