Azure Transactional Replication

John Sterrett names transactional replication into Azure as his favorite feature:

In the field, I see a lot of people using Availability Groups to have a near real-time replica for reporting.  I talked a little bit about this above.  What isn’t mentioned here is you have to maintain a Windows Failover Cluster, Quorum, Active Directory (Unless using Windows 2016 Preview) and more. This gets you a replica that is just a copy of the database. What does this mean? You cannot change database objects like security, indexes, etc. Also, what if you don’t need the whole database(s) for reporting? If not, you can replicate only the data you truly need.

So, let’s recap here.  You only have to replicate the data that you need.  You can have different security and indexes on your reporting subscriber database(s).  The reporting subscriber database can be scaled up or down as needed based on your needs.  The reporting database can now be an Azure Database. Folks, I call this a huge win!

There’s a lot more replication love out there than I’d expect.  John promises to follow up with a guide on how to implement this, so keep an eye out for that.

Is Power BI SSAS In The Cloud?

Koos van Strien hits us with an interesting thought about SSAS versus Power BI:

As I’m currently planning to migrate the entire BI architecture of one of my customers to the cloud, this made me think: can we ditch SSAS as we know it already in favor of Power BI? What are the alternatives?

To study that, I’ve put some diagrams together to show the possibilities of moving BI to the cloud. First, I’ll discuss the possible architectures, then the impossible architecture (but maybe the situation I was looking for).

One man’s opinion:  there will be SSAS for Azure.  I have no proof of this, and the nice part about having no proof is that I can throw out wild speculation without fear of violating NDA….  But to me, Power BI solves a different problem and acts in conjunction with SSAS rather than as its replacement.  I also don’t see any technical reasons why SSAS couldn’t live in the cloud, and so that leads me to believe that it will be there eventually.  But hey, it turns out wild speculation is occasionally wrong…

Extended Events In Azure SQL Database

Julie Koesmarno walks through Extended Events in Azure SQL Database (currently in preview):

Extended Event (XEvent) feature is available as public preview in Azure SQL Database as announcedhere. XEvent supports 3 types of targets – File Target (writes to Azure Blob Storage), Ring Buffer and Event Counter. Once we’ve created an event session, how do we inspect the event session target properties? This blog post describes how to do this in 2 ways: using the User Interface in SSMS and using T-SQL.

It’s nice to see Extended Events making their way into Azure SQL Database.

Linear Regression In Azure ML

Ginger Grant gives a brief discussion of linear regression:

There are two types of indicators for linear correlation, positive and negative as shown on the following charts. The Y axis represents Grades, and the x axis is changed to show positive and negative correlation of the amount of X on grades. When X is the amount of study hours, there is a positive correlation and the line goes up. When X is changed to watching cat videos, there is a negative correlation. If you can’t draw a line around the points there is no correlation. If I were to create a graph where X indicated the quantity of the bags of Cheese Doodles consumed on grades, it would not be possible to draw a straight line, where the data points cluster around it. Since this is Line-ar regression, if that line doesn’t exist there is no correlation. Knowing there is no correlation is also useful.

Simple linear regression is a powerful tool and gets you to “good enough” more frequently than you’d think.

SQL Server Marketplace Images

Kevin Feasel



Melissa Coates debates whether to use a SQL Server marketplace image for an Azure VM:

As more and more customers are interested in moving some portion of their BI/analytics workloads to cloud services, one question that comes up occasionally is whether or not you should start with a marketplace image that has SQL Server already installed. So far I’ve noted a few key considerations for this decision:

  1. Do you want to pay for the SQL Server license as part of the VM pricing?

  2. Do you want to configure SQL Server in a specific way (i.e., following best practices)?

  3. Do you want Azure to handle things like automated patching by default?

My rule of thumb is if it’s Express Edition or just for me to mess around with, I’m typically happy with an image.  But for a production setup, I’d want the fine-grained control at installation time that you just won’t get.

Jupyter Notebooks With R

Kevin Feasel


Cloud, R

Andrie de Vries notes that Azure Machine Learning now supports Jupyter Notebooks with R:

I wrote about Jupyter Notebooks in September 2015 (Using R with Jupyter Notebooks), where I noted some of the great benefits of using notebooks:

  • Jupyter is an easy to use and convenient way of mixing code and text in the same document.

  • Unlike other reporting systems like RMarkdown and LaTex, Jupyter notebooks are interactive – you can run the code snippets directly in the document

  • This makes it easy to share and publish code samples as well as reports.

Jupyter Notebooks is a fine application, but up until now, you could only integrate it with Azure Machine Learning if you were writing Python code.  This move is a big step forward for Azure ML.

Against The Cloud

Kevin Feasel



SQLWayne is against “the cloud:”

First off, the word itself.  The Cloud.  What is The Cloud?  It’s a server that you don’t own.  You can’t touch it, it’s in someone else’s data center.  It may or may not be virtual.  Amazon’s Cloud or Microsoft’s or Google’s are several data centers with racks and racks of servers.  They are physical, just not at your location.  And they’re accessed across the Internet.  This is something that we’ve been doing for 30 years, it’s called a Wide-Area Network, just scaled up bigger.  We had bi-coastal WANs before the World Wide Web came along.

Four or five years ago, I was absolutely in agreement.  Today, I’m 50/50, being near 100% for many types of servers (web servers, etc.) and closer to 25-30% for databases.  My expectation is that those numbers will continue to shift upward as time goes on, but there will always be reasons not to migrate certain servers to someone else’s data center.

Clearing The Azure Procedure Cache

Tim Radney shows us a new way of clearing the procedure cache in Azure SQL Databases (and in 2016 RC0 or later):

It turns out that DBCC FREEPROCCACHE is not supported in Azure SQL Database. This was troubling to me, what if I’m in production and have some bad plans and want to clear the procedure cache like I can with the box version. A little Google/Bing research lead me to find the Microsoft article, “Understanding the Procedure Cache on SQL Azure,” which states:

SQL Azure currently doesn’t support DBCC FREEPROCCACHE (Transact-SQL), so you cannot manually remove an execution plan from the cache.  However, if you make changes to the table or view referenced by the query (ALTER TABLE and ALTER VIEW) the plan will be removed from the cache.

In discussing this with Kimberly Tripp after not seeing that described behavior, it does not flush the plan from cache, but it does invalidate the plan (and then the plan will be eventually aged out of the cache). While this is helpful in certain situations, this was not what I needed. For my demo I wanted to reset the counters in sys.dm_exec_cached_plans. Generating a new plan would not give me the desired results. I reached out to my team and Glenn Berry told me to try the following script:

Read on for the new command, and just like DBCC FREEPROCCACHE, be careful where you point that thing.

Pausing Azure SQL Data Warehouse

Kevin Feasel



Brian Davis shows us how to pause Azure SQL Data Warehouse:

This is where automation comes to the rescue again! Most of our SQLDWs can be paused after 6:00 PM on weekdays, as well as the entire weekend. Now, I could manually go and pause each individual SQLDW at the end of the day, but what happens if I have plans for dinner or something else during that time? I decided that I needed an automated process to check each SQLDW and pause it if it is running. Using Azure Automation andAzure Runbooks, I was able to create a scheduled task that looks for any running SQLDW and pauses it.

Here are the basic steps to implement the automated solution I came up with:

  1. Create a credential in your automation account with access to all SQL Data Warehouses.

  2. Create a PowerShell Workflow Runbook with the code below.

  3. Create and link the schedule(s) needed for it to run.

Azure gripe #4 for me is that they’re so inconsistent about what I can do not to pay money.  Apparently you can pause Azure SQL Data Warehouse, which is good.  But DocumentDB or HDInsight?  Nope, deletion is the only way to stop running up charges.  Check out Brian’s script if you use Azure SQL Data Warehouse and save your company a bit of cash.

Setting Up Azure SQL Database With Powershell

Mike Fal creates and does basic configuration of an Azure SQL Database instance using Powershell:

From a logical standpoint, working with Azure SQL databases is not very different from setting up a SQL Server instance and database in your environment. There are 3 main components we will need to deploy:

  • A server

  • A database

  • A firewall (server or database)

This is the second part in his series.


June 2017
« May