koa2-mongoose-api | api w/ email-tag bi-directional relationship | Data Migration library

I will try to give some tips to help you improve your package performance. You should start troubleshooting your package systematically to find the performance bottleneck.

Some provided links are related to SQL Server. No worries! The same rules are applied in all database management systems.

1. Available resources

First, you should ensure that you have sufficient resources to load the data from the source server into the destination server.

Ensure that the available memory on the source, ETL, and destination servers can handle the amount of data you are trying to load. Besides, make sure that your network connection bandwidth is not decreasing the data transfer performance.

• How Does SSIS Manage Connection Traffic?

Make sure that the following hardware issues are not occurring in any of the servers:

1. Drive out of storage
2. Server is out of memory

Make sure that your machine is not running out of memory. You can simply use the Task Manager to identify the amount of available memory.

2. The Data Source Make sure that the table is not a heap

After checking the available resources, you should ensure that your data source is not a heap. At least it would be best if you had a primary key created on that table.

• Clustered Index vs. Heap in SQL Server
• SQL Server clustered tables vs. Heap tables

Indexes

If your SQL Command contains any filtering, ordering, or Joins, you should create the indexes needed by those operations.

• Use WHERE, JOIN, ORDER BY, SELECT Column Order When Creating Indexes
• Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?

OLE DB provider

Instead of using OLE DB source to connect to Oracle, try using the Microsoft Connector for Oracle (Previously known as Attunity connectors). Microsoft previously mentioned that it should provide faster performance than traditional OLE DB providers.

Use the Oracle connection manager rather than OLE DB connection manager.

To be honest, I am not sure if this component can make the data load faster since I didn't test it before

Removing the destination and adding a dummy task

The last thing to try is to remove the ODBC destination and add any dummy task. For example, use a row count component.

Run the package; if the data is loaded faster, then loading data from Oracle is not decreasing the performance.

3. SSIS configuration

Now, let us start troubleshooting the SSIS package.

Running in 64-bit mode

First, try to execute the package in 64-bit mode. You can change this from the package configuration. Make sure that the Run64bitRuntime property is set to True.

Data flow task buffer size/limits

Using SSIS, data is loaded in memory while being transferred from source to destination. There are two properties in the data flow task that specifies how much data is transferred in memory buffers used by the SSIS pipelines.

Based on the following Integration Services performance tuning white paper:

DefaultMaxBufferRows – DefaultMaxBufferRows is a configurable setting of the SSIS Data Flow task that is automatically set at 10,000 records. SSIS multiplies the Estimated Row Size by the DefaultMaxBufferRows to get a rough sense of your dataset size per 10,000 records. You should not configure this setting without understanding how it relates to DefaultMaxBufferSize.

DefaultMaxBufferSize – DefaultMaxBufferSize is another configurable setting of the SSIS Data Flow task. The DefaultMaxBufferSize is automatically set to 10 MB by default. As you configure this setting, keep in mind that its upper bound is constrained by an internal SSIS parameter called MaxBufferSize which is set to 100 MB and can not be changed.

You should try to change those values and test your package performance each time you change them until the package performance increases.

4. Destination Indexes/Triggers/Constraints

You should make sure that the destination table does not have any constraints or triggers since they significantly decrease the data load performance; each batch inserted should be validated or preprocessed before storing it.

• Are SQL Server database triggers evil?
• The benefits, costs, and documentation of database constraints

Besides, the more you have indexes the lower is the data load performance.

ODBC Destination custom properties

ODBC destination has several custom properties that can affect the data load performance such as BatchSize (Rows per batch), TransactionSize (TransactionSize is only available in the advanced editor).

• ODBC connection is very slow

To seed the user and role data, you can create a static class, like this:

This should get you started:

Azure SQL Database (and SQL Server 2017+) has a built-in integration to Azure BLOB storage, and for a public container you don't even need a credential, Eg:

The above method is recommended for Table API as mentioned in the doc, if you want to migrate data of SQL API use the Data Migration Tool.

Considering the company doesn't have on-prem resources, I would be looking at implementing the data migration on Azure Data Factory. Below are few points to take into consideration:

Pros:

1. Integration between ADF and other Azure resources e.g. SQL Database is seamless and doesn't require connector setup, etc.
2. You would take advantage of Microsoft's network to improve your data transfer, your data won't go over the network, everything is within MS data centers.
3. More secure and reliable transfer, you could take advantage of ADF Managed Identity for authenticating to your source and destination.
4. Since there will be a lot of changes, splits, etc, you can take advantage of ADF's ability to start from where the pipelines failed. On the other hand, in SSIS you'd need to start all over again.
5. Better monitoring capabilities

Cons:

1. You'd need an infrastructure to develop, deploy, and run your SSIS packages, which will increase implementation time and maintenance overhead.
2. You could run SSIS packages using ADF, but it requires a much bigger implementation to host your packages and run them. Also, it'd be more costly.
3. If the plan is to use a VM, there is an additional overhead to set up the VM and SSIS. Also, the cost associated with spinning up a new VM and SQL Server.
4. Not great monitoring and retry capabilities

The approach of migrating first to CSV and then to Cloud SQL for MySQL sounds good. As you said, you will need to create a Cloud Storage Bucket [1], upload your CSV file to the bucket, and follow the steps here [2] corresponding to MySQL.

[1] - https://cloud.google.com/storage/docs/creating-buckets

[2] - https://cloud.google.com/sql/docs/mysql/import-export/import-export-csv#import

You could get time like so Time.new(2021, 9, 7)

What wrong am I doing?

You're not doing anything wrong. It's just that the Cosmos DB SDK used by this tool is very old (Microsoft.Azure.DocumentDB version 2.4.1) which targets an older version of the Cosmos DB REST API. Since you created your container using a newer version of the Cosmos DB REST API, you're getting this error.

If your container is pretty basic (in the sense that it does not make use of anything special like auto scaling etc.), what you can do is create the container from the Data Migration Tool UI itself. That way you will not run into compatibility issues.