For a SQL server DBA – How to start Azure Learning – Part 1 (DTU)

As we all know, It’s a Cloud era! There are lots changes which are coming our way, which will directly influence are day to day work. People are still contemplating how this change will impact them and where to invest their time for technical growth. I have been talking about this with many DBAs and I feel somewhere they are confused on how to get into azure. Just to help people in deciding which direction to move in, I thought of writing something on this in the form of a series of post.

As a database professional, we have two major directions to focus:

1. Platform as a Service (PaaS)
2. Infrastructure as a Service (IaaS)

For PaaS , I may learn migration , HADR or monitoring ,very easily. As it’s all going to be based on similar concepts but the main point to understand is , when to move to PaaS. Similarly, we may learn how to deploy and setup SQL on VMs in Azure but when we should go for IaaS or when to choose PaaS over IaaS or vice-versa.

In this Azure learning series, I will share some of my thoughts on how to choose between these two. But before getting into those details, lets discuss on the changes which are really going to make us think differently. It will help to build some base and understand how to leverage cloud

For both PaaS and IaaS – I will talk about what are the things which have changed from our current on-premise infrastructure. In this post, we will start with PaaS.

Before you read forward, just be sure of what’s SQL on Azure VMs (IaaS) and SQL azure databases(PaaS). Please check this link : – https://azure.microsoft.com/en-us/documentation/articles/data-management-azure-sql-database-and-sql-server-IaaS/.

Resource Consumption (PaaS):

As we know , now the concepts are changing for the resource utilization. Earlier , we used to talk about CPU in GHz and memory in GBs etc. but it’s all going to change , now it will be all DTUs (Database Throughput Unit). In SQL Azure databases , we need to be cognizant of the various performance tiers we have e.g.

Service Tier/Performance Level DTU MAX DB Size Max Concurrent Requests Max Concurrent Logins Max Sessions Benchmark Transaction Rate Predictability
Basic 5 2 GB 30 30 300 16,600 transactions per hour Good
Standard/S0 10 250 GB 60 60 600 521 transactions per minute Better
Standard/S1 20 250 GB 90 90 900 934 transactions per minute Better
Standard/S2 50 250 GB 120 120 1,200 2,570 transactions per minute Better
Standard/S3 100 250 GB 200 200 2,400 5,100 transactions per minute Better
Premium/P1 125 500 GB 200 200 2,400 105 transactions per second Best
Premium/P2 250 500 GB 400 400 4,800 228 transactions per second Best
Premium/P3 1000 500 GB 1,600 1,600 19,200 735 transactions per second Best

It must be a very open ended question for us to decide on , which performance tier should be chosen or how many DTUs will suffice my application needs. For each tier, the cost is different therefore choosing the tier will be very critical to project budgets. You will always want to fit in the lower slab to save money which will mean tuning the application as much as possible.

I have seen , customers have not been interested in investing time to benchmark their applications.But now, it’s going to be really important.

To move into Azure, we should learn various patterns of the application:

1. How many transactions/second application has during the peak loads
2. How many max concurrent connections does it create
3. Performance during peak loads as well as during lean period – for switching between performance tiers

Currently, we are used to planning disks/tempdb/max server memory/trace flags etc. etc. but with Azure PaaS , it’s all going to be taken care for us automatically. We just need to understand how many DTUs do we need and which database to be moved to Azure. If you articulate it, it’s going to change our conversations and the way we plan about new applications/migrations. Isn’t it a big change? but surely a change for good.

We need to have more focus towards, how to benchmark an application and understand which performance tier to choose. If none of this fit your workload requirement, there are some ways like sharding and elastic DB which you need to explore to scale up even further.

Let explain with an example, today we don’t think much about the data type and it’s length before we create a table e.g. if date has to be stored, we don’t bother much to use datetime(8 bytes) datatype without knowing the number of bytes it’s going to consume. Had we chosen date(4 bytes) data type , we would have saved 5 extra bytes of storage. It holds true for other data types as well. When the number of rows grow high, the space saving is big. If the database size if cut down, there may be chances to move to lower performance tier which means saving cost for the company.

Given the limitations we have in cloud, there is a huge scope of optimization the resource consumption for our application to work efficiently and “economically”.  I will come up with a detailed post on benchmarking and DTUs but for now , it’s just to build some basics.

People, who are thinking on how these DTUs were calculated  – please refer : https://msdn.microsoft.com/en-us/library/azure/dn741327.aspx.

I will come up with Database backup options in Azure and how it changes our day to day work. I will write post on all the changes which I have observed in SQL Azure databases (PaaS) and then will move to changes in SQL on Azure VMs (IaaS).

HTH!

Disclaimer:
The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

2 thoughts on “For a SQL server DBA – How to start Azure Learning – Part 1 (DTU)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s