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

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