monitoring microsoft azure sql server vms and sql database

Grant Fritchey | www.ScaryDBA.comwww.ScaryDBA.com

Monitoring Microsoft Azure SQL Server VMs and SQL DatabaseGrant [email protected]

Grant Fritchey | www.ScaryDBA.com

Goals Understand the different parts of the

architecture that can be monitored Learn about the tools used for monitoring Identify the differences

between monitoring a physical box, a local VM and a hosted environment


Get in touch Grant Fritchey

scarydba.com

[email protected]

@gfritchey

Grant Fritchey | www.ScaryDBA.com 4

Monitoring as the Basis of Knowledge Monitoring is the ultimate example of

knowledge is power Is the server online and available Are there errors we need to know

about What does the performance look like Immediate performance information Historical performance information Ability to compare to a known state

https://www.flickr.com/photos/4thglryofgod/7763836962/in/photolist-7zkdBB-cQ4GeY-aec8fs-8LZTwi-afih5x-8FVLbN-8KvSqc-bVhD96-gtD2GW-8Kw2o4-akGYf9-8pxA7R-aKHQq6-dZrjLa-dbFQVB-8V7raV-9YPiQR-dAj4bg-8pxzUH-9kQsAV-8pALBf-dz4nZs-fH3MbA-fH3Mg9-fGLbLz-fH3Mtj-fGLbVi-fGLc2M-fGLcB4-fGLcrR-fH3KGA-fGLcfr-fH3KXJ-fH3KQs-fGLcnD-fGLcag-fH3L39-fH3MkE-fGLb8k/


Architectural Differences The hardware is effectively invisible Within Azure, you get what Microsoft gives

you Most tools are already known to you Automation is available It’s on the cloud

https://www.flickr.com/photos/trevorblake/2988073856/in/photolist-5m7G9k-5y3EfL-5Cy5rp-5Kch7X-5S1MTk-67Abuj-6jsdop-6ynko4-6yrx41-6yrFbG-6ywP9k-6F3dtR-6J5pWk-77Y5GJ-786YPZ-7f1hjz-7fpEAQ-7oFyxK-7oFDKM-7oKsEQ-7oKtHw-9YouDf-aAJ2Gn-9ZdbSQ-a1uMoP-bWyLhu-8YUU8w-a4WcY2-bpgEsS-bCbvQ2-8YUTnE-aGsLAa-8drnZj-aGsKU2-9ch3Tz-9HFfKf-f76DEL-kPUihR-aGsqTa-8uDzfg-dEi9ZH-dGhcgY-dGhbLh-dGbM8F-bdWwve-dZzYUK-eNtLJa-bySDFz-bahfBM-fhCNqB-fq3EmJ/


What Can Be Monitored Azure Microsoft Azure SQL Database (MASD) Azure SQL Server Virtual Machine Blob Storage


Azure: What Can You Monitor? Azure Service Azure applications Various Azure components As a SQL Server DBA» Beyond availability, not much here


Azure: Tools Windows Azure Service Dashboard Azure Management Portal Service level diagnostics data

(Azure Applications, outside our scope) Azure SDK System Center Azure Monitoring Pack 3rd Party

http://www.windowsazure.com/en-us/support/service-dashboard/


DEMOAzure Monitoring


MASD: What Can You Monitor Availability Connectivity Core query performance Deadlocks Errors Throttling But you can’t monitor» Extended query performance» Disk I/O» Detailed performance metrics»Most internals


MASD: Tools Azure Management Portal Windows Azure SQL Database Portal Dynamic Management Objects (DMOs)


DEMOSQL Database Monitoring


Azure VM: What Can You Monitor It’s Windows

» Availability» Internally, everything you

expect to see» Externally, no access to

underlying hardware It’s SQL Server» Internally, everything you expect to see» Relationship to Windows, exactly what you expect» Externally, no access to underlying hardware

It’s a VM» Internal behaviors still related to standard templates» I/O can be observed» No other external access


Azure SQL Server VM: Tools Standard tool set» Performance Monitor» Extended Events» Trace Events» Dynamic Management Objects» Performance Monitor Counters

Additional Tools» AzureTools» Event Viewer» Logs» Azure SDK» 3rd Party

http://msdn.microsoft.com/en-us/library/gg433048.aspx


DEMOVM Monitoring


Blob Storage: What Can You Monitor Ingress/Egress Availability Latency Capacity Others


Blob Storage: Tools Azure Management Portal Azure SDK


DEMOStorage Monitoring


General Recommendations Retry Logic Plan for outage

» Not because Azure is bad, but because it’s the internet

Build in logging and tracing» And make that configurable» Instrument your code

Treat T-SQL the same as ever» Although MASD data collection is limited

Focus on VM internals» Surrender control over the external system

Except for I/O» That you can monitor» To speed I/O, look to put static content

on a nearby Content Delivery Network (CDN) Take regular baselines

https://www.flickr.com/photos/19783530@N00/1780363950/in/photolist-9TwERk-j7Ehge-5HZYck-5icFwp-dAMohY-hphvAu-4nZv-3HjQbb-hEmAXH-78dkf4-4uVjmu-7fx3e-6pKQfE-62jZBq-8TND8t-2uzExP-7pYEBc-aRz2Rc-4TYfBb-4sW94b-9KSDpW-cWDhBy-ePVkcB-2USDXC-8LqP3z-fJaCBE-hA8UBS-jfrPgQ-7PG8BJ-gQqEJU-9Wp4t4-6KKx1V-3dLdbV-7Z2aLp-ffELd1-8bw1Eb-7gmUnA-7kNr7m-aserDa-axvEJ-dt9JQp-4ZrXra-4qaZRc-fjADAc-2GHXT-8dsEHQ-f8ZCV-M61zm-9hA6md-5RwBCi/


More Recommendations Right-size the CPU» You pay for what you use, but

dedicating CPU to you is the same thing as using them.

Enable Read/Write cache» On by default» High I/O switch to none» Latency sensitive and

low I/O switch to read Create an Availability Group for your VMs

https://www.flickr.com/photos/19783530@N00/1780363950/in/photolist-9TwERk-j7Ehge-5HZYck-5icFwp-dAMohY-hphvAu-4nZv-3HjQbb-hEmAXH-78dkf4-4uVjmu-7fx3e-6pKQfE-62jZBq-8TND8t-2uzExP-7pYEBc-aRz2Rc-4TYfBb-4sW94b-9KSDpW-cWDhBy-ePVkcB-2USDXC-8LqP3z-fJaCBE-hA8UBS-jfrPgQ-7PG8BJ-gQqEJU-9Wp4t4-6KKx1V-3dLdbV-7Z2aLp-ffELd1-8bw1Eb-7gmUnA-7kNr7m-aserDa-axvEJ-dt9JQp-4ZrXra-4qaZRc-fjADAc-2GHXT-8dsEHQ-f8ZCV-M61zm-9hA6md-5RwBCi/


Most Important Recommendations Don’t trust anything I’ve told you about

recommendations Don’t trust anything

Microsoft tells you about recommendations

Verify everything through testing

https://www.flickr.com/photos/25315751@N04/6264575957/in/photolist-9oD82R-8zaPYM-axzAbi-aiJFoM-mmoXhy-6cni59-82DwEc-bHffBx-2USDXC-6LBNST-8cPApG-eWCGe4-5igUUj-KbLG7-2HqbGY-82xGx4-N9fNS-8p5nBi-6ZNynA-6kLk2i-6KqWZS-6qjq34-9ezvjP-dsoTCg-5wCstP-6ZR9Cn-5F9vZC-3HpKt-9XKEKB-8ppQbs-2Hyc9L-7YuRss-Pa1xE-32MNfD-32MNda-bEMNch-au3YK2-dsoUWk-Dx6zF-ak66uy-j44RdW-anJPxJ-jXdc7e-j3YjWJ-caasku-VfYYb-eQ1ue3-6tUnXy-3qK7jK-g6d32N/


References Monitoring Windows Azure SQL Database Usin

g Dynamic Management Views SQL on Azure VM Troubleshooting in Windows Azure How to Monitor a Storage Account Windows Azure – Troubleshooting & Debuggin

g Azure SQL Server VM Disk Configuration Collect Logging Data by Using Windows Azure

Diagnostics Take Control of Logging and Tracing in Windo

ws Azure Troubleshooting and Monitoring for SQL Serve

r in Azure Virtual Machines Windows Azure SQL Database Performance an

d Elasticity Guide Performance Guidance for SQL Server in Wind

ows Azure Virtual Machines

http://msdn.microsoft.com/en-us/library/windowsazure/ff394114.aspx

http://msdn.microsoft.com/en-us/library/windowsazure/ff394114.aspx

http://curah.microsoft.com/20602/sql-on-azure-vm-troubleshooting-monitoring-and-tuning

http://www.windowsazure.com/en-us/documentation/articles/best-practices-troubleshooting/

http://www.windowsazure.com/en-us/documentation/articles/storage-monitor-storage-account/

http://blogs.msdn.com/b/kwill/

http://blogs.msdn.com/b/kwill/

http://instadba.com/azure-vm-sql-server-disk-configuration/

http://msdn.microsoft.com/en-us/library/windowsazure/gg433048.aspx

http://msdn.microsoft.com/en-us/library/windowsazure/gg433048.aspx

http://msdn.microsoft.com/en-us/magazine/ff714589.aspx

http://msdn.microsoft.com/en-us/magazine/ff714589.aspx

http://msdn.microsoft.com/library/azure/dn195883.aspx


http://social.technet.microsoft.com/wiki/contents/articles/3507.windows-azure-sql-database-performance-and-elasticity-guide.aspx

http://social.technet.microsoft.com/wiki/contents/articles/3507.windows-azure-sql-database-performance-and-elasticity-guide.aspx



https://www.flickr.com/photos/emkeller/5822050276/in/photolist-9LBYLT-9StwHb-6fVCkr-6fZP7m-2vnahk-89Lrm9-89Lsz3-89HcyM-63rUTh-2VYwZV-4bWxd8-mCpQC-5mdSGc-4QiRYi-dTiYAo-5FYXcE-7fJpnP-4bX798-4rCHR6-6usC6c-4LEx5v-9yiCru-53LYWK-4kBpip-5guM2d-4AYg6h-4rttdK-5eGgDL-5EhAiD-7kjL9w-7kgryn-7kkfoE-7kihWX-7kfT7H-7knw5E-7kkTFq-7kjzBm-7khvFc-7kfYEF-7kg5MB-7kfRaX-7kk3pb-7kkNMf-7kh2gp-7kh7Dp-7kkRtS-7kjGL3-7khAyR-7ki8kg-7kiEen/


Goals Understand the different parts of the

architecture that can be monitored Learn about the tools used for monitoring Identify the differences

between monitoring a physical box, a local VM and a hosted environment

monitoring microsoft azure sql server vms and sql database

Documents

cloud grant fritchey

external access grant

known state4 grant fritchey

gfritcheygrant fritchey

touch grant

invisiblewithin azure

azure apps

sql databasegrant