"Paddy" Database monitoring series (i)-monitoring concept

Most of the time, talk to friends around the two questions, what is the daily work of the DBA? What is the most important database aspect?

I believe the answers to these two questions will not be exactly the same, I am also, so here only represents my personal views, and the following content will be around my ideas, I hope to have more or less help.

What are the daily tasks of Q1:DBA?

A:

1. Installation deployment of the database
2. Database monitoring
3. Database optimization
4. Database failure handling
5. Other

Q2: What is the most important database aspect?

A: 　　 stability

Many companies, DBAs and management of the number of db than 1:10, 1:50 is not much, 1:100 have seen, database optimization, high availability, monitoring and so on are to ensure the stability of the database, stability if not guaranteed, other work can not be carried out smoothly, then in the work of ensuring stability, The monitoring is particularly prominent. My personal view, the DBA daily 80% of the work is in the monitoring DB, automation is essential, in order to save your 79% of monitoring time, you have to install, optimize, troubleshooting, with the developer work time, then 80%, 1% where to go? The answer is that manual monitoring will occupy 1%. Any monitoring program can never be 100% instead of artificial, just as your mother puts you in kindergarten and knows what you are doing in kindergarten , so you need to look at the actual situation of your db from time to time.

I divided the DB monitoring into two categories: first, function monitoring, performance monitoring.

I advocate the first guarantee function, to be eligible to guarantee performance, even the function can not guarantee the monitoring, not good performance monitoring.

There are a lot of open source software, such as cacti, Zabbix, Nagios, for database or server function monitoring, Basic or call DMV, WMI to monitor, performance monitoring is basically around performance counter running value monitoring, my monitoring program is the same, However, there is a little difference between the collection and use of basic data.

Third-party monitoring program I summarize is the basic data collection + Calculate the base data + calculate after the data graph shows . The widespread use of open-source programs has created a phenomenon that some people are too reliant on, and most companies don't ask you how to use third-party tools when recruiting DBAs, and as corporate recruiters, they are more likely to recruit people who know how to collect basic data, how to calculate the underlying data, and how to present the calculated data.

In other words, how to calculate, how to show, as long as you have basic data, then everything will be a cinch.

Briefly summarize my monitoring list, in the following article, I will share how each one is implemented.

First, function monitoring

1. Connection Count Monitoring
2. Database Status Monitoring
3. Object use spatial monitoring
4. Database Mirroring Status
5. Database Job Monitoring
6. Database error log monitoring
7. Database uses spatial monitoring
8. Database blocking monitoring
9. Database Index usage Monitoring
10. Database statistic Information Monitoring
11. Database virtual log file monitoring

Second, performance monitoring

1. How multiple database performance counters are collected uniformly.
2. How to use SQL Server to generate the data you need.

Iii. How to use SQL CLR+WMI for Windows monitoring

Iv. alarm: How to use SQL Server to achieve SMS alarm and email alarm

All night overtime, this time can only say here, the above only represents my idea, if there are different views, very welcome to discuss.

"Paddy" Database monitoring series (i)-monitoring concept