Azure SQL Database SKU Recommendation Utility

According to the September Azure Newsletter, the SKU recommendation utility for Azure SQL Database is in preview. It’s great to see the Azure SQL team finally built their own version of the DTU Calculator that I built a few years ago. The demand for such a utility seemed obvious when I pitched the idea to the SQL team back in 2014. 🙂

I had an opportunity to review the new SKU recommendation utility so I thought I’d share some of my first impressions. I’ll try not to be overly critical but but the new utility is missing some critical features when compared to the DTU Calculator, IMHO.

SQL Server 2016 or Later

The SKU Recommendation Utility only supports SQL Server 2016 or later. I have to admit, this limitation doesn’t make sense to me. I expect that most developers thinking about migrating their databases wouldn’t be running SQL Server 2016 or later. Those developers are likely part of the thousands with SQL Server 2008 or SQL Server 2012, now faced with upgrade or migrate decisions because of SQL support end-of-life.

Command Line Interface (CLI) Functionality

The SKU Recommendation Utility is limited to CLI only. Again, I don’t get it. In order to get the Recommendation Utility, you have to download the Database Migration Assistant which has it’s own GUI. Seems like a missed opportunity to have both the collection and results included as part of an already existing UI.

Collecting and Reviewing

Collecting and reviewing SKU recommendation results is a two-step process. Admittedly, this is how the DTU Calculator works, but this was the SQL team’s chance to one-up me and integrate both steps into the GUI so the whole process feels seamless.

SKU Recommendation Results

Once you’ve gone through the process of collecting your counters, you’ll need to feed the CSV file into an executable to get the results. The executable will write another file with a table of the recommendation results. While I admit, I like some of the information in the file, I was disappointed at the level of granularity, lack of analytics information, and visual interface.

Regional Pricing

I know I’ve been critical up to this point so I’ll shift gears a bit. The ability to include regional pricing as part of the recommendation is a very cool feature. Unfortunately, I’m not sure how it works or where it shows up in the results. The only references I see to pricing are in the ExclusionReasons and AppliedRules columns but the pricing information in those columns is pretty generic. I was hoping to see an actual price estimate. Maybe I’m missing something.

SQL Managed Instance

Another cool feature of the SKU Recommendation Utility is that it can recommend either Azure SQL Database or Azure SQL Managed Instance as the optimal deployment target. This is actually a very useful feature. Especially, when it’s coupled with the information in the ExclusionReasons and AppliedRules columns; providing a good level of information as to why SQL MI is a better option than SQL DB or visa versa.

Summary

Hopefully, I didn’t turn you off completely to the SKU Recommendation Utility. I’m happy to see the Azure SQL team is working to address what I feel are the first questions developers ask when thinking about migrating their databases to Azure. Specifically, “which service tier and performance level should I use and how many database throughput units (DTUs) am I using now?” and “how much will running my databases in Azure cost me?”

Unfortunately, the SKU Recommendation Utility feels very limited and missing what I feel are some of the most compelling features of the DTU Calculator. That being said, I still feel the DTU Calculator is a better option for answering the questions above. On that note, I’m pleased to announce I’m working on the next version of the DTU Calculator! Here’s a quick summary of some of the key features I’m adding to what I’m calling the Azure SQL Calculator. I’ll release a separate blog post with more details later.

  • Support for analyzing performance on multiple SQL Servers simultaneously
    Support for analyzing performance on individual databases, not just at a server level
  • Support for Azure SQL DB, Elastic Pools, and SQL Managed Instance as part of the recommendation
  • Support for regional pricing as part of the results
  • Support for recommendation of optimal deployment targets for best performance and least cost
Advertisements

Artificial Intelligence (AI) Primer

Last week I had the opportunity to present an Artificial Intelligence primer to a group of senior leaders at Microsoft.  I spent several days putting this content together so I figured I’d share. 🙂

What is AI?

Definition

Artificial intelligence (AI) is generally considered the next big technological shift so it’s an incredibly hot topic with a lot of people talking about it.  Depending on who you talk to, you may hear varying definitions, but Microsoft generally defines AI as an information system, inspired by biological systems, designed to give computers the human-like abilities of hearing, seeing, reasoning, and learning.

Despite all the buzz, AI is probably one of the least understood technologies.  That’s not surprising since AI isn’t something you can see or touch.  When AI is implemented well, you may not even realize you are using it.  In fact, most of us are probably relying on technology that uses AI without even knowing it.  If you’ve recently used Cortana, Bing, or built a PowerPoint deck, you’ve probably used AI.

AI is not one universal technology.  It’s an umbrella term that usually refers to multiple technologies like machine learning, deep learning, computer vision, natural language processing (NLP), and many others.  These technologies can be used individually or in combination to add intelligence to applications and do things that are like what people can do.  For example, we can use AI to:

  • Perceive images and sounds, and recognize them (classification)
  • Reason over large amounts of data to identify patterns (clustering)
  • Help decision-making by making predictions and understanding the relationship between various people, places, or things (regression)

Since AI is an umbrella term, people will often use AI and machine learning interchangeably.  Though AI and ML are related, they aren’t the same so it’s important to understand the relationship.  Machine learning is considered a subcategory of AI; focused on analyzing data to train models that make predictions.  AI leverages ML algorithms to give systems a sense of intelligence.

Influences

AI is not a new field; much of its theory and technology has been developed over the last 70 years.  However, AI has recently moved into the mainstream due to these factors:

  • Massive computing power of the cloud
  • Availability of enormous datasets that can be used to teach AI systems
  • Breakthroughs in developing AI algorithms and improving AI methods such as deep learning

Microsoft has advantages in every one of these areas and that’s helping us more quickly infuse AI into many of our core products and services.  These advantages include the immense computing power of Azure, access to comprehensive data spanning services like Bing, Office, and LinkedIn, and the AI breakthroughs coming out of our worldwide network of research labs.  Microsoft also provides a variety of tools for developers and data scientists to build AI-infused applications.

Market Size and Competition

AI is delivering real-life benefits, and many consider it ready to transform software and services.  As a result, interest is high.  Tractica predicts that revenue generated from the direct and indirect application of AI software will grow from $1.4 billion in 2016 to nearly $60 billion by 2025.  A Cowen and Company financial analyst report found that 81% of IT leaders are currently investing in or planning to invest in AI.  Many of whom are looking to use AI to improve their existing products and services.

Also contributing to the growth, the number of AI startups and investments has exploded over the past five years, reaching $15 billion.  According to Accenture, the total number of AI startups has increased 20-fold since 2011.  In 2016, over 550 AI startups raised $5 billion in funding and CB Insights reports that over 200 private AI companies have been acquired since 2012.

Tech giants like Amazon, Apple, Facebook, Google, IBM, and others already use AI technology as part of their stack.  They are also aggressively competing for position; poaching talent, setting up research labs, and buying startups.  McKinsey estimates the tech giants spent $20-$30 billion globally on AI in 2016, 90% of which went to R&D and 10% to acquisitions.  All these factors are creating a sense of urgency, as we all attempt to best each other with AI-infused products and services.  Given the similarities in products, services, and other offerings, Microsoft’s primary competitors in the AI market are Amazon, Google, IBM, and Facebook.

Microsoft’s Approach to AI

Microsoft has a unique approach to AI that is based on three pillars:

  • Leading innovation that extends capabilities
  • Building powerful platforms that make innovation faster and more accessible
  • Developing a trusted approach so that AI is developed and deployed in a responsible manner

1. Leading innovation

Microsoft is leading innovation with breakthroughs in fields like image recognition, speech recognition, reading comprehension, and many others.  We are using advances in computer vision and image recognition for products such as Seeing AI – an app that helps people who are blind or have low vision do things like recognize currency and get a description of people around them.

Our speech recognition algorithms can recognize the words in a conversation with an error rate of just 5.1%, which is about as well as a person.

We have similar results in machine reading comprehension, which uses AI to read, answer, and even ask questions.  This year, we created technology that used AI to read a document and answer questions about as well as a human.

In addition to developing products and services that people can use at work and at home, we’re committed to creating tools that use AI to address societal challenges. We call this effort, AI for Good.

2. Building powerful platforms

To build powerful platforms and make Microsoft AI accessible to everyone, we have created APIs and other tools that developers, customers, and data scientists can use to add intelligence into existing products and services, or to build new ones.

More than 760,000 developers from 60 countries are using Cognitive Services to build apps that do things like recognize gestures, convert speech into text, or identify, caption and moderate images.

In addition, more than 240,000 developers have signed up to use Azure Bot Service to create bots that can interact naturally with customers on websites and in apps.

All these tools run on Microsoft Azure, which now spans 36 regions around the world and provides the backbone for many customers who are developing and running AI-infused products and services.

In addition, we make the Microsoft Cognitive Toolkit — which is used across Microsoft to achieve breakthroughs in artificial intelligence using deep learning – freely available to the public via an open-source license.

3. Developing a trusted approach

Though we are in the early stages of understanding what AI systems will be capable of, developing a trusted approach is important.  For now, AI systems are very good at doing certain tasks, like recognizing photos or words, but AI still a long way from being able to do any close to what we see in our favorite science fiction books and movies.

As AI systems get more sophisticated and start to play a larger role in people’s lives, it’s imperative for companies to develop and adopt clear principles that guide the people building, using, and applying AI systems.

Among other things, these principles should ensure that AI systems are fair, reliable and safe, private, and secure, inclusive, transparent, and accountable.  To help achieve this, the people designing AI systems should reflect the diversity of the world in which we live.

At Microsoft, we’ve developed an internal advisory committee to help ensure our products adhere to these principles.

Making AI Real

So far, I’ve shared an overview of AI and how Microsoft is leveraging it to enhance the products and services we offer.  Now I’d like to talk about how we utilize all our products, tools, services, and readiness materials to help our partners.

We lead by showing how our partners can innovate faster with Microsoft’s flexible AI platform and tools; utilizing Azure’s comprehensive set of flexible AI services, enterprise-grade AI infrastructure that runs AI workloads anywhere at scale, and our modern AI tools designed for developers and data scientists to help create AI solutions easily, with maximum productivity.

For technical audiences, we start by learning as much as possible about our partners’ solutions.  For partners that are new to AI, we show them how, with Cognitive Services and Microsoft Bot Framework, they can infuse vision, speech, language, knowledge, search, and/or conversational AI into their solutions; doing so in hours – not days or weeks.

While Cognitive Services and Bot Framework are a great way to get new-to-AI partners started, some partners may require a solution that’s a bit more tailor-made.  Cognitive Services also offers customization for several of their services.   For partners who need to train and customize a service, without knowing much about various AI and ML algorithms, these custom services are a great option.

In some cases, partners will need solutions that are beyond the scope of our pre-built services.  For those cases, Microsoft provides platforms and tools that support all major Data Science languages (Python, R, Node, Java, C++, C#, F#, SQL and more).  Equally important, our platforms support many of the popular non-Microsoft deep-learning frameworks like Google’s TensorFlow and Facebook’s Caffe2 as well as Open-Source and commercial software platforms.

It’s also important to note, we have the Azure AI Gallery:  Which contains ready to implement AI solution templates created by our growing community of developers and data scientists.

Call to Action

Hopefully, it’s clear that AI represents a huge opportunity for Microsoft as well as our partners.  Since Microsoft has been a leader in the AI space for a long time, there’s a lot of content and resources available.  Whether you’re new to AI or an AI veteran, there are resources to help you come up to speed or keep your skills sharp.

Educate yourself on what’s possible so you can have AI conversations with your partners.  I’d recommend starting with the Microsoft AI Platform whitepaper, the AI Landscape blog post, and the Ethics and Law in Data and Analytics MOOC sites to be very useful.

For technical audiences, take the time to up-skill or stay sharp by leveraging our learning paths, AI School, and the Machine Learning and AI programs on EdX.

For business audiences, check out the AI Playbook released by Melissa Mulholland’s team in January.  I’d also recommend reading and Gartner’s 2018 MQ for Data Science and Machine Learning.

As you can see, there’s a lot of information available and trust me, there’s a lot more so if you have questions, feel free to reach out.

To close, I’d like to reference this quote from Harry Shum.  I believe it perfectly represents the opportunity we have with AI.

AI will transform every business, improve every life and solve some of society’s most fundamental challenges.

Harry Shum, Microsoft EVP AI & Research

Great Doc!

Just about 4 weeks ago, I changed roles at Microsoft and joined the Technical Content team.  I have to admit when I first learned of the opportunity, I was a little skeptical.  I’m not much of a writer and I certainly didn’t want to become one.  However, once I started learning more about the team and the opportunity, it was clearly the right fit.

Very few teams at Microsoft have the opportunity to directly impact how our customers and developers engage our products and services.  Even fewer teams have the level of executive investment all the way up to Scott Guthrie; leading to change the way we think about technical content.  It’s no longer about writers writing docs based on features in our products and services.  Now it’s about content developers creating samples, tutorials, hands-on-labs, as well as writing docs that give our customers and developers the information they need to build solutions quickly and effectively.  The focus is customers and developers – we want to understand what they need and provide it to them in the way they want it.

My team is responsible for developer focused technical content.  That means we develop content for:  Azure App Services, .NET/Core, ASP.NET/Core, Java, Node, PHP, Python, Ruby, Go, Visual Studio, VS Code, Visual C++, VSTS, and a handful of other tools and services – so, not much.

My hope is to use this blog to keep those who care informed regarding Microsoft’s investment in technical content as a way to help our developers and customers.  From time to time, I’ll share some of our vision and roadmap as well as the cool updates we’re making to our technical content.  In that spirit, here’s a few of the updates we’ve made in the last few weeks:

The .NET team shipped the .NET Glossary which is a huge step to providing users that are new to .NET a quick reference to understand frequently used terms and acronyms within the documentation.  It was a big effort involving multiple groups to pull it off.  My understanding is Scott Hanselman has a blog coming.  That’ll be cool to see.

Last week, we released two new dev centers focused on .NET and Node.  Our goal was to get folks up in minutes on Azure and have a left to right view for the community.  We also wanted to have a homebase for advocates and the communities we are connected to via advocacy.  More recently, we made similar updates to the Java Developer Center; focusing customers on our quickstarts, tutorials, and samples.  These are definitely big steps forward for OSS on Azure.

I’d love to hear feedback as I continue to share.  Let me know what you think about what we’re doing.

Introducing the DTU Calculator Command Line Utility

Several weeks ago I added support for a command line utility (CLU) to help collect performance metrics from SQL Server, prior to using the Azure SQL Database DTU Calculator, as an alternative to the PowerShell script. I apologize for not adding a blog post sooner. If you are unfamiliar with the Azure SQL Database DTU Calculator, I encourage you to start with the first post in the series.

I added the CLU for a couple of reasons. The first being a result of the sheer volume of questions I received regarding the PowerShell script not working for some reason or another; the second reason being that I have a suspicion many of the people using the DTU Calculator would feel comfortable with a tool written in C#.

When I created the PowerShell script, I tried to keep it as simple as possible and I figured that any engineer could open it up and modify as needed. In hindsight, I should have updated the script to include more validation and error handling but I felt that adding too much script would do more harm than good. I may test that theory by adding validation and error handling in the future – feel free to help me out, update it yourself, and send me the script – but for now I’ve created an alternative C# based utility.

The C# code is hosted on GitHub so you can review/modify as needed. If you’ve reviewed the code, you can see it’s pretty simple so I won’t spend much time discussing. However, there are a couple of points worth mentioning:

  • The utility collects CPU and IOPS exactly the same as the PowerShell script. However, the “Database – Log Bytes Flushed” counter is optional. The utility uses that counter if it exists, and proceeds without it if it doesn’t.*
  • The app.config contains all the settings for the utility. You can change the counter category, instance, and/or name; the collection interval and duration; as well as the  CSV file location. If you need more than that, you can download the source code and modify as needed.

*Note:  Since the PowerShell script and command line utility measure CPU and IOPS at the server level and Log Bytes Flushed is measured at the database level, I decided to make the database counter optional.

As always, if you have questions and/or feedback, don’t hesitate to reach out.

Migrating from AWS RDS SQL Server to Azure SQL Database Using the Azure SQL Database DTU Calculator

Last week a colleague of mine in the UK asked me for some help migrating from AWS’ RDS SQL Server to Azure SQL Database.  The details of why you’d want to do this are out of the scope of this post but needless to say, Azure SQL Database is better.  This post assumes you’re familiar with the DTU Calculator and how it works.  If not, click here to learn more.

AWS RDS provides managed services for deploying SQL Server as well as several other major DBMS platforms.  The service is single-tenant in that the RDS server is just a dedicated VM that AWS has wrapped to make it look like a platform as a service (PaaS) offering.  Unfortunately, AWS doesn’t provide access to the underlying VM so capturing the performance metrics needed by the DTU Calculator is a little bit tricky and the PowerShell script I developed to help doesn’t work.  My first thought was to capture performance metrics using SQL Server’s Dynamic Management Views (DMOs) and provide a command-line interface wrapper to generate a CSV file the DTU Calculator understands.  This is where I ran into my first problem.  Does anyone understand how to use those things?  Seriously?!?  I spent hours searching the internets trying to find something, anything, that would help.  Finally, I came across the following book by Louis Davidson and Tim Ford, Performance Tuning with SQL Server Dynamic Management Views.  After reading through a few chapters related to the performance counters I needed, I determined I would need to write the following SQL statements:

SELECT
       counter_name,
       A.cntr_value,
       B.cntr_value,
       Ratio = CASE WHEN B.cntr_value = 0 THEN 0
       ELSE A.cntr_value / B.cntr_value END
FROM sys.dm_os_performance_counters A,
       (SELECT cntr_value
       FROM sys.dm_os_performance_counters
       WHERE OBJECT_NAME LIKE 'SQLServer:Resource Pool Stats%'
             AND COUNTER_NAME LIKE 'CPU usage % base%') B
WHERE COUNTER_NAME LIKE 'CPU Usage %'
AND OBJECT_NAME LIKE 'SQLServer:Resource Pool Stats%'
AND COUNTER_NAME NOT LIKE 'CPU usage % base%'
AND COUNTER_NAME NOT LIKE 'CPU usage target%'

SELECT *, 'PERF_COUNTER_LARGE_RAWCOUNT' FROM sys.dm_os_performance_counters
WHERE COUNTER_NAME LIKE 'Disk Read Bytes/sec%'

SELECT *, 'PERF_COUNTER_LARGE_RAWCOUNT' FROM sys.dm_os_performance_counters
WHERE COUNTER_NAME LIKE 'Disk Write Bytes/sec%'

SELECT *, 'PERF_COUNTER_BULK_COUNT' FROM sys.dm_os_performance_counters
WHERE COUNTER_NAME LIKE 'Log Bytes Flushed/sec%'
AND INSTANCE_NAME LIKE '<Database>'

Reading the queries, it’s easy to see what’s going on.  First, I capture the CPU as a percentage.  Second, I get reads/writes as raw values.  Finally, I get the log which happens to be a bulk count so I’ll need to store and compare its value on each interval to find the real value.  Running the queries against a local database gave me the results I wanted but I was skeptical AWS RDS would do the same so  I fired up an RDS SQL Server instance with a database and ran the queries in SSMS.  Just as I suspected, no luck.  It seems AWS RDS will give me CPU information but I’m S.O.L. for the  other counters.  The queries run fine but the last three don’t return any values.

I spent several more hours investigating alternatives and settled on AWS CloudWatch.  AWS does provide RDS performance metrics via CloudWatch but they are aggregation metrics.  The smallest increment is 60 seconds so I’ll need to take the average of each counter for each interval.  CloudWatch provides multiple counters for RDS but the following seem to be related to what the DTU Calculator needs:  CPUUtilization, ReadThroughput, WriteThroughput.  Unfortunately, there doesn’t appear to be a way to capture the equivalent of Log Bytes Flushed/sec so I’ll need to ignore that counter and hope the others get me close enough in my calculation.

I settled on using the GetMetricStatistics action.  I won’t go into detail regarding that action and I’m definitely not an AWS CloudWatch expert so you’ll want to do some research of your own to validate my findings.  I’ve done some testing using the CloudWatch CLI and I believe something similar to the following commands will work:

mon-get-stats CPUUtilization --start-time 2015-11-15T22:00:00.000Z --end-time 2015-11-15T23:00:00.000Z --period 60 --statistics "Average" --namespace "AWS/RDS" --dimensions "DBInstanceIdentifier=[dbinstanceidentifier]"

mon-get-stats ReadThroughput --start-time 2015-11-15T22:00:00.000Z --end-time 2015-11-15T23:00:00.000Z --period 60 --statistics "Average" --namespace "AWS/RDS" --dimensions "DBInstanceIdentifier=[dbinstanceidentifier]"

mon-get-stats WriteThroughput --start-time 2015-11-15T22:00:00.000Z --end-time 2015-11-15T23:00:00.000Z --period 60 --statistics "Average" --namespace "AWS/RDS" --dimensions "DBInstanceIdentifier=[dbinstanceidentifier]"

Take the output from those three commands and copy the results into a CSV file.  Use the following headers:  ‘% Processor Time’, ‘Disk Reads/sec’, ‘Disk Writes/sec’, ‘Log Bytes Flushed/sec.’  Since you don’t have metrics for the ‘Log Bytes Flushed/sec’ column, fill that column with zeros.  Once you’ve built the CSV file, you’re all set to upload the results into the DTU Calculator.  If your RDS SQL Server is running more than a single database, select the ‘Elastic Databases’ option to calculate the equivalent pool size.

As I mentioned above, calculating the Azure SQL Database equivalent for an AWS RDS SQL Server is a bit tricky and by no means perfect.  However, my hope is this post gives you some indication of how to gather performance metrics from RDS using AWS CloudWatch and hopefully provides a baseline calculation for migrating to Azure SQL Database.  As always, I’d love to hear your feedback.

Analyzing the Azure SQL Database DTU Calculator Results

The following is the third in a series of posts about the Azure SQL Database DTU Calculator.  The first post in this series described the need for the calculator and the process of creating it.  The second post described using the calculator.  This post will focus on analyzing the results.

Single Azure SQL Database

Assuming you’ve captured your SQL Server’s performance and uploaded it in the calculator, you will now see the results page.  The example below is for a single database so your results may vary slightly if you’re using Elastic Database, but in general, the results page is divided into three basic sections:  ‘Service Tier and Performance Level’, ‘DTUs Over Time’, and ‘View More Details’.  Note that the ‘View More Details’ section is collapsed by default so you’ll need to expand it in order to analyze CPU, IOPS, and Log recommendations individually.

Service Tier/Performance Level

The ‘Service Tier/Performance Level’ section provides an overall recommendation for migrating to Azure SQL Database.  This recommendation is calculated by taking the maximum number of DTUs for either CPU, IOPS, or Log at each interval and using that maximum as the basis for the recommendation.  Since the calculator recommends the Service Tier/Performance Level which maximizes performance while minimizing costs, it’s important to note the recommendation may not cover 100% of your workload.  There may be some portion of your workload that falls into a higher Service Tier and Performance Level so you’ll want to validate your database’s performance post migration.

Note:  You can gain additional insights by hovering your mouse over each section of the chart to see what percentage of your workload is covered by a specific Service Tier/Performance Level as well as how much that Service Tier/Performance Level covers the entire workload.

Service Tier/Performance Level

DTUs Over Time

The ‘DTUs Over Time’ section is similar to the ‘Service Tier/Performance Level’ recommendation in that it uses the maximum DTU for CPU, IOPS, and Log.  The difference is the ‘DTUs Over Time’ chart displays the DTU value at each interval.  This type of visualization is useful since it shows how DTUs relate to performance.  In general, you would hope your performance is somewhat consistent but having this chart allows you to identify performance spikes that may cause problems with your Azure SQL Database deployment and do some additional investigation to identify and resolve those performance issues before migrating to Azure SQL Database.

DTUs Over Time

View More Details

The ‘View More Details’ section is broken down into four subsections.  The first three sections show individual recommendation for CPU, IOPS, and Log.  Having individual recommendation allows you to quickly identify how your workload is bound.  For example, the following shows recommendations for CPU and IOPS.  Notice the recommendation for CPU is Standard – S1 and the recommendation for IOPS is Standard – S0.  This means your workload is CPU bound as your workload requires more CPU than disk.

Service Tier/Performance Level for CPU 

Service Tier/Performance Level for IOPS

The last chart shows how much of your workload’s CPU, IOPS, and Log is covered by a Service Tier/Performance Level.  By utilizing this chart you can visualize how your workload changes across Service Tiers and Performance Levels.  For example, the following chart shows that 30% of CPU, 78% of IOPS, and 100% of Log are immediately covered by the Basic Service Tier and it isn’t until Standard – S1 that all three metrics have over 90% coverage.

Service Tier/Performance Level for CPU, IOPS, and Log

Elastic Databases

The Elastic Databases recommendation is almost identical to the recommendation for a single database with two major differences.  The first difference is that elastic database pools don’t have a concept of Performance Levels so the recommendation only provides a Service Tier (Basic, Standard, or Premium).  The second difference is the Elastic Databases recommendation shows the total eDTUs required for the pool.  The total eDTUs required for the pool is important because of the way Elastic Databases are billed.  You pay for the total eDTUs required for all databases in the pool.  See the SQL Database Pricing page for more information.

Introducing the Azure SQL Database DTU Calculator: Part 2

The following is the second in a series of posts about the Azure SQL Database DTU Calculator.  In this post, I’ll cover some recent updates I’ve made as well as an overview of using the calculator for either a single database or a pool of databases.

Running a single SQL Server database in Azure has been supported for a few years but the concept of a Database Throughput Units (DTU) is relatively new.  The Azure SQL team first introduced the concept after some customers complained about performance when using Azure SQL Web and Business editions while other customers raved about performance.  You can guess which neighbors were the noisy ones.  In order to give all Azure customers a more predictable level of performance, the Azure SQL team introduced a new set of Service Tiers and Performance levels.  I won’t go into details regarding all the features of Service Tiers and Performance Levels but it is worth mentioning that each tier and level has an associated cost.  If you want more performance, you pay more.  If you want less…  you get the point.

Introducing tiers and levels to provide more reliable performance was a great first step and works really well for a single database which requires consistent performance.  However, many customers have more than a single database and in many cases, the performance of those databases isn’t consistent.  There are periods of high intensity followed by periods of low intensity.  Initially, developers would need to create a separate Azure SQL Database and try to manage the peaks and lulls of each database separately.  However, this type of database management was complicated and not cost effective when when working with several databases or more.

At Build 2015, Microsoft announced the preview of Azure SQL Elastic Databases to support unpredictable database demands and reduce the complexity of managing multiple databases.  The on-demand performance scaling of individual databases within an elastic database pool is possible because each database within a pool uses eDTUs from a shared set associated with the pool.  Allowing databases that require more performance to consume more DTUs while databases requiring less to consume less.

Regardless of running a single database or a pool of databases, developers migrating to Azure still need to know how their databases will perform after they migrate.  The good news is that SQL Azure allows you to scale your database up or down depending on your database needs.  The bad news is all of that happens post migration so it’s difficult to get a sense of cost without knowing the Service Tier and Performance Level required for your database workload.  That’s where the DTU Calculator can help.

Single Database or Multiple Databases

The instructions for using the DTU calculator are straightforward so I’ll try to focus on some of the things I don’t mention on the main page of the calculator.  First, you’ll need to decide if you’re going to run the calculator for a single database or multiple databases.  Running the calculator for a single database means your SQL Server only has one database and that database is what you plan to migrate to Azure.  If your SQL Server has more than one database but you still only plan to migrate one of those databases, you’ll either need to modify the PowerShell script I provide on the calculator’s main page, or understand that the calculator will likely overestimate your workload because the script is configured to capture totals for CPU, IOPs, and Log on the server– not by individual database.

If you SQL Server has multiple databases and you plan to migrate all of those databases to Azure, then you can run the PowerShell script as provided.  The calculator also supports multiple databases on multiple servers.  However, you will need to run the script on each server and then upload all the results in the calculator.

Resource Utilization

As part of the calculator, I’ve provided a PowerShell script to help capture the correct performance counters for the correct period of time.  By default, the script captures performance for the server at one second intervals for the duration of an hour.  However, you do not need to use the script as provided or at all.  Feel free to modify the script as needed or roll your own.  The only requirement for uploading results to the calculator is a CSV file with headers:

  • Processor – % Processor Time
  • Logical Disk – Disk Reads/sec
  • Logical Disk – Disk Writes/sec
  • Database – Log Bytes Write/sec

Note:  Make sure you run the PowerShell script as administrator.  If not, you may see errors about missing counters.

Upload Results

Once you have the performance CSV file(s), you are ready to calculate the results.  First, enter the number of cores for your SQL Server.  The calculator uses the number of cores to normalize the results against the servers running in Azure.  After specifying the number of cores, click the browse button to upload your CSV file.  If you are running the calculator for multiple databases, use the add and remove icons to add/remove multiple files to the table.  Once you have entered the number of cores for each server and uploaded all of your files, click the calculate button.

Review Analysis

After clicking the calculate button, you’ll see several charts which provide an analysis of your database resource consumption. The charts depict the percentage of time (based on your measurements) that your database’s resource consumption fits within the limits of each Service Tier and Performance Level.  You can review CPU, Iops, and Log individually as well as collectively to better understand which metrics affect the performance of your database.

Conclusion

In this post, I’ve given a little more background regarding database throughput units, Azure SQL Database, and Elastic Databases.  I’ve also  provided some instructions for using the DTU Calculator for either a single database or a pool of databases.  In my next post, I’ll cover analyzing the results.