1 / 23

Monitoring Microsoft Azure SQL Server VMs and SQL Database

Dive deep into monitoring architecture, tools, and differences between physical, local VMs, and hosted environments. Learn about Azure tools and what can be monitored, including MASD, Azure VMs, and Blob Storage. Receive expert recommendations on monitoring strategies, tool sets, and performance metrics to optimize your Azure environment effectively.

dmcnair
Download Presentation

Monitoring Microsoft Azure SQL Server VMs and SQL Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Monitoring Microsoft Azure SQL Server VMs and SQL Database Grant Fritchey grant@scarydba.com

  2. 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

  3. Get in touch Grant Fritchey scarydba.com grant@scarydba.com @gfritchey

  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

  5. 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

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

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

  8. 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

  9. Azure Monitoring Demo

  10. 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

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

  12. SQL Database Monitoring Demo

  13. 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

  14. 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

  15. VM Monitoring Demo

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

  17. Blob Storage: Tools • Azure Management Portal • Azure SDK

  18. Storage Monitoring Demo

  19. 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

  20. 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

  21. 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

  22. References • Monitoring Windows Azure SQL Database Using Dynamic Management Views • SQL on Azure VM • Troubleshooting in Windows Azure • How to Monitor a Storage Account • Windows Azure – Troubleshooting & Debugging • Azure SQL Server VM Disk Configuration • Collect Logging Data by Using Windows Azure Diagnostics • Take Control of Logging and Tracing in Windows Azure • Troubleshooting and Monitoring for SQL Server in Azure Virtual Machines • Windows Azure SQL Database Performance and Elasticity Guide • Performance Guidance for SQL Server in Windows Azure Virtual Machines

  23. 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

More Related