Press "Enter" to skip to content

Category: Cloud

Saving An ADF Pipeline As A Template

Published 2019-02-14 by Kevin Feasel

Rayis Imayev shares with us how you can save an Azure Data Factory pipeline as a template:

Azure Data Factory (ADF) provides you with a framework for creating data transformation solutions in the Microsoft cloud environment. Recently introduced Template Gallery for ADF pipelines can speed up this development process and provide you with helpful information to create additional activity tasks in your pipelines.

We naturally long to seek if something standard can be further adjusted. That custom design is like ordering a regular pizza and then hitting the “customize” button in order to add a few toppings of our choice. It would be very impressive then to save this customized “creation” for future ordering. And Azure Data Factory has a similar option to save your custom data transformation solutions (pipelines) as templates and reuse them later.

Click through to see how you can do just that.

Comments closed

Improving The Azure Automated AG Experience

Published 2019-02-12 by Kevin Feasel

Allan Hirt would like to see a few improvements to the experience when creating availability groups on Azure VMs:

What does this mean? To have a supported WSFC-based configuration (doesn’t matter what you are running on it – could be something non-SQL Server), you need to pass validation. xFailOverCluster does not allow this to be run. You can create the WSFC, you just can’t validate it. The point from a support view is that the WSFC has to be vetted before you create it. Could you run it after? Sure, but you still have no proof you had a valid configuration to start with which is what matters. This is a crucial step for all AGs and FCIs, especially since AGs do not check this whereas the installation process for FCIs does.

If you look at MSFT_xCluster, you’ll see what I am saying is true. It builds the WSFC without a whiff of Test-Cluster. To be fair, this can be done in non-Azure environments, too, but Microsoft givs you warnings not to do that for good reason. I understand why Microsoft did it this way. There is currently no tool, parser, or cmdlet to examine the output of Test-Cluster results. This goes back to why building WSFCs is *very* hard to automate.

I’m not sure how easy some of these fixes would be, but they’d definitely be nice.

Comments closed

One More Data Gateway Is All You Need

Published 2019-02-11 by Kevin Feasel

Meagan Longoria explains when you might need data gateways when implementing an Azure BI architecture:

Let’s start with what services may require you to use a data gateway.

You will need a data gateway when you are using Power BI, Azure Analysis Services, PowerApps, Microsoft Flow, Azure Logic Apps, Azure Data Factory, or Azure ML with a data source/destination that is in a private network that isn’t connected to your Azure subscription with a VPN gateway. Note that a private network includes on-premises data sources and Azure Virtual Machines as well as Azure SQL Databases and Azure SQL Data Warehouses that require use of VNet service endpoints rather than public endpoints.  

There are a few of them so check out Meagan’s post and take notes.

Comments closed

Azure VM Boot Diagnostics

Published 2019-02-11 by Kevin Feasel

John Morehouse shows us how to enable and use boot diagnostics on Azure VMs to troubleshoot why that server isn’t coming up the way you’d expect:

The next blade will show you an active console of the virtual machine.  From here you are able to determine what the current status of the virtual machine might be.  You will also noticed that you can gain access to the serial log (shown below), which will give you more detailed information about the boot process.
Once we click on Boot Diagnostics, we will then see the initial startup screens of the server:

This is useful if you have some huge misconfiguration and the server’s failing for some reason.

Comments closed

Power BI Workspace V2

Published 2019-02-07 by Kevin Feasel

Reza Rad shows us the differences between Power BI Workspace V1 and V2:

Workspace version 2 has been available in Power BI Service for more than 6 months now. The new version introduced in August 2018, however, still many people don’t know what it is, and what is the difference of that with the old version, and the most important question: Should you create the new workspace in V2 or V1? Should you click on the Try Now button when you create the new workspace or not? I have previously written about workspaces and their important role in creating a collaborative environment. In this post, I’ll answer all questions above to help you make the right decision when creating the workspace. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.

I’ll admit I was unaware of V2 workspaces. This was interesting reading.

Comments closed

Integrating Azure Data Factory With GitHub

Published 2019-02-07 by Kevin Feasel

Rayis Imayev shows us how to tie Azure Data Factory pipelines with GitHub, allowing automatic check-in based on ADF pipeline changes:

Working with Azure Data Factory (ADF) enables me to build and monitor my Extract Transform Load (ETL) workflows in Azure. My ADF pipelines is a cloud version of previously used ETL projects in SQL Server SSIS.

And prior to this point, all my sample ADF pipelines were developed in so-called “Live Data Factory Mode” using my personal workspace, i.e. all changes had to be published in order to be saved. This hasn’t been the best practice from my side, and I needed to start using a source control tool to preserve and version my development code.

Click through for a detailed demo.

Comments closed

Automated Query Capture With Logic Apps

Published 2019-02-06 by Kevin Feasel

Arun Sirpal shows how we can use Azure Logic Apps to automate periodic capture of running queries in Azure SQL Database:

Have you ever wanted to capture the T-SQL, waits, sessions IDs (etc) at a specific time for Azure SQL Database? Sure there are a few ways to do this. Extended Events comes to mind but I wanted to do something different.

For this blog post I decided to use Brent Ozar’s famous sp_BlitzWho command (in expert mode) coupled with Azure Logic Apps. At a high level it is simple. At a specific time trigger the execution of sp_BlitzWho stored procedure and query it for later use.

Click through to see how to set this up.

Comments closed

Query Store On Azure SQL DW

Published 2019-02-04 by Kevin Feasel

Matt Usher announces Query Store is now available to all in Azure SQL Data Warehouse:

Since our preview announcement, hundreds of customers have been enabling Query Store to provide insight on query performance. We’re excited to share the general availability of Query Store worldwide for Azure SQL Data Warehouse.

Query Store automatically captures a history of queries, plans, and runtime statistics and retains them for your review when monitoring your data warehouse. Query Store separates data by time windows so you can see database usage patterns and understand when plan changes happen.

Given its power in the on-prem product, I’m glad that Azure SQL Data Warehouse is getting Query Store as well.

Comments closed

Where To Store SQL Server DB Files In Azure

Published 2019-02-04 by Kevin Feasel

James Serra gives us a few options for storing database files (that is, your MDF, NDF, and LDF files) when running SQL Server in an Azure VM:

If using managed disks, the recommendation is to attach several premium SSDs to a VM.  From Sizes for Windows virtual machines in Azure you can see for the size DS5_v2 you can add 64 of 4TB data disks (P50) yielding the potential total volume size of up to 256TB per VM, and if you use the preview sizes (P80) your application can have up to around 2PB of storage per VM (64 of 32TB disks).  With premium storage disks, your applications can achieve 80,000 I/O operations per second (IOPS) per VM, and a disk throughput of up to 2,000 megabytes per second (MB/s) per VM.  Note the more disks you add the more storage you get and the more IOPS you get, but only up to a certain point due to VM limits, meaning at some point more disks get you more storage but not more IOPS.

But there are a few other options too, so check them out.

Comments closed

Apps To Manage SQL Server On Azure VMs

Published 2019-02-04 by Kevin Feasel

Kevin Chant has a list of tools you can use to manage SQL Server on Azure VMs:

From experience I know it’s important to know what applications you can use locally with Azure to manage SQL Server solutions. So you have the right tools for the job.

For instance, I was talking with some people at a client’s site the other day about deciding what application to use to future proof themselves.

In this post I will cover applications for use with Windows, MacOS and Linux distributions. 

I don’t think I’m spoiling too much in saying that about 80% of these are the same tools you would use for on-prem work.

Comments closed