Data migration tool - Part IV: from Microsoft SQL Server to IRIS

This is the fourth part of the series of articles on migrating from the main databases on the market to InterSystems IRIS. In this part, the procedures for migrating from Microsoft SQL Server will be detailed. As described in previous articles, there are currently a few options to do the migration, but the two most popular options include the use of DBeaver (https://openexchange.intersystems.com/package/DBeaver) or SQLGateway. The first one will be demonstrated in this article, and the second one in an excellent article by Robert Cemper, DB Migration using SQLgateway (https://community.intersystems.com/post/db-migration-using-sqlgateway).

 

Get the sample data for the migration process

In Github it is possible to download a docker compose project to build and run 2 databases:
1.    Source Database: SQL Server database Docker instance with a sample database.
2.    Target Database: InterSystems IRIS data platform Docker instance with a ready schema to receive the source database.
To get the sample and run it, follow these steps:
1.    Go to https://github.com/yurimarx/migration-mssql-iris and click Download to go to the git repository.
2.    Clone the project: git clone  https://github.com/yurimarx/migration-mssql-iris.git.  
3.    Go to the project folder migration-mssql-iris.
4.    Do the build: docker-compose build.
5.    Execute the containers: docker-compose up -d.
6.    Check if everything is ok with the instances in your docker desktop:

About the data to be migrated

In the first two parts, we worked with a sales database. In part III we used a DB2 sample database (it is a more complete sales database). Now we will work with the famous MSSQL sample database, the AdventureWorks database. The data to be migrated is represented here:

AdventureWorks Schemas

So, the migration process from SQL Server to IRIS will include 6 schemas and 70 tables. Here are table details:

The migration destination will be dc_test schema inside the USER namespace in the InterSystems IRIS database.

An Open-source tool to migrate from SQL Server to IRIS: DBeaver

DBeaver is a database tool used to connect, create, drop, select, update and delete data objects to the main database products in the market. Download it from: https://openexchange.intersystems.com/package/DBeaver. Now follow the installation instructions to get this fantastic product into your laptop or desktop.
DBeaver can be used to migrate data between database connections, even if they come from different manufacturers and versions.

Connecting the Source and Target Databases using the DBeaver

Now we will set the database connections to be migrated.
To set MS SQL connection to the DBeaver:
1.    In the DBeaver Go to File > New.
2.    Select Database Connection and click Next:

3.    Choose SQL tab > SQL Server and click next:

4.    Fill the SQL Server connection fields as shown in this picture:

To set InterSystems IRIS connection to the DBeaver:

1.    In the DBeaver Go to File > New.
2.    Select Database Connection and click Next:

3.    Choose SQL tab > InterSystems IRIS and click next:

4.    If DBeaver requests to download the InterSystems IRIS driver, press Yes or Ok.
5.    Set the InterSystems IRIS connection fields as demonstrated in this picture:

The connections (AdventureWorks and user) are available in the Database Navigator:

Do the migration

To do the migration, follow these steps:

1.    Expand the SQL Server connection > Schemas > HumanResources > Tables and select all tables. Click the mouse right button on the selected tables and choose Export Data, as illustrated in this picture:

2.    Select Database, as displayed in this picture and click Next:

3.    Click the Choose button:

4.    Select dc_test and click Ok.

5.    Now it is necessary to change the field rowguid from the data type varchar(16) to varchar(50) because some rows have more than 16 characters.
6.    Expand the AdventureWorks.HumanResources.Employee table, select the rowguid field and click Columns…

7.    Change the Target Type from VARCHAR(16) to VARCHAR(50) and click Ok.  

8.    Repeat the same process with all other tables with the same field in the other schemas.
9.    Now, when the Target Data Types have been changed, click Next.

10.    Set the Fetch size to 1000000 and click Next.

11.    Accept the default values in the Data load settings and click Next.

12.    In the Confirm click Proceed.

13.    Now you can see all SQL Server Tables inside InterSystems IRIS dc_test schema in the Database Navigator.

Repeat the migration procedures with the other schemas.
The migration process is very simple for tables, but for views, functions, triggers and stored procedures, you will need to rewrite the SQL source code using ObjectScript or SQL.