Summary: The intent of this article is to introduce Microsoft technologies for data storage, movement, and transformation. These technologies support business intelligence by enabling your organization to store and access heterogeneous, quality data for analytics and reporting, in the cloud or on premise.
For an overview of the Microsoft business intelligence technologies, see Introducing Microsoft BI Reporting and Analysis Tools.
Writer: Carla Sabotta
Technical Reviewer: Douglas Laudenschlager, Sreedhar Pelluru
Published: March 2016
Applies to: Microsoft Azure, SQL Server 2016
Copyright
This document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it.
Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.
This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.
© 2016 Microsoft. All rights reserved.
Contents
Introducing Microsoft Technologies for Data Storage, Movement and Transformation 1
Supporting Business Intelligence 1
Introduction 5
Relational Data Storage 5
SQL Server Database Engine 5
More Information 5
Azure SQL Database 6
More Information 6
Azure SQL Data Warehouse 6
Non-Relational Data Storage 7
DocumentDB 7
More Information 7
Azure Storage 7
More Information 8
Data Movement and Transformation 8
Azure Data Factory 8
Why and When to Use Azure Data Factory 9
More Information 9
Microsoft Power Query for Excel 9
Enterprise Information Management with On-Premise Data 10
More Information 11
More Information 12
More Information 13
Moving Data from On Premise to Cloud 13
Integrating Technologies 13
More Information: Integrating with BI technologies 14
Conclusion 14
The intent of this article is to introduce Microsoft technologies for data storage, movement, and transformation. These technologies support business intelligence by enabling your organization to store and access heterogeneous, quality data for analytics and reporting, in the cloud or on premise.
For a brief overview of the Microsoft analytics and reporting tools, see Introducing Microsoft BI Reporting and Analysis Tools.
The Database Engine is the core SQL Server service for storing, processing, and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding applications within your enterprise.
You use the Database Engine to create relational databases for online transaction processing or online analytical processing data.
You can also set up a hybrid solution by storing SQL Server database files as Windows Azure Blobs. This allows you to create a database in SQL Server running on-premises or in a virtual machine in Windows Azure with a dedicated storage location for your data in Windows Azure Blob Storage. This feature provides several benefits for data virtualization, data movement, security and availability, and low costs and maintenance for high-availability and elastic scaling.
By running SQL Server in Azure Virtual Machines, you can create a server in the cloud. With Azure as a hosting environment, you can reduce the total cost of ownership of deployment, management, and maintenance of your enterprise database applications. Administrators and developers can still use the same development and administration tools that are available on-premises. In addition, you can provision or deprovision virtual machines to achieve elasticity on demand and connect them to your on-premises infrastructure by using Azure Virtual Network.
SQL Server Database Engine
What’s New in Database Engine
SQL Server Data Files in Windows Azure
In-Memory OLTP – Common Workload Patterns and Migration Considerations
SQL Server 2014 and Windows Azure Blob Storage Service: Better Together
SQL Server in Azure Virtual Machines
Provisioning a SQL Server Virtual Machine on Azure
Virtual Machines Pricing
SQL Database is a relational database service in the cloud based on the Microsoft SQL Server engine, which supports existing SQL Server tools, libraries and APIs.
You can scale up or scale out to thousands of databases, and protect your data with auditing, restore, and geo-replication. SQL Database provides you with the ability to restore your data to any point-in-time from any transaction up to 35 days. You can also replicate your data to an Azure region you choose and implement a geographic disaster recovery policy that meets the needs of your business.
SQL Database Overview
Create your first Azure SQL Database
Overview: management tools for SQL Database
Connecting to SQL Database: Links, Best Practices and Design Guidelines
SQL Data Warehouse is a distributed database in the Azure Cloud that can process up to petabyte volumes of relational and non-relational data. Query compute is scaled independently. You can dynamically deploy, grow, shrink, and pause compute.
This distributed database is based on the SQL Server Database Engine, with stored procedures, user-defined functions, table portioning, indexes, and collations. And with Polybase, you can also query non-relational data in Azure blob storage and Hadoop’s File System (HDFS).
Massive parallel processing (MPP) architecture and the columnstore index technology enable breakthrough performance for SQL Data Warehouse.
What is SQL Data Warehouse?
Get started: provision a SQL Data Warehouse
Get Started: Connect to SQL Data Warehouse
Microsoft Azure DocumentDB is a fully-managed, true schema-free NoSQL document database service. DocumentDB delivers consistently fast reads and writes, schema flexibility, and the ability to easily scale a database up and down on demand.
DocumentDB natively supports JSON documents. By default, it automatically indexes all the documents in the database and does not expect or require any schema or creation of secondary indices. DocumentDB enables complex ad hoc queries using a SQL language, supports well defined consistency levels, and offers JavaScript language integrated, multi-document transaction processing using the familiar programming model of stored procedures, triggers, and UDFs.
Application scenarios may include user data for interactive web and mobile applications as well as storage, retrieval, and processing of application JSON data. A database can store any number of JSON documents, as DocumentDB is well suited for applications that run at scale on the internet.
Introduction to Microsoft Azure DocumentDB
DocumentDB Learning Path
DocumentDB Pricing
Azure Storage delivers the scalable, durable, and highly available storage that large-scale applications need, and provides a storage foundation for Azure Virtual Machines.
The scalability enables you to store and process a wide range of data sizes, from hundreds of terabytes for big data scenarios to small amounts of data for a small business web site. Wherever your needs fall, you pay only for the data you’re storing. And, the storage is elastic. You can design applications for a large global audience, and scale those applications as needed - both in terms of the amount of data stored and the number of requests made against it.
An Azure storage account gives you access to Azure storage services. With a standard account, you have access to the following.
· Blob storage. Storage for large amounts of unstructured data.
· Table storage. A NoSQL database, which is an alternative to traditional relational databases.
· Queue storage. A messaging solution for asynchronous communication between application components, whether they’re running in the cloud, on a computer or on a mobile device.
· File Storage. Cloud-based file shares that enable you to migrate legacy applications to Azure quickly.
The following are additional features of Azure Storage.
· Automatically load-balances your data based on traffic. As demand increases, appropriate resources are automatically allocated.
· Accessible anywhere in the world, from applications running in the cloud, on a computer or on a mobile device.
· Supports a variety of operating systems and programming languages. Data sources are also exposed via simple REST APIs.
To ensure durability and high availability, your Azure storage account is always replicated.
Introduction to Microsoft Azure Storage
Get started with Azure Storage in five minutes
Azure Storage Pricing
Data Factory is a cloud-based data integration service that orchestrates and automates the movement and transformation of data. Just like a manufacturing factory that runs equipment to take raw materials and transform them into finished goods, Data Factory orchestrates existing services that collect raw data and transform it into ready-to-use information.
Data Factory works across on-premises and cloud data sources and SaaS to ingest, prepare, transform, analyze, and publish your data. Use Data Factory to compose services into managed data flow pipelines to transform your data using services like Azure HDInsight (Hadoop) and Azure Batch for your big data computing needs, and with Azure Machine Learning to operationalize your analytics solutions. Go beyond just a tabular monitoring view, and use the rich visualizations of Data Factory to quickly display the lineage and dependencies between your data pipelines. Monitor all of your data flow pipelines from a single unified view to easily pinpoint issues and setup monitoring alerts.
The Data Factory service provides reliable and complete view of your storage, processing, and data movement services. It helps you quickly assess end-to-end data pipeline health, pinpoint issues, and take corrective action if needed. You can also visually track data lineage and the relationships between your data across any of your sources, and see a full historical accounting of job execution, system health, and dependencies from a single monitoring dashboard.
Using the Azure Preview Portal, you can view your data factory as a diagram, view activities in a pipeline, view input and output datasets, and more.
See Customer Case Studies to learn directly from our customers how and why they are using Data Factory.
Introduction to Azure Data Factory Service
Build your first pipeline using Azure Data Factory
Data Movement Activities
Data Transformation Activities
...
Lauviah666