Accelerate your Oracle data warehouse migration to dedicated SQL pools in Azure Synapse Analytics

Azure Synapse Analytics is a limitless analytics service that brings together enterprise data warehousing and Big Data Analytics. It gives you the freedom to query data on your terms at scale using Synapse SQL. Dedicated SQL pools in Azure Synapse Analytics are powerful massively parallel processing (MPP) distributed query engine which offers enterprise data warehousing capabilities for workloads with predictable performance and cost. This Massively parallel processing (MPP) engine uses a node-based architecture to optimize the quick execution of the most complex queries on large volumes of data.

Modernizing your on-premises data warehouse by migrating to Azure Synapse reduces maintenance costs, greatly improves performance, and provides high availability. However, migrating from an on-premises data warehouse to a Cloud data warehouse can be complex and time consuming. Extract, transform and load (ETL) processes, large amounts of data and reports built over the years need to be migrated to Azure Synapse quickly while navigating differences in architecture and design, database objects and data types, performance tuning, ETL and SQL.

Due to the enhanced security and scalability of compute and storage resources that Azure Synapse Analytics offers along with flexibility and seamless integration; customers implement a change management strategy to migrate to dedicated SQL pools in Azure Synapse Analytics from their existing on-Premises data warehouse sources. In this blog, we introduce the migration guide which not only resolves the common issues faster but also helps accelerate the migration of Oracle data warehouse to dedicated SQL pools in Azure Synapse Analytics. Here is what the oracle migration guide covers:

1. Design and performance for Oracle migration – We start by describing the database, data types, and database objects that need to be changed to migrate to dedicated SQL pools in Azure Synapse Analytics. Then we discuss the similarities and differences in performance tuning along with best practices that can be adapted for a highly performant data warehouse along with various ingestion methods supported.
2. Data, ETL, and load migration considerations – Here we set out the initial decisions that need to be made, and best practices to minimize migration risk. We describe a suggested approach to determining the size of the database and its volume, along with ETL design and tools that can be utilized.
3. Security access and Operations - Both Oracle and Azure Synapse Analytics implement database access control via a combination of users, roles, and permissions. Both use standard SQL and therefore it may be possible to automate the migration of existing user ids, roles, and permissions. With minimal risk and user impact, most Oracle operational tasks can be implemented in Azure Synapse Analytics. This section contains how all security access and operations can be easily migrated from Oracle.
4. Visualization and reporting for Oracle migrations – This section contains the considerations and approach to analyze and migrate business intelligence dashboards and reports.

5. Minimizing SQL issues - There are several differences in Structured Query Language (SQL) support between Oracle and Azure Synapse Analytics, including data definition language (DDL) and data manipulation language (DML). This section contains the most common approaches to bridge this gap along with guidelines to convert Oracle built-in SQL functions to Azure Synapse Analytics.
6. Microsoft and 3^rd party tools – SQL Server Migration Assistant (SSMA) for Oracle not only automates code translations for small to medium sized workloads but also helps adapt code between Oracle and Azure Synapse Analytics. This section contains the details of all the Microsoft as well as the products that Microsoft partners offer for migrations.
7. Implementing modern data warehouses – The On-Premises data warehouse when migrated to dedicated SQL pools in Azure Synapse Analytics can be integrated seamlessly with Microsoft’s Azure analytical ecosystem. The migrated data warehouse can be modernized by taking advantage of Microsoft technologies such as Azure Data Lake Storage for ingestion and cost-effective storage, Azure Data Factory for self-service data integration and Common Data Model to share consistent trusted data across multiple technologies. Also, Microsoft’s data science technologies and Azure HDInsight can be leveraged to process massive amounts of data in a cost-effective manner and to predict outcomes using Azure Machine Learning. Azure Event Hubs, Azure Stream Analytics and Apache Kafka help to integrate streaming data. All the Microsoft technologies when combined not only unlocks the potential to derive past, present, and future insights but also helps business discover more potential data sources and make data-driven informed decisions thereby helping businesses flourish to greater heights.

We’re happy to share the new migration guide to dedicated SQL pools in Azure Synapse Analytics from Oracle. It provides steps, process and guidelines on migrations and insights on the capabilities of SQL Server Migration Assistant (SSMA) for Oracle, which helps automate the migration. Take advantage of the migration guide to modernize your data warehouse and accelerate your business by integrating with the Azure Synapse Analytics ecosystem.

Check out our Migration Guides for Teradata and IBM Netezza migrations.