Today, everything needs to be instant, even our data. With the rise of cloud-based services like Microsoft Azure SQL Data Warehouse, companies are now able to gather the information they need whenever they need it with on-demand scalability. We will walk through Azure SQL Data Warehouse (ASDW), its implementation use cases, as well as how to load and visualize the data.
With digital transformation, companies are facing increased demand to analyze data on the fly to obtain valuable insights in a timely manner. This might not be possible with existing infrastructure due to rapidly-growing volumes of structured and unstructured data. On-premises solutions take time to provision or scale up the resources. The emergence of cloud technologies brought agility, on-demand scalability, and quick return on investment. Smartbridge clients are now turning their attention to their legacy data warehouse as an opportunity to further leverage cloud-based services to modernize their data management architecture.
What is Azure SQL Data Warehouse?
Azure SQL Data Warehouse (ASDW) is a massively parallel processing (MPP) data warehouse with unlimited storage and computing power which can be scaled in and scaled out as per demand. Azure SQL Data Warehouse is optimized for reporting purposes. It is helping companies to analyze their structured and unstructured data faster to realize value sooner. With Polybase, SQL Server tables can be joined with unstructured data residing in Azure Blob Storage without need to bring that data into ASDW.
Azure SQL Data Warehouse Architecture
ASDW has a similar architecture to other managed MPP databases that separate storage from compute resources. Built on top of Azure Blob Storage, it dynamically provisions those compute resources to query data that resides there.
ASDW is divided between two types of nodes: Control (the “brain”) and Compute (the “engine”).
- Responsible for creating a query execution plan, breaking that query into parallel phases to be executed by each of the compute nodes
- Stores query metadata, query optimization and distribution
- Contains an instance of SQL Database responsible for processing the data
- Returns query results to the control node to be aggregated once processed
Azure SQL DWH Implementation Use Cases
There are multiple implementation use cases available to suit company needs and long term plans.
1. New: Create Azure SQL DWH on Microsoft Azure
In this use case, a completely new Azure SQL Data Warehouse is created and data is loaded from sources. This is for companies who want to build their first data warehouse.
2. Extend: On-Premises Enterprise Data Warehouse with Azure SQL Data Warehouse
In this use case, data from the on-premises data warehouse can be replicated to Azure SQL Data Warehouse to achieve higher performance and mitigate bottlenecks in data analysis and visualization.
3. Lift and Shift: On-Premises Data Warehouse to Azure SQL Data Warehouse
In this approach, an on-premises data warehouse is migrated into Azure SQL Data Warehouse. This is accomplished using Microsoft Data Migration Service Utility. Alternatively, a current backup of SQL Server databases, residing in Azure Blob storage, can be restored to a managed instance of Azure SQL Data Warehouse.
Data Integration/Data Load
SQL Data Warehouse supports loading and exporting data through several Microsoft tools as well as third-party tools such as Informatica, Fivetran, Striim, Talend, and Alooma.
The most commonly used Microsoft tools are Azure Data Factory, SSIS, Polybase, and BCP.
Azure Data Factory (ADF)
- Fully managed ETL service in cloud
- Allows creation of data-driven workflows in the cloud for orchestrating and automating data movement and data transformation
- Can load data from both relational and non-relational sources into Azure SQL Data Warehouse. It provides initial and incremental data load capabilities and jobs can be scheduled
- Allows re-use of SSIS packages (see below)
- Stored procedures with transformation logic can be implemented
- Command-line utility designed for copying data to/from Microsoft Azure Blob, file, and table storage, using simple commands
SQL Server Integration Services (SSIS)
- SSIS package can be used to load data from SQL Server into Azure SQL Data Warehouse
- Allows restructuring, transformation and cleansing of data
- SSIS uses ADO NET to connect to SQL Data Warehouse
- SSIS packages can be executed into Azure Data Factory using Integration Runtime
- No ETL is required
- Uses familiar T-SQL language to run queries on external data
- Can push query operations to Hadoop
- External data can be joined with data stored in ASDW
Apart from these tools, Microsoft also provides utilities to migrate an on-premises data warehouse into Azure SQL Data Warehouse.
Azure SQL Data Warehouse supports leading data visualization tools for data analysis and reporting.
- MicroStrategy’s connector enables web and desktop connectivity workflows and enhances the Relational Engine with support for Azure SQL Data Warehouse
- Connects to Azure SQL Data Warehouse through Tableau SQL Server connector
- Embedded Power BI as well Desktop Power BI can be connected for data visualization
With Microsoft Azure SQL Data Warehouse implementation, companies are achieving a quicker return on their data management investment. Some of the most valuable benefits are:
Improved Analytics and Decision Making
- With essentially unlimited scalability of cloud computing resources, companies achieve improvements in performance more easily when compared to on-premises architecture
- Data scientists and business analysts can access and analyze data from multiple sources within a short time and bring agility to their business decision making
- It’s secure and reliable, with minimum downtime and data loss
Quick Setup and Lower Upfront Infrastructure Costs
- No capital expenditure
- There is no waiting period to procure infrastructure
- Resources can be scaled up and down as needed, and paused when not in use
- Reduced workload on Database Administrators and Developers
- Overall reduction in overhead
Looking for more on Data Management?
Explore more insights and expertise at smartbridge.com/datamanagement
Keep Reading: Azure Data Factory for Snowflake — PART 1
Originally published at https://smartbridge.com on March 11, 2019.