You were recently hired as a database administrator for CSUG

You were recently hired as a database administrator for CSU-Global Campus. During grade reporting, CSG-Global cannot afford to have any downtime. You have been asked to develop a plan for monitoring SQL Server databases. Address these aspects of the project in your discussion post:

Develop a prioritized list of daily tasks in the order in which they should be performed to monitor the database.

Identify and explain tasks that you can automate.

Explain the benefits of establishing a baseline to benchmark the performance of SQL Server.

Solution

Required details prior to build a plan for monitoring and doing daily tasks for health checks

1.      To develop a plan for monitoring SQL Server databases we need to prepare a list of database servers that are to be monitored, with a complete list of databases residing on each database server and with the details like SQL versions, Server properties, Database properties, SQL Server Instance-level configuration, Database-level configuration and the Security permission on the database . We have to maintain all these data and then store it in a central repository, for reporting and analysis. This collection of data can also be automated using some scripts and tools.

2.      These details help us in effective monitoring, alerting and reporting, where we are responsible for telling you that something’s failed on SQL server or when it goes down. So that we can respond to an emergency before too many people are affected.

Plan for Monitoring Database with a Prioritized List of Daily Tasks

1.      First start with a relatively small set of base alerts, across your servers and databases to get comfortable with the level of alerting, monitor growth of the central repository to get a feel for how much data that you are collecting.

2.      Capture the data and logs and decide for how long to keep historical data online. Purge any data older than your required period or migrate the data for longer-term storage and analysis.

3.      A scheduled backup plan is required we need to be sure that all required backups (full and differential database and log backups) are running successfully. Based on the requirement it can be daily, weekly or monthly. Successfully backed up database can be restored yet times require in any issues of for further analysis.

4.      Health Monitoring should be performed on a daily basis to monitor the core services of the SQL Server and Server Agent.

5.      A regular check of disk space, and database file sizes should be monitored for capacity planning and for better performance.

6. Resource usage monitoring like SQL Server-wide CPU, I/O and Memory usage should be done daily and tracked over time on

Tasks That You Can Automate

Benefits:

This process helps you visualize and manage SQL server database and provides in-depth SQL monitoring data that helps to plan capacity and notifies you any critical issues before they effect on large scale. Also helps you in root cause analysis to troubleshoot performance problems quickly. Overall it helps in effective health maintenance and better performance of the SQL Database Servers.