5 June 2017 Andrey Ivanov
One of the largest North American agricultural enterprises has been envisioning and designing a new cloud-based Business Intelligence and Data Warehousing architecture. They were using...
Typical challenges that we see
Evaluating Azure SQL Data Warehouse with enough data
Proof-of-concept (POC) projects often don't include enough data to show off the performance of Azure SQL Data Warehouse
Microsoft designed Azure SQL Data Warehouse for unlimited scale using a massively parallel computer (MPP) architecture. If the existing Oracle Data Warehouse solution uses a large server using symmetric multiprocessor (SMP) under 10 terabytes of data, performance using Azure SQL Data Warehouse may produce disappointing results.
We recommend POC projects with Azure SQL Data Warehouse using a set of tables that are more than 10 terabytes to showcase performance against huge sets of data.
In order to migrate the data to Azure SQL Data Warehouse, we can utilize several methods for migrating data from an on-premises Oracle Data Warehouse tables to Azure SQL Data Warehouse including:
- Using parallel servers to export tables as CSV files share and then upload them to Azure storage over Azure ExpressRoute.
- Taking a similar approach but transferring the data to internal SATA HDDs or SSDs and then shipping them to an Azure data center using the Microsoft Azure Import/Export service.
Once the data is in Azure blob store, we would use a combination of Polybase with partition switching to optimize the data load into the large fact tables on Azure SQL Data Warehouse.
SQL Server Migration Assistant (SSMA) doesn't support Azure SQL Data Warehouse as a destination
While Azure SQL Data Warehouse uses Transact SQL, distribution, storage, and indexing strategies are very different than SQL Server.
In general, Oracle indexing of tables for a data warehouse typically is not applicable for deployment with Azure SQL Data Warehouse. As an early partner with Azure SQL Data Warehouse along with our experience with Oracle, we understand the intent of the Oracle applications.
As part of our 12 step database migration steps, we typically take the following approach for creating the new schema on Azure SQL Data Warehouse:
- Use SSMA to transfer create the base table definitions for the fact and dimension tables
- Define a partitioning scheme for the table to optimize data loading and query performance
- Use Oracle table statistics to define the appropriate distribution, table format, and index strategy
- Once the data is loaded into the tables, we’ll compare the performance of the Oracle server and the Azure SQL Data Warehouse to see if there are any improvements needed
Learn more about Data Warehouse Migration to Microsoft DW Technologies
Contact us Getting Started
At DB Best, we have a set of entry-level offers to help you understand your modernization landscape and demonstrate the value of moving your Oracle data warehouses to Azure SQL Data Warehouse. We can help you chose the best option to Migrate Oracle DW to Azure SQL Data Warehouse, modifying the architecture of your entire system to add the latest business intelligence and advanced analytics solutions.
For qualified Microsoft customers, we have access to Microsoft Business Incentive Funds where Microsoft will co-invest for training, future-state architectural design engagements, and proof-of-concept projects.
Building an Ultimate Cloud-based Business Intelligence Solution for Agriculture
One of the largest North American agricultural enterprises was considering a new cloud-based Business Intelligence and Data Warehousing architecture. They were using a fragmented system based on Oracle database with reports using a mix of SQL Server Reporting Services (SSRS) and Excel dashboards.
We came up with a solution that allowed them to consolidate all reports with Power BI dashboards. In addition, we prototyped as part the proof of concept migrated the original Oracle database to Azure SQL Data Warehouse. The end result is a proposal that will reduce their license costs and improve the performance of the newly created cloud database system.
Check out our video below to see how we approached this proof of concept solution.
Contact us today
Learn more
We have a great set of blog posts and data sheets to help you better understand our capabilities as the leader in database migration services and prove out possibilities and expertise in moving Oracle data warehouses to Microsoft Azure cloud platform.
Blog posts
Building an Ultimate Cloud-based Business Intelligence Solution for Agriculture
Data warehouse integration to embrace data analytics
27 June 2017 Stanislav Andrenko
Our customer has been supporting Oracle based online transaction processing (OLTP) system. They decided to take advantage of the modern data analysis capabilities of SQL Server's data ...
Reference data sheet
Oracle to SQL Migration
21 September 2016 Andrey Ivanov
Leveraging Microsoft SQL Server, DB Best can help Oracle customers achieve the performance, scale, and security that their mission-critical applications require while keeping costs und...
Reference white paper
Guide to migrating from Oracle to SQL Server and Azure SQL Database
8 October 2015 Andrey Ivanov
This white paper explores challenges that arise when you migrate from an Oracle 7.3 database or later to SQL Server 2014. It describes the implementation differences of database objec...
Request a quote