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.
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.
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.
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.
Visualization and reporting for Oracle migrations – This section contains the considerations and approach to analyze and migrate business intelligence dashboards and reports.
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.
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.