When we are beginning to think of migrating our on-premises databases to Azure SQL, we have to decide on a proper purchase model, a service tier, and a performance level. Before starting the Azure SQL migration process, we have to find logical and provable answers to the following questions:
- Which purchase model is suitable for my apps and business requirements?
- How much budget do I need?
- Which performance level meets my requirements?
- Can I achieve the acceptable performance of my apps?
The purchase model and service tiers will certainly affect the Azure bills. On the other hand, we have to achieve the maximum performance with the selected service tier.
It’s a good article with helpful tips for people thinking of moving to Azure SQL Database.
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.
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.
Fortunately, the error message is clear and helpful, two terms which rarely go in conjunction with “error message.”
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.
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.
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.
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.
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.
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.
Database master key.
Database scoped credential.
Also, fantastic obfuscation in the post. Hello, Shane!
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.