Microsoft Big Data Certification Exams

I previously blogged about Microsoft certification changes, and since then there have been some new Microsoft Big Data certifications exams released:

Already live:

70-773: Analyzing Big Data with Microsoft R Server

70-774: Perform Cloud Data Science with Azure Machine Learning

70-775: Perform Data Engineering on Microsoft Azure HDInsight

Now in beta:

70-776: Perform Big Data Engineering with Microsoft Cloud Services (Azure SQL Data Warehouse, Azure Data Lake Analytics, Azure Data Factory, Azure Stream Analytics)

70-778: Visualizing Data with Power BI

A few other related notes:

Posted in Certification, SQLServerPedia Syndication | 2 Comments

Azure Analysis Services web designer

Microsoft has released a preview of the Azure Analysis Services web designer.  This is a browser-based experience that will allow developers to start creating and managing Azure Analysis Services (AAS) semantic models quickly and easily.  SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS) will still be the primary tools for development, but this new designer gives you another option for creating a new model or to do things such as adding a new measure to a development or production AAS model.

A highly requested feature is that you can import a Power BI Desktop file (.pbix) into an Analysis Services database.  And once imported you can reverse engineer to Visual Studio.  Note for PBIX import only Azure SQL Database, Azure SQL Data warehouse, Oracle, and Teradata are supported at this time and Direct Query models are not yet supported for import (Microsoft will be adding new connection types for import every month).

This initial release includes three major capabilities, model creation, model editing, and doing queries.  The model can be created directly from Azure SQL Database or SQL Data Warehouse or as mentioned imported from Power BI Desktop PBIX files.  When creating from a database, you can choose which tables to include and the tool will create a DirectQuery model to your data source (DirectQuery is where the query to obtain the model is passed down into the data source and executed there).  Then you can view the model metadata, edit the Tabular Model Scripting Language (TMSL) JSON, and add measures.  There are shortcuts to open a model in Power BI Desktop, Excel, or open a Visual Studio project which is created from the model on the fly.  You can also create simple queries against the model to see the data or test out a new measure without having to use SSMS.

Keep in mind that if you save changes to the TMSL, it is updating the model in the cloud.  It is a best practice to make changes to a development server and propagate changes to production with a tool such as BISM Normalizer.

More info:

Introducing the Azure Analysis Services web designer

Azure Analysis Services Web Designer

Posted in Azure Analysis Services, SQLServerPedia Syndication, SSAS | 1 Comment

Azure SQL Data Sync 2.0

Azure SQL Data Sync has been ignored for quite some time, but has finally gotten an update (it’s in public preview).  This release includes several major improvements to the service including new Azure portal support, PowerShell and REST API support, and enhancements to security and privacy.

SQL Azure Data Sync is a Microsoft Windows Azure web service that provides data synchronization capabilities for SQL databases.  SQL Azure Data Sync allows data to be synchronized between on-premises SQL Server databases and Azure SQL databases; in addition, it can also keep multiple Azure SQL databases in sync (see Azure SQL Data Sync technical documentation).

SQL Data Sync targets the reference data replication scenario.  Its key capabilities are:

Sync between SQL Server (2005 SP2 and later) and Azure SQL databases, or between Azure SQL databases:

  • One-way and bi-directional sync
  • One-to-one and hub-spoke
  • Table filter and column filter
  • Scheduled and on-demand
  • Eventual consistency

Active Geo-Replication, in contrast, targets the GeoDR scenario for Azure SQL Database by replicating the database to another region.  It only supports one-way replication (secondaries are read-only), replication is at database granularity, there is no database or column/row filter support, and it is only available for Premium service tier.

Data Sync is now available in the new Azure portal.  If you would like to try Data Sync refer to this tutorial.  Existing users will be migrated to the new service starting June 1, 2017.  For more information on migration look at the blog post “Migrating to Azure SQL Data Sync 2.0.”

More info:

Azure SQL Data Sync Refresh

Sync data across multiple cloud and on-premises databases with SQL Data Sync

Getting Started with Azure SQL Data Sync (Preview)

Posted in Azure, SQLServerPedia Syndication | Comments Off on Azure SQL Data Sync 2.0

4TB disk sizes for Azure IaaS VMs available

Microsoft has introduced two new disk sizes for Azure IaaS VMs in P40 (2TB) and P50 (4TB) for both managed and unmanaged Premium Disks and S40 (2TB) and S50 (4TB) for both managed and unmanaged Standard Disks.  This enables customers to add 4x more disk storage capacity per VM.  Customers can now provision up to a total of 256TB disk storage on a GS5 VM using 64 disks with 4TB capacity.  This means the max SQL Server database size on a Azure VM goes from 64TB to 256TB!

Premium Disks Standard Disks
Managed Disks P40, P50 S40, S50
Unmanaged Disks P40, P50 Max up to 4,095GB

Larger Premium Disks P40 and P50 will support your IO intensive workload and therefore offer higher provisioned disk performance. The maximum Premium Disk IOPS and bandwidth is increased to 7,500 IOPS and 250 MBps respectively.  Standard Disks, of all sizes, will offer up to 500 IOPS and 60 MBps.

P40 P50 S40 S50
Disk Size 2048GB 4095GB 2048GB 4095GB
Disk IOPS 7,500 IOPS 7,500 IOPS Up to 500 IOPS Up to 500 IOPS
Disk Bandwidth 250 MBps 250 MBps Up to 60 MBps Up to 60 MBps

You can create a larger disk or resize existing disks to larger disk sizes with your existing Azure tools through Azure Resource Manager (ARM) or the Azure Portal.  Azure Backup and Azure Site Recovery support for larger disks is coming soon (current timeline is before end of July).

You can visit the Managed Disk Pricing and unmanaged Disk Pricing pages for more details about pricing.

More info:

Azure increases the maximum size and performance of Azure Disks

Posted in Azure, SQLServerPedia Syndication | 2 Comments

Data lake details

I have blogged before about data lakes (see What is a data lake? and Why use a data lake?), and wanted to provide more details on this popular technology, some of which I cover in my presentation “Big data architectures and the data lake“.  In short, the data lake is a storage repository, usually Hadoop, that holds a vast amount of raw data in its native format until it is needed.

The data lake should be the center of just about any big data solution for these major reasons:

  • Inexpensively store unlimited data
  • Collect all data “just in case”
  • Store data with no modeling – “Schema on read”
  • Complements enterprise data warehouse (EDW)
  • Frees up expensive EDW resources
  • Quick user access to data
  • Allows for Hadoop tools for ETL
  • Easily scalable
  • With Hadoop, high availability built-in

The data lake introduces a new data analysis paradigm shift:

OLD WAY: Structure -> Ingest -> Analyze

NEW WAY: Ingest -> Analyze -> Structure

This allows you to avoid a lot of up-front work before you are able to analyze data.  With the old way, you have to know the questions to ask.  The new way supports situations when you don’t know the questions to ask.

This solves the two biggest reasons why many EDW projects fail:

  • Too much time spent modeling when you don’t know all of the questions your data needs to answer
  • Wasted time spent on ETL where the net effect is a star schema that doesn’t actually show value

Data Lake layers:

  • Raw data layer– Raw events are stored for historical reference. Also called staging layer or landing area
  • Cleansed data layer – Raw events are transformed (cleaned and mastered) into directly consumable data sets. Aim is to uniform the way files are stored in terms of encoding, format, data types and content (i.e. strings). Also called conformed layer
  • Application data layer – Business logic is applied to the cleansed data to produce data ready to be consumed by applications (i.e. DW application, advanced analysis process, etc). This is also called by a lot of other names: workspace, trusted, gold, secure, production ready, governed
  • Sandbox data layer – Optional layer to be used to “play” in.  Also called exploration layer or data science workspace

Be aware that you still need data governance so your data lake does not turn into a data swamp!  Having a data lake does not lessen the data governance that you would normally apply when building a relational data warehouse.  In fact, it usually requires more data governance.  So look at tools such as the Azure Data Catalog to keep track of all your data.

Data typically lands in products such as Hadoop Distributed File System (HDFS) or the Azure Data Lake Store (ADLS).   Since these technologies are just storage, you need other technologies for the compute part.  For example, here are ways to clean data in ADLS:

Since you will typically have both a Data Lake and a relational data warehouse when building a big data solution, it’s important to understand the roles of each:

Data Lake/Hadoop (staging and processing environment)

  • Batch reporting
  • Data refinement/cleaning
  • ETL workloads
  • Store historical data
  • Sandbox for data exploration
  • One-time reports
  • Data scientist workloads
  • Quick results

Data Warehouse/RDBMS (serving and compliance environment)

  • Low latency
  • High number of users
  • Additional security
  • Large support for tools
  • Easily create reports (Self-service BI): A data lake is just a glorified file folder with data files in it – how many end-users can accurately create reports from it?  Very little, hence a major reason to use a RDBMS

A question I get frequently is if all relational data should be copied to the data lake, especially if some of the relational data is not needed for reporting or analytics in the data lake (just in the data warehouse).  There still could be reasons to copy the data to the data lake, such as for backup purposes, to use low-cost storage for raw data saving space on the data warehouse, to use Hadoop tools, or to offload the refining of the data from the data warehouse (especially if ETL jobs on the data warehouse are taking too long).  But in some cases you may want to skip the data lake, especially if you have many existing SSIS packages as you can minimize the changes to them (i.e. just changing the destination source).  Also keep in mind the extra time needed to export data out from a relational database into a flat file before copying it to the data lake.

One issue to be aware of is when you have to move a lot of data each day from a relational database to a data lake.  You may want to look at 3rd-party products for Change Data Capture (CDC) for high volumes of data to get updates of your source system into a data lake such as HVR and Attunity.  Performing updates to rows of data in files sitting in a Hadoop/HDFS data lake can be very slow compared to appends.

Posted in Azure Data Lake, SQLServerPedia Syndication | 3 Comments

Azure DevTest Labs

I have been working at Microsoft now for 3 years and 4 months (side note: it’s by far the best company I have ever worked for).  You would think by now I know about every Azure product, but we release new products and services at such a tremendously fast pace that almost weekly I discover something I did not know about.  Today was one of those days as I discovered Azure DevTest Labs, which was made generally available in May 2016 (it public previewed in November 2015).

Here is the overview:

Developers and testers are looking to solve the delays in creating and managing their environments by going to the cloud.  Azure solves the problem of environment delays and allows self-service within a new cost efficient structure.  However, developers and testers still need to spend considerable time configuring their self-served environments.  Also, decision makers are uncertain about how to leverage the cloud to maximize their cost savings without adding too much process overhead.

Azure DevTest Labs is a service that helps developers and testers quickly create environments in Azure while minimizing waste and controlling cost.  You can test the latest version of your application by quickly provisioning Windows and Linux environments using reusable templates and artifacts.  Easily integrate your deployment pipeline with DevTest Labs to provision on-demand environments.  Scale up your load testing by provisioning multiple test agents, and create pre-provisioned environments for training and demos.

Azure DevTest Labs addresses the problems in Dev/Test environments today majorly through four aspects:

  • Quickly be “ready to test” – DevTest Labs enables you to create pre-provisioned environments with everything your team needs to start developing and testing applications.  Simply claim the environments where the last good build of your application is installed and get working right away.  Or, use containers for even faster and leaner environment creation
  • Worry-free self-service – DevTest Labs makes it easier to control costs by allowing you to set policies on your lab – such as number of virtual machines (VM) per user and number of VMs per lab.  DevTest Labs also enables you to create policies to automatically shut down and start VMs
  • Create once, use everywhere – Capture and share environment templates and artifacts within your team or organization – all in source control – to create developer and test environments easily
  • Integrates with your existing toolchain – Leverage pre-made plug-ins or our API to provision Dev/Test environments directly from your preferred continuous integration (CI) tool, integrated development environment (IDE), or automated release pipeline. You can also use our comprehensive command-line tool

Jeff Gilbert’s TechNet blog has some great blogs on Azure DevTest Labs as well as Praveen Kumar Sreeram, and there are some excellent short videos by Microsoft to help you get started.

You’ll need a subscription that provides you monthly Azure credits to use DevTest labs.  Besides the pay-as-you-go option, there are free options and subscription options:

* MSDN Platforms is available exclusively through Microsoft Volume Licensing. For pricing and purchase details, contact your Microsoft account representative, Microsoft Partner, or an authorized volume licensing reseller.

More info:

Getting to know Azure DevTest Labs

How to Use Azure DevTest Labs for Test Environments and Dev Machines

Azure DevTest Labs.

More about Azure DevTest Labs

Posted in Azure, SQLServerPedia Syndication | 2 Comments

Data Science Virtual Machine

The Data Science Virtual Machine (DSVM) is a customized VM image on Microsoft’s Azure cloud built specifically for doing data science.  It has many popular data science and other tools pre-installed and pre-configured to jump-start building intelligent applications for advanced analytics.  So instead of you having to create a VM and download and install all these tools which can take many hours, within a matter of minutes you can be up and running.

The DSVM is designed and configured for working with a broad range of usage scenarios.  You can scale your environment up or down as your project needs change.  You are able to use your preferred language to program data science tasks.  You can install other tools and customize the system for your exact needs.

It is available on Windows Server 2012 (create), Windows Server 2016 (create) and on Linux – either Ubuntu 16.04 LTS (create) or on OpenLogic 7.2 CentOS-based Linux distributions (create).

The key scenarios for using the Data Science VM:

  • Preconfigured analytics desktop in the cloud
  • Data science training and education
  • On-demand elastic capacity for large-scale projects
  • Short-term experimentation and evaluation
  • Deep learning

The DSVM has many popular data science and deep learning tools already installed and configured.  It also includes tools that make it easy to work with various Azure data and analytics products.  You can explore and build predictive models on large-scale data sets using the Microsoft R Server or using SQL Server 2016 (note that R Server and SQL Server on the DSVM are not licensed for use on production data).  A host of other tools from the open source community and from Microsoft are also included, as well as sample code and notebooks.  See the full list here and see the latest new and upgraded tools here.

Finally, for Windows users check out Ten things you can do on the Data science Virtual Machine and for Linux users check out Data science on the Linux Data Science Virtual Machine.  For more information on how to run specific tools for Windows see Provision the Microsoft Data Science Virtual Machine and for Linux see Provision the Linux Data Science Virtual Machine.

More info:

Data Science Virtual Machine – A Walkthrough of end-to-end Analytics Scenarios (video)

Introduction to the cloud-based Data Science Virtual Machine for Linux and Windows

Introducing the new Data Science Virtual Machine on Windows Server 2016

Posted in SQLServerPedia Syndication | 3 Comments

Data Warehouse Fast Track Reference Guide for SQL Server 2016

I had previously blogged about the Data Warehouse Fast Track for SQL Server 2016, a joint effort between Microsoft and its hardware partners to deliver validated, pre-configured solutions that reduce the complexity of implementing a data warehouse on SQL Server Enterprise Edition.

Now available are two new excellent white papers to give you a better understanding of Fast Track for SQL Server 2016:

Introducing Microsoft Data Warehouse Fast Track for SQL Server 2016

Data Warehouse Fast Track Reference Guide for SQL Server 2016

Posted in Data warehouse, Fast Track, SQLServerPedia Syndication | 2 Comments

SSAS high availability

If you are looking at providing high availability (HA) for SSAS, here are 3 options:

  1. Install SSAS on a Windows Server Failover Cluster (WSFC)
    Here’s a good article. The main issue with this option is that SSAS isn’t cluster-aware, so if windows is “OK” but SSAS (the service) is hung, it won’t failover.  Also check out How to Cluster SQL Server Analysis Services
  2. Network Load Balancing (NLB) across a SSAS Scale-Out Query Cluster
    Basically just load balancing queries across 1+N servers that are all hosting a separate copy of the tabular or multidimensional model.  If a query-server goes down, there’s still several others available to resolve the query.  This provides scalability and availability.  Unfortunately, it is not completely transparent as you have to manage:
    – Configuration of the load balancer
    – Deployment of updates (for the analysis services databases you can do detach->file copy to other servers->reattach, analysis services database backup/restore, or process data on a “process” server and use database synchronization to update the read-only instances you put behind the load balancer).  If you have to have 24×7 availability, you have to put a node offline when a node is already synchronized, otherwise you accept having different versions of the same database available at the same moment
  3. Azure Analysis Services
    This new service in the cloud has options for high availability.  It makes it super easy to (programmatically) spin up another server and restore a backup.  Just keep in mind the new server does not have the same address, so you have to manage the client connection and this is not transparent.  Also note this service has a 99.9% SLA

On a side note, SSAS can use a SQL Server database in an Always On availability group as a data source: Analysis Services with Always On Availability Groups.

Posted in SQLServerPedia Syndication, SSAS | 5 Comments

SQL Data Warehouse reference architectures

With so many product options to choose from for building a big data solution in the cloud, such as SQL Data Warehouse (SQL DW), Azure Analysis Services (AAS), SQL Database (SQL DB), and Azure Data Lake (ADL), there are various combinations of using the products, each with pros/cons along with differences in cost.  With many customers looking at using SQL DW, I wanted to mention various reference architectures that I have seen, ordered by most cost to lowest cost:

  1. Do staging, data refinement and reporting all from SQL DW.  You can scale compute power up when needed (i.e. during staging, data refinement, or large number of users doing reporting) or down to save costs (i.e. nights and weekends when user reporting is low).  The pros of this option are by reducing the number of technologies you are building a simpler solution and reducing the number of copies of the data.  The cons are since everything is done on SQL DW you can have performance issues (i.e. doing data refinement while users are reporting), can hit the SQL DW concurrent query limit, and can have a higher cost since SQL DW is the highest-cost product, especially if you are not able to pause it.  Pausing it reduces your cost to zero for compute, only having to pay for storage (see Azure SQL Data Warehouse pricing), but no one can use SQL DW when paused
  2. Do staging and data refinement in a Hadoop data lake, and then copy all or part of the data to SQL DW to do reporting.  This saves costs in SQL DW by offloading data refinement, and gives you the benefit of using a data lake (see What is a data lake? and Why use a data lake?).  You save costs by not having to scale up SQL DW to do the data refinement (scaling up would minimize affecting reporting performance and refine data quicker) and by not having to store as much data in SQL DW.  You also save costs by archiving data in the data lake and using PolyBase to access it (be aware these queries could be slow as PolyBase does not support query pushdown in SQL DW).  A con of this architecture is having an extra copy of the data along with the extra ETL needed
  3. Do staging and data refinement in SQL DW, and copy some or all data to one or more data marts (in SQL DB or SQL Server in a VM) and/or one or more cubes (in AAS or SSAS in a VM) for reporting, which is considered a “Hub-and-Spoke” model.  Scale down SQL DW after data refinement and use it for a limited amount of big queries.  This overcomes the SQL DW concurrent query limit by having users query the data mart/cube and saves costs by querying less expensive options.  You also get the benefits that come with a cube such as creating a semantic layer and row-level security that is not available in SQL DW (see Why use a SSAS cube?).  This architecture can also be combined with the previous architecture to add in a data lake.  A con of this architecture is having extra copies of the data along with the extra ETL needed
  4. Do staging and data refinement in SQL DW, and copy all data to a data mart (SQL DB or SQL Server in a VM) and/or a cube (AAS or SSAS in a VM) for reporting.  Pause SQL DW after the staging and data refinement is done.  This is used when giving users access to SQL DW will impact ELT and/or user queries wouldn’t be as responsive as needed, or when cost is a top priority (you only pay for storage costs when SQL DW is paused).  A con of this architecture is having extra copies of the data along with the extra ETL needed, and not having SQL DW available for big queries

More info:

Using SQL Server to Build a Hub-and-Spoke Enterprise Data Warehouse Architecture

Hub-And-Spoke: Building an EDW with SQL Server and Strategies of Implementation

Posted in Azure SQL DW, Data Lake, Data warehouse, SQLServerPedia Syndication | 6 Comments