Category: Cloud

The first and most drastic change that occurred over the last year is the number of players that Gartner decided to highlight in its report: the number of vendors went from 14 to just 6 this year.

Why is that?! Have the big become bigger and the small smaller?! Or has the space shrunk? The latter is highly improbable. All the contrary: earlier last year, forecasted that the highest growth in the cloud market would be coming from the sector this MQ covers: Gartner predicted that the cloud system infrastructure services would grow over 36% to reach $34B+ in 2017.

So, what gives?!

Read on to learn what gives. As far as the rankings themselves go, I think it’s reasonable: AWS and Azure can generally go head-to-head on features though Amazon does have the advantage. Google is a distant third and the rest aren’t major players.

Comments closed

Don’t Forget Those Paused Indexes

Published 2018-05-23 by Kevin Feasel

Arun Sirpal tries to create a new index on his Azure SQL Database:

I was creating some demo non-clustered indexes in one of my Azure SQL Databases and received the following warning when I executed this code:
CREATE NONCLUSTERED INDEX [dbo.NCI_Time]
ON [dbo].[Audit] ([UserId])
INCLUDE ([DefID],[ShopID])
Msg 10637, Level 16, State 3, Line 7

Cannot perform this operation on ‘object’ with ID 1093578934 as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

How intriguing!

Fortunately, the error message is clear and helpful, two terms which rarely go in conjunction with “error message.”

Comments closed

Bulk Loading Into Cosmos DB

Published 2018-05-22 by Kevin Feasel

Ben Jarvis has a performance test for the new Cosmos DB Bulk Executor Library:

One of the many exciting announcements made at MSBuild recently was the release of the new Cosmos DB Bulk Executor library that offers massive performance improvements when loading large amounts of data to Cosmos DB (see https://docs.microsoft.com/en-us/azure/cosmos-db/bulk-executor-overview for details on how it works). A project I’ve worked on involved copying large amounts of data to Cosmos DB using ADF and we observed that the current Cosmos DB connector doesn’t always make full use of the provisioned RU/s so I am keen to see what the new library can offer and look to see if our clients can take advantage of these improvements.

In this post I will be doing a comparison between the performance of the Cosmos DB connector in ADF V1, ADF V2 and an app written in C# using the Bulk Executor library. As mentioned in the Microsoft announcement, the new library has already been integrated into a new version of the Cosmos DB connector for ADF V2 so the tests using ADF V2 are also using the Bulk Executor library.

There are some significant performance improvements from using this bulk loader, as Ben shows.

Comments closed

Using AU Analyzer To Lower Data Lake Analytics Costs

Published 2018-05-16 by Kevin Feasel

Matthew Hicks shows off the Data Lake Analytics AU Analyzer:

The AU Analyzer looks at all the vertices (or nodes) in your job, analyzes how long they ran and their dependencies, then models how long the job might run if a certain number of vertices could run at the same time. Each vertex may have to wait for input or for its spot in line to run. The AU Analyzer isn’t 100% accurate, but it provides general guidance to help you choose the right number of AUs for your job.

You’ll notice that there are diminishing returns when assigning more AUs, mainly because of input dependencies and the running times of the vertices themselves. So, a job with 10,000 total vertices likely won’t be able to use 10,000 AUs at once, since some will have to wait for input or for dependent vertices to complete.

In the graph below, here’s what the modeler might produce, when considering the different options. Notice that when the job is assigned 1427 AUs, assigning more won’t reduce the running time. 1427 is the “peak” number of AUs that can be assigned.

I like this kind of tooling, as it provides a realistic assessment of tradeoffs.

Comments closed

Creating Azure SQL Database Managed Instances Via ARM Templates

Published 2018-05-16 by Kevin Feasel

Jovan Popovic shows how us how to build a Managed Instance of Azure SQL Database using Powershell and an ARM template:

Values that you need to change in this request are:

name – name of your Azure SQL Managed Instance (don’t include domain).
properties/administratorLogin – SQL login that will be used to connect to the instance.
properties/subnetId – Azure identifier of the subnet where Azure SQL Managed Instance should be placed. Make sure that you properlyconfigure network for Azure SQL Managed Instance
location – one of the valid location for Azure data centers, for example: “westcentralus”
sku/name: GP_Gen4 or GP_Gen5
properties/vCores: Number of cores that should be assigned to your instance. Values can be 8, 16, or 24 if you select GP_Gen4 sku name, or 8, 16, 24, 32, or 40 if you select GP_Gen5.
properties/storageSizeInGB: Maximum storage space for your instance. It should be multiple of 32GB.
properties/licenceType: Choose BasePrice if you don’t have SQL Server on-premises licence that you want to use, or LicenceIncluded if you can have discount for your on-premises licence.
tags(optional) – optionally put some key:value pairs that you would use to categorize instance.

Click through for the template and a quick Powershell script which shows how to use the template.

Comments closed

Useful Azure SQL Database T-SQL Statements

Published 2018-05-15 by Kevin Feasel

Arun Sirpal has a few T-SQL statements to help manage an Azure SQL Database database:

Creating a database

Very easy to create something like the below – a S2 database.

CREATE DATABASE [MeeTwoDB]
( MAXSIZE = 5GB, EDITION = 'standard', SERVICE_OBJECTIVE = 'S2' ) ;  

--checking details
SELECT Edition = DATABASEPROPERTYEX('MeeTwoDB', 'Edition'),
       ServiceObjective = DATABASEPROPERTYEX('MeeTwoDB', 'ServiceObjective')

Read on for several more examples.

Comments closed

Open Source ML With Azure

Published 2018-05-09 by Kevin Feasel

David Smith shares his Build conference slides:

The topic for my talk at the Microsoft Build conference yesterday was “Migrating Existing Open Source Machine Learning to Azure”. The idea behind the talk was to show how you can take the open-source tools and workflows you already use for machine learning and data science, and easily transition them to the Azure cloud to take advantage of its capacity and scale. The theme for the talk was “no surprises”, and other than the Azure-specific elements I tried to stick to standard OSS tools rather than Microsoft-specific things, to make the process as familiar as possible.

Click through for the slides and additional resources.

Comments closed

Writing Extended Events To Blob Storage

Published 2018-05-09 by Kevin Feasel

Arun Sirpal shows how to write an Azure SQL Database extended events session to blob storage:

Last year I wrote about Azure SQL Database extended events (https://blobeater.blog/2017/02/06/using-extended-events-in-azure/) and gave an example where I was capturing deadlocks via the ring buffer. Ever since then I wanted to do a follow-up post but using Azure storage as the target for my XEL files.

This is more complicated than using the ring buffer as the target and requires a couple of things:

Azure storage account where you create a dedicated container for the files.
SAS key.
Database master key.
Database scoped credential.

Also, fantastic obfuscation in the post. Hello, Shane!

Comments closed

Determining Compute Resources From Managed Instances

Published 2018-05-08 by Kevin Feasel

Dimitri Furman explains Azure SQL Database Managed Instance resource allocations:

In the current Azure SQL Database Managed Instance (MI) preview, when customers create a new instance, they can allocate a certain number of CPU vCores and a certain amount of disk storage space for the instance. However, there is no explicit configuration option for the amount of memory allocated to the instance, because on MI, memory allocation is proportional to the number of vCores used.

How can a customer determine the actual amount of memory their MI instance can use, in GB? The answer is less obvious than it may seem. Using the traditional SQL Server methods will not provide the right answer on MI. In this article, we will go over the technical details of CPU and memory allocation on MI, and describe the correct way to answer this question.

The information and behavior described in this article are as of the time of writing (April 2018). Some aspects of MI behavior, including the visibility of certain compute resource allocations, may be temporary and will likely change as MI progresses from the current preview to general availability and beyond. Nevertheless, customers using MI in preview will find that this article answers some of the common questions about MI resource allocation.

What you see in SQL Server Management Studio is true, but it is also not the whole picture.

Comments closed

Reading AWS Aurora Error Logs With Powershell

Published 2018-05-08 by Kevin Feasel

Michael Bourgon has a Powershell script which reads error logs from AWS Aurora:

Been working on monitoring. For some reason, when you tell Aurora to send errorlogs to Cloudwatch, all it sends are the Audit Logs, which will tell you that code had changed, etc, but doesn’t (!?!?!??!!!) actually put your logs in Cloudwatch. I don’t understand it, so I built this process to look through logs and return the data. The next step would be to format it and either upload to Cloudwatch manually, or log it, or send email.

Click through for the script.

Comments closed