Enterprise class SQL Server monitoring in distributed production environments with high number of servers, Roman Dimenko, Dell EMC, CEE-SECR 2017

For those IT professionals who have to deal with SQL Server administration, maintenance and monitoring in mission critical environments on daily basis. Either accidential DBAs or professional DBAs looking for free standardized monitoring solutions.

Transcript

1. October 2017, St. Petersburg Software Engineering Conference Russia October 2017, St. Petersburg Software Engineering Conference Russia Enterprise class SQL Server monitoring in distributed production environments with high number of servers Roman Dimenko, DELL EMC
2. About myself • DBA and DBD since early 2000`s • Managed distributed DB teams up to 20 people • Worked with DELL EMC, Microsoft, Spar, McDonald`s • High load OLTP databases, DWH • Industries: telecom, fintech, adtech, retail, cloud
3. Statement of the problem • Database monitoring in general
4. Statement of the problem • Database monitoring in general • Proactive and reactive
5. Statement of the problem • Database monitoring in general • Proactive and reactive • Cost
6. Statement of the problem • Database monitoring in general • Proactive and reactive • Cost • Skills
7. Statement of the problem • Database monitoring in general • Proactive and reactive • Cost • Skills • Scalability
8. Statement of the problem • Database monitoring in general • Proactive and reactive • Cost • Skills • Scalability • Levels of monitoring (from hardware to business logic)
9. Statement of the problem • Database monitoring in general • Proactive and reactive • Cost • Skills • Scalability • Levels of monitoring (from hardware to business logic) • Service models (managed service) and service providers and what they use
10. Comparison of existing solutions • Monitoring • Tuning • SQL Server • Windows • Network • Reporting • Alerting • Advice
11. Features • Historical monitoring data
12. Features • Historical monitoring data • Close to real-time
13. Features • Historical monitoring data • Close to real-time • Alerts via email
14. Features • Historical monitoring data • Close to real-time • Alerts via email • Customizable thresholds
15. Features • Historical monitoring data • Close to real-time • Alerts via email • Customizable thresholds • Advanced SQL Server features monitoring
16. CPU load
17. Memory consumption
18. Further plans • Windows monitoring
19. Further plans • Windows monitoring • Other RDBMS (PostgreSQL, MySQL, Oracle)
20. Further plans • Windows monitoring • Other RDBMS (PostgreSQL, MySQL, Oracle) • Linux
21. Further plans • Windows monitoring • Other RDBMS (PostgreSQL, MySQL, Oracle) • Linux • AI, Data mining
22. Architecture Laptops
23. Problems solved • More than 100 counters • Machine Metrics • SQL Server metrics • Database metrics • High Availability metrics (AO, Logshipping, Mirroring) • BI metrics (SSRS, SSAS)
24. Problems solved • More than 100 counters • Trend analysis and prediction
25. Problems solved • More than 100 counters • Trend analysis and prediction • Ability to drill down from high level problem to root cause
27. Problems solved • More than 100 counters • Trend analysis and prediction • Ability to drill down from high level problem to root cause • Meet growing business needs
28. Business value • Operations throughput
29. Business value • Operations throughput • Decision making
30. Business value • Operations throughput • Decision making • Cost of failure
31. Business value • Operations throughput • Decision making • Cost of failure • Capacity planning (volume, type of hardware)
32. Business value • Operations throughput • Decision making • Cost of failure • Capacity planning (volume, type of hardware) • DB monitoring is usually undervalued
33. Business value • Operations throughput • Decision making • Cost of failure • Capacity planning (volume, type of hardware) • DB monitoring is usually undervalued • Examples: DELL EMC, VIVA, Spar, Stock market Brokers
34. Real life cases • Examples: DELL EMC, VIVA, Spar, Stock market Brokers • DELL EMC From Nagios and basic alerts to comprehensive DB monitoring • VIVA From basic alerts • Spar From no monitoring • Stock market brokers From SCOM • WallMart from no monitoring
35. Nagios case detailed (DELL EMC) • Before • Only basic monitoring • After • Comprehensive monitoring
36. Benefits of the solution • Open Source agents
37. Benefits of the solution • Open Source agents • Low to no impact on performance
38. Benefits of the solution • Open Source agents • Low to no impact on performance • Secure
39. Benefits of the solution • Open Source agents • Low to no impact on performance • Secure • Scalable
40. Benefits of the solution • Open Source agents • Low to no impact on performance • Secure • Scalable • Free
41. Benefits of the solution • Open Source agents • Low to no impact on performance • Secure • Scalable • Free • Customizable
42. Benefits of the solution • Open Source agents • Low to no impact on performance • Secure • Scalable • Free • Customizable • Authors
43. Questions
44. Contacts • www.dimenko.com • roman.dimenko@gmail.com • LinkedIn: https://ru.linkedin.com/in/romandimenko • Early adopters are welcome
45. Thank you!