5 Considerations when Migrating from Oracle to Azure SQL Server

A huge number of mid and large-sized organizations currently utilize Oracle database as part of their overall IT stack. Many of these organizations are still using the on-premise database version. As the volume of organizational data increases, their database ecosystem begins to get stretched, requiring better management, higher storage and processing power which unfortunately increases overall cost. With this increased data volume, many companies find it difficult to maintain their databases at optimum costs while addressing the growing complexity.

Once organizations recognize and buy into the benefits of migrating enterprise applications to the cloud, they then need to evaluate which platforms make the most sense for future growth. In a nutshell, they need a platform that will provide improved performance and operational benefits at an optimized cost structure. Two of the major alternatives to Oracle on-premise database is migrating to Oracle Cloud or Microsoft SQL server (hosted in Azure cloud or on-premise).

If you have made the decision to move to Microsoft SQL server, here are 5 considerations to help both the planning process and actual migration go a bit smoother:

1. 1. 1. How much effort is required to complete the migration process?Oracle and SQL have a large set of common features, so, the bulk of the migration can be handled by tools and features available within SQL. Oracle features not compatible (or not easy to migrate) with SQL, will require more effort including:
         1. Row-level triggers
         2. Package variables
         3. Collections (nested tables, VARRAYS, objects)
         4. System packages
         5. User-defined types
         6. Wrapped source code

         The duration and complexity of such a migration process depends upon a number of variables including:

         • Oracle specific features used in database design and programming
         • Size of database (influences the overall approach as discussed below)
         • Design of the data integration layer and its level of connection with Oracle
      2. What other Oracle database features need to be addressed?The Object-Relational Mapping (ORM) used by your applications needs to be reviewed. The same version of ORM may support a different set of features on Oracle and SQL Server. For example, some versions of Hibernate ORM support sequences within Oracle but not within SQL Server. It is better to test the application by migrating a small set of tables, to identify if there will be a larger effort involved with ORM changes (or upgrades).
      3. What is the best migration approach – big bang or phased?Big-bang refers to completing the entire migration in one go. For this to occur, the existing system needs to be shutdown, as any transactions occurring after final backup will not transfer into the new system. This is a simple, effective, and robust method, however in most cases a complete shutdown is just not feasible for most businesses. If this approach is taken, multiple test executions can be done in advance, to ensure that things go well on migration day.In a phased migration, the first stage focuses on migrating data that either doesn’t change at all, or changes very rarely. In the next phase, jobs can be run at specified intervals, which migrates any delta in data since the last run. There are certain advantages of following this approach:
         • There is not a complicated migration when 100s of GB of data moves. This provides less chance of complications or failure.
         • Grants the ability to complete testing on the MS SQL server thoroughly before cutover.

         The phased migration approach only works if the Oracle system has a schema, which allows the organization to identify deltas easily. This usually means having audit columns which can mark updated rows. If this isn’t possible, the big bang approach is a better option.

      4. What tools can be used to complete an Oracle Database to SQL Server Migration?Microsoft provides a SQL Server Migration Assistant (SSMA) for schema migration which can be used as an assessment tool. The output can be used as the first version of the SQL schema. SSMA also provides options for data migration, however, it does not completely support parallel operations. It can complete multiple tables in parallel, but there is no parallelism within the table.A better option for data migration is Azure Data Factory (ADF), which supports on-premise migration with multiple parallel threads, and allows for choosing the number of threads. This is particularly beneficial if the SQL database will be hosted in an Azure environment, but it can be also used if hosted on-premise.
      5. How can the migration process be accelerated?Once the most suitable migration approach (discussed above) has been finalized, the following 3 tips can significantly improve the speed of migration:
         1. As millions of rows get inserted in the SQL database, disabling/dropping all indexes before migration helps to manage the indexes. Recreating them after the migration is a much faster approach.
         2. Setting the recovery mode on SQL to “simple” skips transaction logging and makes inserts much faster.
         3. Setting the initial file size of the SQL database to the size it is expected to grow helps to avoid all allocations and re-allocations.

Once the migration is initiated, you want to ensure that the migration is successful. Here are a few objects that should be reviewed after the migration is completed:

1. 1. • Have checksum scripts ready to compare data between SQL and Oracle server.
      • Performance monitoring systems should be set up on SQL server. The administrators should be prepared to address any functional/ performance issues that may show up after the migration.
      • Be prepared with a plan B if the SQL migration fails, including a plan to switch back to Oracle as migration issues are worked through.
      • Review where archived data will be stored. SQL server on Azure does offer BLOB storage, which offers large data storage at a reasonable cost.

Conclusion:

A migration from Oracle to Microsoft SQL can be a smooth endeavor if you have expertise in both databases. A well-made migration plan that includes the considerations highlighted above will help minimize potential issues during and post-migration.

If you need supplemental support from a partner who has done similar migrations, Emtec’s team of experts can help. We have guided numerous clients through the considerations mentioned above to execute seamless migrations from Oracle to MS SQL.

If you are contemplating a similar migration, or would like to analyze your cloud readiness and migration options, please contact us.