DB Best uses a 12-step migration process to make sure all bases are covered as part of a typical migration project. Data migration is a critical part of a database migration projects and tools like SSMA can help in the process. In the following video, we will show you how to migrate data using the SSMA tool, as well as highlight errors that may occur during database migration.
Background on data migration
SQL Server Migration Assistant helps migrate data, but you still need to be well prepared before starting the data migration process. Prior to data migration, you have to convert the database schemas. For this stage, you can set the types mapping using the corresponding tab of the SQL Server Migration Assistant for Oracle tool. Then you have to apply the converted objects to the target database.
You will also want to review the Migration section in the Project Settings as shown below. The Client Side Data Migration Engine is useful for loading smaller amounts of data. The Server Side Data Migration Engine uses SQL Server Agent to transfer data in the background and is used for larger data transfers that can take a long time.
To get access to the extended data migration options, set the corresponding option to “Show”. Now the extended settings will be displayed in the Data Migration tab in the top part of the window.
The most valuable items here are the batch size (the number of data rows, copied at the same time), the parallel data migration mode and the type of data migration engine (more on that in the Observations section).
After completing all of these steps, the target database will be ready to accept the data transfer, allowing you to move forward with the actual data migration.
Observations
We’ve used the SQL Server Migration Assistant for Oracle in a number of database migration projects. Here are the key findings:
- By default, the data from the target table is deleted at the start of the data migration operation. You can keep the original data in the target database, adding the new data to the existing table, by unchecking the “Truncate SQL Server table” the extended settings in the Data Migration tab.
- SSMA uses a SELECT query to extract data from the source table and a bulk insert operation to execute the insert statement in the target database. You can customize the select query in the extended settings of the Data Migration tab.
- To use the Server Side Data Migration option, you have to install the extension pack in the target database.
- One of the typical data migration errors we have encountered is related to the NULL values in the unique index column. It’s a supported option in the Oracle environment which cannot be used in SQL Server. In order to solve this issue, you have to create special filtered indexes for that column instead of the unique index in SQL Server, or simply remove it from the table altogether.
Experiment
We have used the SSMA tool for data migration of an Oracle table with a total of 14,563,070 rows (around 1 GB of data). When we used the Client Side Migration engine, the migration successfully completed in 1 hour and 38 minutes (the average speed was 1.45 Mbits per second). Using the Server Side Migration engine allowed us to complete the operation in 1 hour and 21 minutes, which is a fairly slow result too. The average migration speed this time was around 1.8 Mbps. Surely, the average migration speed depends on the access speed of source and target databases, however the result was a bit disappointing. For example, the DB Best Database Compare Suite application migrates data in the same database in just 17 minutes, reaching the transfer speed of 8.5 Mbps.
Conclusion
SQL Server Migration Assistant is a simple and handy tool for database migrations into SQL Server. It has a friendly user interface. The data migration option is a welcome bonus for this tool.
We’ve noticed, that the speed of data migration in SSMA is pretty low. This is not a critical issue for migrating of smaller data volumes, but for big data migration projects we recommend other professional tools (such as Database Compare Suite, OraMigrator, DB2Migrator, MySQLMigrator). Those tools provide a greater flexibility in configuration and control of the migrated data.
Depending on your deployment needs, you may need to look for sophisticated data migrations options such as replication and change data capture when moving the database into production. DB Best can help you in determining the best strategy for data migration and build a custom solution if required.
