Total Cost of Ownership (TCO) Calculator

For a long time clients would ask me how to determine the cost savings by migrating their applications and databases to Azure.  I never had a good answer until now: The Total Cost of Ownership (TCO) Calculator.  Now in preview, just provide a brief description of your on-premises environment to get an instant estimate of the cost savings you can realize by migrating your application workloads to Microsoft Azure.  Here is an example:

Enter the inputs:


Enter the assumptions:


Hit the “Calculate” button, and out comes the report:



Posted in Azure, SQLServerPedia Syndication | 3 Comments

Azure Advisor

Azure Advisor is a really cool personalized recommendation engine that provides you with proactive best practices guidance on High Availability, Security, Performance and Cost when using Azure.  It is in public preview.  Learn more about Azure Advisor here.

Azure Advisor analyzes your resource configuration and usage telemetry to detect risks and potential issues.  It then draws on Azure best practices to recommend solutions that will reduce your cost and improve the security, performance, and reliability of your applications.

It is real easy to use.  Just login to the Azure portal and click here (or click on More services link available at the bottom of the Menu and select Azure Advisor).  You then pick the subscription you want to analyze, filter by resource groups if you wish, and click the “Get recommendations” button.  You will then see a list of recommendations, that you can filter based on category (High Availability, Security, Performance, Cost):


You can then click to see the list of recommendations (some which have a snooze button so you can delay or dismiss taking action):


You can then drill down to see the list of resources the recommendation is for:


And then you can perform the fix:


This is a easy way to optimally configure your Azure resources!

More info:


A Quick Tour of the Microsoft Azure Advisor

Posted in Azure, SQLServerPedia Syndication | Comments Off on Azure Advisor

Analytics Platform System 2016 Release

Microsoft has a new release for the Analytics Platform System (APS).  This appliance update is called APS 2016 and has been released to manufacturing and is now generally available.  APS is Microsoft’s scale-out Massively Parallel Processing fully integrated system for data warehouse specific workloads.

This release is built on the latest SQL Server 2016 release, offers additional language surface coverage to aid in migrations from SQL Server and other platforms, adds PolyBase connectivity to the current versions of Hadoop from Hortonworks, additional PolyBase security with Kerberos support and credential support for Azure Storage Blobs, greater indexing and collation support and improvements to the setup and upgrade experience with FQDN support.

The majority of these capabilities have shipped in the monthly releases of Azure SQL Data Warehouse service and/or SQL Server 2016 following the cloud first principle of shipping, getting feedback, and improving rapidly across all of our products.

What’s New in the Release:

SQL Server 2016 re-platform and TSQL compatibility improvements to reduce migration friction from SQL SMP

Data Types:

Windowing Functions:

Security Functions:

Additional Functions:

  • RAND() – returns a pseudo-random float value from 0-1
  • DBCC SHRINKDATABASE() – shrinks the size of data and log files in a database
  • sp_spaceused() – displays the disk space used or reserved in a table or database

Polybase/Hadoop Enhancements

  • Support for the latest distributions from Hortonworks (HDP 2.4 and HDP 2.5)
  • Kerberos support via database scoped credentials
  • Credential support with Azure Storage Blobs

Install/Upgrade Enhancements

  • WSUS updated images
  • Security updates taken in updated firmware and drivers
  • Fully Qualified Domain Name support
  • APS installer includes PAV and hotfixes
  • The latest generation processor support (Broadwell), DDR4 DIMMs, and improved DIMM throughput

The latest APS update is an addition to already existing data warehouse portfolio from Microsoft, covering a range of technology and deployment options that help customers get to insights faster.  Customers exploring data warehouse products can also consider SQL Server with Fast Track for Data Warehouse or Azure SQL Data Warehouse, a cloud based fully managed service.

For more details about these features, please visit our online documentation or download the client tools.

Posted in PDW/APS, SQLServerPedia Syndication | 4 Comments

PASS Summit Announcements: PolyBase enhancements

Microsoft usually has some interesting announcements at the PASS Summit, and this year was no exception.  I’m writing a set of blogs covering the major announcements.  Next up is the PolyBase enhancements.

PolyBase is a technology that accesses and combines both non-relational and relational data, all from within SQL Server.  It allows you to run queries on external data in Hadoop or Azure blob storage.  The queries are optimized to push computation to Hadoop via MapReduce jobs.

By simply using Transact-SQL (T-SQL) statements, you an import and export data back and forth between relational tables in SQL Server and non-relational data stored in Hadoop or Azure Blob Storage.  You can also query the external data from within a T-SQL query and join it with relational data.

The major use cases for PolyBase are:

  • Load data: Use Hadoop as an ETL tool to cleanse data before loading to data warehouse with PolyBase
  • Interactively Query: Analyze relational data with semi-structured data using split-based query processing
  • Age-out Data: Age-out data to HDFS and use it as ‘cold’ but query-able storage

The main benefits of PolyBase are:

  • New business insights across your data lake
  • Leverage existing skillsets and BI tools
  • Faster time to insights and simplified ETL process

PolyBase supports the following file formats: Delimited text (UTF-8), Hive RCFile, Hive ORC, Parquet, gzip, zlib, Snappy compressed files.

For more details see: Introduction to PolyBase presentation, PolyBase Guide, and the list of supported data sources here.


Polybase was first made available in Analytics Platform System in March 2013, and then in SQL Server 2016.  The announcement at the PASS Summit was that by preview early next year, in addition to Hadoop and Azure blob storage, PolyBase will support Teradata, Oracle, SQL Server, and MongoDB in SQL Server 2016.  And the Azure Data Lake Store will be supported in Azure SQL Data Warehouse PolyBase.

With SQL Server 2016, you can create a cluster of SQL Server instances to process large data sets from external data sources in a scale-out fashion for better query performance (see PolyBase scale-out groups):


In summary, the main reasons to use PolyBase:

  • Ability to integrate SQL Server with data stored in HDFS or Windows Azure Storage BLOB
  • Commodity hardware and storage are cheap, easily distributed on HDFS; increases data reliability at a low cost
  • Increasing number of different types of data; structured, unstructured, semi-structured (Can have them stored on the best system suitable and queried in one place)
  • Increasing size of data and strong aversion to data deletion due to company culture or restrictions

More info:

Integrating Big Data and SQL Server 2016

PolyBase in SQL Server 2016 video

Polybase in SQL Server – Big Data Queried with T-SQL video

Why Polybase?

Posted in PolyBase, SQLServerPedia Syndication | 2 Comments

Microsoft Connect(); announcements

Microsoft Connect(); is a developer event from Nov 16-18, where plenty of announcements are made.  Here is a summary of the data platform related announcements:

More info:

The Intelligent Data Lake

Announcing the Next Generation of Databases and Data Lakes from Microsoft

Azure Data Lake Microsoft Virtual Academy

Posted in Data Lake, HDInsight, SQL Server, SQLServerPedia Syndication | Comments Off on Microsoft Connect(); announcements

PASS Summit Announcements: Microsoft Professional Program for Big Data

Microsoft usually has some interesting announcements at the PASS Summit, and this year was no exception.  I’m writing a set of blogs covering the major announcements.  Next up is the Microsoft Professional Program for Big Data.

A few months back, Microsoft started the Microsoft Professional Program for Data Science (note the program name change from Microsoft Professional Degree to Microsoft Professional Program, or MPP).  This is online learning via as a way to learn the skills and get the hands-on experience that a data science role requires.  You may audit any courses, including the associated hands-on labs, for free.  However, to receive credit towards completing the data science track in the Microsoft Professional Program, you must obtain a verified certificate for a small fee for each of the ten courses you successfully complete in the curriculum.  The course schedule is presented in a suggested order, to guide you as you build your skills, but this order is only a suggestion.  If you prefer, you may take them in a different order.  You may also take them simultaneously or one at a time, so long as each course is completed within its specified session dates.

Announced at the PASS Summit is the next program, called the Microsoft Professional Program for Big Data.  The Big Data program is still in development, and subject to change.  It will be launched in 2017.  Here is the tentative course list:


This is a great set of courses for those of you looking to architect and build big data solutions.  Note you can concentrate on learning Microsoft technologies or take the open source path.

The next program after Big Data is expected to be for Front End Web Development.  There are also many other free courses from Microsoft that you can take at edX (see list).

Posted in Big Data, SQLServerPedia Syndication | Comments Off on PASS Summit Announcements: Microsoft Professional Program for Big Data

Azure SQL Server 2016 VM

With Windows Server 2016 just been released, now is the perfect time to build an Azure VM with SQL Server 2016 on Windows Server 2016.  In a matter of minutes you can be playing and learning both platforms.  Below I will document the steps I took to build the VM along with the additional software I installed.  This is a fully-loaded VM that I use for demo’s and to build small projects:

(Software updates as of 11/4/2016)

  1. Go to the Azure Portal, choose “New”, type in “SQL Server 2016”, and choose “SQL Server 2016 RTM Enterprise on Windows Server 2016”.  This will install SQL Server 2016 CU2 (13.0.2164.0)
  2. Follow the prompts to enter the info needed to build the VM.  I kept the default Azure “Resource Manager” (ARM) deployment model.  I chose the “East US” region, picked “Standard DS3” for the virtual machine size, created a resource group called “SQLServer”, used an existing storage account I called “serrastoragessd” which is premium-LRS and located in the East US region, enabled R Services, and created one data disk (under “Storage configuration” in SQL Server settings – create more disks for faster performance – please read Storage configuration for SQL Server VMs)
  3. After about 5 minutes your new VM will be ready.  I then connect to the VM and check for windows updates and install them
  4. On the Azure Portal, click on the VM and under “Support + Troubleshooting” you will see “Boot diagnostics”.  This will show you the boot screen of the VM so you can see if it is still performing windows updates
  5. I then login to SSMS and for the server properties change the server authentication to “SQL Server and Windows Authentication mode”.  I then create a SQL login with sysadmin server role
  6. Install the latest CU if needed: see SQL Server 2016 build numbers
  7. Get the latest SSMS version if needed: see Download SQL Server Management Studio (SSMS).  Latest is 16.5 (13.0.16000.28)
  8. Install Visual Studio Enterprise 2015 with Update 3 (14.0.25424.00 Update 3)
  9. Install SQL Server Data Tools (SSDT) 2015 GA for VS 2015 (14.0.61021.0)
  10. Install Office Professional Plus 2016 (make sure to choose 64-bit version)
  11. Install Visio Professional 2016 (make sure to choose 64-bit version)
  12. Install Chrome (Version 53.0.2785.143)
  13. Install Adobe Reader (Version 2015.020.20039)
  14. Install Java 8 (Update 111)
  15. Install Azure Data Lake Tools for Visual Studio (Version 2.2.21)
  16. Install Power BI Desktop (October update)
  17. Install Microsoft Data Migration Assistant (Version 2.0)
  18. Install Microsoft Database Experimentation Assistant Technical Preview (Version 1.0)
  19. Install Azure Storage Explorer (Version 0.8.5).  I use a Shared Access Signature to connect to my Azure storage
  20. Install Roboform (Version 7.9.22)
  21. Install DocumentDB Studio (Version 0.71)
  22. Install DocumentDB Data Migration Tool (Version 1.7)
  23. Install Data Warehouse Migration Utility Preview (Version 1)
  24. Install Azure SDK for .NET VS 2015 (Version 2.9.5)
  25. Install Microsoft Data Management Gateway (Version 2.4.6137.1)
  26. Install Red Gate Azure Explorer (Version
  27. Install Chrome Postman (Version 4.8.1)
  28. Install Fiddler (Version
  29. Install Narratives for Power BI
  30. Install ZoomIt (Version 4.5)
  31. Install Microsoft R Open (Version 3.3.1).  Installs RGui
  32. Install RStudio (Version 1.0.44)

  33. Install R Tools for Visual Studio 2015 (RTVS) (Version 0.5)
  34. Download and restore the Wide-World-Importers sample databases (Version 1.0)
  35. Download and restore the AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3
  36. Download and restore the Northwind database
  37. Download and restore samples databases from SQLskills
  38. Use Site Recovery to backup your VM daily
  39. When not is use, manually stop your VM, or use an Azure Marketplace solution or a graphical runbook (both use Azure Automation) to save money.  Note you will still be charged for storage if you created a data disk (see Azure Storage Pricing)
Posted in Azure, SQL Server 2016, SQLServerPedia Syndication | 3 Comments

PASS Summit Announcements: SQL DW free trial

Microsoft usually has some interesting announcements at the PASS Summit, and this year was no exception.  I’m writing a set of blogs covering the major announcements.  Next up is the free trial of Azure SQL Data Warehouse (SQL DW).

Azure SQL Data Warehouse is an enterprise-class, massively parallel processing (MPP) distributed database capable of processing petabyte volumes of both relational and non-relational data.  It is the industry’s first cloud data warehouse capable of grow, shrink, and pause-in-seconds capabilities with proven SQL functionality and if you’ve not yet tested it out you can now; for free.

You can use this one month free trial to do POCs and try out SQL DW up to 200 DWU and 2TB of data.  You must sign up by December 31st 2016.  Please note that once the one month free trial is over, you will start getting billed at general availability pricing rates.  For more information on the free trial, and to sign up, go here.

This is a great promotion as without it, you can get a free $200 credit for Azure, but you will quickly hit that limit when use SQL DW.

For an excellent 5-part overview of SQL DW, check out Azure SQL Data Warehouse

More info:

One Month Free Trial for Azure Data Warehouse

Posted in Azure SQL DW, SQLServerPedia Syndication | 1 Comment

PASS Summit Announcements: Power BI reports on-prem in SSRS

Microsoft usually has some interesting announcements at the PASS Summit, and this year was no exception.  I’m writing a set of blogs covering the major announcements.  Next up is the technical Preview for Power BI reports on-prem in SSRS.

The Technical Preview is a pre-configured Virtual Machine in the Azure Marketplace that includes everything you need to get started, even sample reports and data.  With this update, you can visually explore data and create an interactive report using Power BI Desktop, and then publish that report to an on-premises report server (SQL Server Reporting Services).  You can then share the report with your coworkers so they can view and interact with it in their web browsers.

Check out the Official announcement.  Get it now in the Azure Marketplace.  An excellent step-by-step tutorial is at Technical Preview of Power BI reports in SQL Server Reporting Services now available and Create Power BI reports in the SQL Server Reporting Services Technical Preview.  Post questions in the Reporting Services forum.  For users who would prefer to run this technical preview on an on-premises server, you can provision a virtual machine and then download the image as a .vhd file and use Hyper-V functionality to do so (see How to run the Technical Preview of Power BI Reports in SQL Server Reporting Services on-prem using Hyper-V).


Previously you would use Power BI Desktop to build reports and you would publish them to the Power BI Service in the cloud.  This is a solution for those that do not want to publish their reports to the cloud.

This preview supports Power BI reports that connect “live” to Analysis Services models – both Tabular and Multidimensional (cubes).  Additional data sources will be added in a future preview.  There is a new feature in this version: the ability to add comments to reports.  Make sure to check out Ten things you might have missed in the Technical Preview of Power BI Reports in SQL Server Reporting Services.


Microsoft plans to release the production-ready version in the next SQL Server release wave.  They won’t be releasing it in a Service Pack, Cumulative Update, or other form of update for SSRS 2016.  The Technical Preview is effectively a pre-release of SSRS vNext.

More info:

First thoughts on Power BI on premises

Power BI Reports in SSRS Techinical Preview

Power BI reports in SQL Server Reporting Services: Feedback on the Technical Preview

Posted in Power BI, SQLServerPedia Syndication, SSRS | Comments Off on PASS Summit Announcements: Power BI reports on-prem in SSRS

PASS Summit Announcements: Azure Analysis Services

Microsoft usually has some interesting announcements at the PASS Summit, and this year was no exception.  I’m writing a set of blogs covering the major announcements.  Perhaps the biggest one is the introduction of the Azure Analysis Services Public Preview (OLAP).

This is a PaaS for SQL Server Analysis Services (SSAS).  So it’s PaaS SSAS 🙂  Read the official announcement.

It is based on the analytics engine in SSAS,  For those not familiar with SSAS, it is an OLAP engine and BI modeling platform that enables developers and BI professionals to create BI Semantic Models that can power highly interactive and rich analytical experiences in BI tools (such as Power BI and Excel) and custom applications.  It allows for much faster query and reporting processing compared to going directly against a database or data warehouse.  It also creates a semantic model over the raw data to make it much easier for business users to explore the data.

Some of the main points:

  • Developers can create a server in seconds, choosing from the Developer (D1) or Standard (S1, S2, S4) service tiers.  Each tier comes with fixed capacity in terms of query processing units and model cache.  The developer tier (D1) supports up to 3GB model cache and the largest tier (S4) supports up to 100GB
  • The Standard tiers offer dedicated capacity for predictable performance and are recommended for production workloads.  The Developer tier is recommended for proof-of-concept, development, and test workloads
  • Administrators can pause and resume the server at any time.  No charges are incurred when the server is paused.  On the roadmap is to offer administrators the ability to scale up and down a server between the Standard tiers (not available currently)
  • Developers can use Azure Active Directory to manage user identity and role based security for their models
  • The service is currently available in the South-Central US and West Europe regions.  More regions will be added during the preview

Similarities with SSAS:

  • Developers can use SQL Server Data Tools (SSDT) in Visual Studio for creating models and deploying them to the service.  Administrators can manage the models using SQL Server Management Studio (SSMS) and investigate issues using SQL Server Profiler
  • Business users can consume the models in any major BI tool.  Supported Microsoft tools include Power BI, Excel, and SQL Server Reporting Services.  Other MDX compliant BI tools can also be used, after downloading and installing the latest drivers
  • The service currently supports tabular models (compatibility level 1200 only).  Support for multidimensional models will be considered for a future release, based on customer demand
  • Models can consume data from a variety of sources in Azure (e.g. Azure SQL Database, Azure SQL Data Warehouse) and on-premises (e.g. SQL Server, Oracle, Teradata).  Access to on-premises sources is made available through the on-premises data gateway
  • Models can be cached in a highly optimized in-memory engine to provide fast responses to interactive BI tools.  Alternatively, models can query the source directly using DirectQuery, thereby leveraging the performance and scalability of the underlying database or big data engine

Check out the pricing, the documentation, tutorial videos, and the top-rated feature requests.

Get started with the Azure Analysis Services preview by simply provisioning a resource in the Azure Portal or using Azure Resource Manager templates, and using that server name in your Visual Studio project.


More info:

Learn more about Azure Analysis Services

First Thoughts On Azure Analysis Services

Creating your first data model in Azure Analysis Services

Why a Semantic Layer Like Azure Analysis Services is Relevant (Part 1)

Posted in SQLServerPedia Syndication, SSAS | 3 Comments