For a SQL server DBA – How to start Azure Learning – Part 5 (SQL Azure Database Monitoring)

In continuation to this series, I will write about how we can monitor SQL Azure DB. Before I get into the monitoring of SQL Azure DB, I just wanted to discuss on the major work which DBA does on day to day basis:

1. Database Management e.g. creating/adding/removing files
2. Backup/Restore
3. Database Security
4. HADR
5. Performance Tuning and Optimization
6. Performance Monitoring
7. Database maintenance e.g. Rebuild Index/Update statistics etc.

Let’s see how many activities will reduce if the database is moved to SQL Azure DB.

1. Database Management – we don’t need to add/remove any files. Once the database is moved to Azure we don’t have this option. Database size will be dependent on the performance tier selected.
2. Backup/Restore – It will be managed by Microsoft itself so, this task will reduce. You just need to use the GUI for the restore and get your work done.
3HADR – You just need to configure HADR options as discussed in my post and rest will be managed by Microsoft.

For rest of the activities , the method will change slightly – though logically things will remain the same. If you understand, It’s still SQL server underneath.

4. Database maintenance – The activities like Rebuilding Indexes/update statistics will still need to be managed. These activities will still need to be scheduled regularly. Just remember, there are some restrictions with the T-log consumption. I will come up with a separate post on the limitations which we hit in Azure DB due to some resource constraints.

5. Database Security – It will still need to be managed, though there is a change with the way it used to be managed in on-premise environment.

6. Performance Tuning and Optimization – We will no longer be able to manage SQL configuration using sp_configure or hardware or database files etc. Generally these areas used to be looked at before getting into query tuning. DTUs will become the prime focus when it will come to throughput of IO/CPU etc. Query tuning will remain the same, we still need to look for Index/table scans or implicit conversions or joins etc. in the execution plans.

7. Performance Monitoring – Finally we are on today’s topic of discussion, If we talk about what do we monitor today for on-premise SQL server:

1. Memory
2. CPU
3. IO
4. Network
5. SQL Access Methods
6. SQL Memory Clerks
7. SQL Blocking/Deadlocks
8. SQL Transactions
9. DB Size/Growth
10. SQL Configuration changes
11. SQL Connections
12. SQL Wait Statistics
13. Connection failures/Attentions

We generally use various tools like Perfmon, Management Data Warehouse , DMVs and activity monitor etc. to see these various resources.  But for Azure DBs, we will have a monitoring Dashboard and DMVs to serve the purpose. The monitoring dashboard is really detailed to help with most of the resources and for delving deeper we will still have the luxury of the DMVs as required. The DMVs related to the features we don’t have or are not required won’t be there.

Just login into https://manage.windowsazure.com and you will your database as follows:

image

Just click on the database name and you will get an option to select the dashboard, here you can see the database size:

image

If you want to use PowerShell to see the DB size , check this post.

Just above the database size, there is monitoring Dashboard but for detailed option, just click on Monitor option:

image

It’s testing database so, I couldn’t have much data there. If you want to add more counters, simple select “Add Metrics” and you will see:

image

Most of the counters are self-explanatory so, I will simply come to the point – something , which will be change , to look at is:

DTU percentage
Log IO Percentage
Storage
Data IO percentage

These four counters are really important to understand. I personally think, DTU is little tricky to articulate as a DB professional working on on-premise SQL Server. Instead of thinking about CPU/memory/storage , we just need to focus on the transactions / concurrent connections / Concurrent logins etc. As discussed in the previous posts, Microsoft guarantees to provide the below mentioned metrics in the performance tiers and you just need to move your from how much CPU/disk you need. Instead focus on how much transaction /connections will the application have etc. etc.

image

Once the application is deployed on a specific tier, we need to monitor how much percentage is being used. If it’s 80 – 90% utilized and we will either need to tune the queries using the same methods as we used to use for on-premise SQL server. Else, we will need to scale up to the next level. This can be scheduled for automatic scale up and scale down as well. For more information on resource limits check – https://azure.microsoft.com/en-us/documentation/articles/sql-database-resource-limits/

 

For information on DTU – please check this video:

https://channel9.msdn.com/Series/Windows-Azure-Storage-SQL-Database-Tutorials/Scott-Klein-Video-02/player

 

References – https://msdn.microsoft.com/en-us/library/azure/dn369873.aspx

Benchmark Overview – https://msdn.microsoft.com/en-us/library/azure/dn741327.aspx

Resource Limits  – https://azure.microsoft.com/en-us/documentation/articles/sql-database-resource-limits/

I will hold PaaS series for now and will move a little bit into IaaS as well. I will write my next post on storage for IaaS.

HTH!

Advertisements

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.