Why Comprehensive Health Checks for MS SQL Servers Matter: Part 2

First part of this blog post: https://blog.pythian.com/why-comprehensive-health-checks-for-ms-sql-servers-matter-part-1/.

The SQL Server Health Check is ready to generate many reports. Four of them are useful and critical in ensuring your SQL Server environment is performing optimally, secure, and can recover easily:

• Best practices configuration review: a comprehensive document that lists all the configuration items that are recommended to be changed to make your SQL Server instances run adherent to the current market best practices (Microsoft and Community recommendations).
• Backup review: a document that shows all the recoverability points to make sure that all your databases are readily available in the case of an incident.
• Performance Analysis review: a report that will show what needs to be addressed to drastically improve the overall instance performance.
• Security Review: an important document that lists all the points to be remediated to make your instances more secure and reduce its surface attack area.

Let’s take a closer look at a few examples of each report.

Best Practices Configuration Review

This is the most desired report. Out of a quick analysis of your SQL Server instance, it will provide you with recommendations that are identical to those provided by Microsoft and based on SQL Server Community experts Best Practices, also based on our 20+ years of experience supporting SQL Server environments.

The main points added in the Report includes but are not limited to:

• SQL server instance settings (CPU, memory, services, security, etc);
• Database configurations (Recovery model, growth rate, Filegroups, etc);
• High level index and T-SQL analysis;
• Windows settings (Page files, memory Page locks, running services, Anti-Virus etc);
• Storage settings (i.e.: Block size);
• Network settings (general Best Practices).

Picture 1: Best Practices Configuration Review sample

Backup Review

Data is at the heart of every company. You must be able to easily recover from disasters involving the systems that store that data (in this case MS SQL Server). Having outdated or no backups at all is a recipe for disaster.

Your business has a recovery point objective, or how much data you can afford to lose, and a recovery time objective represented by how much time can elapse, in the case of a disaster, before engineers reestablish their systems. 

This is what we aim to provide with that report–a means for your company to successfully achieve the agreed RPO and RTO levels.

Picture 2: Backup Review Report sample

Performance Analysis

Data grows and with that growth a few issues may arise. For example, statistics that were estimating performance pathways properly are not being updated properly or currently have poor estimation metrics. Tables have many “holes” in their basic build units (8 KB pages) and your system could benefit from what we call low hanging fruits: performance problems that could be easily addressed because data volume changed over time. There’s also a different category of issues which may come up depending on how your system queries were built and even depending on the logic used on development. Either way, the performance analysis report has got your instance covered.

Picture 3: Performance Review Report sample

Security Review

Keeping your data away from malicious hands (or eyes) has always been a deep concern from every business owner. In this document, you’ll find a review of the instance’s host operating system, as well as the SQL Server instance and its object’s secureness.  

The review is not an audit (this is a feature you enable at the SQL Server level), but it’s based on a number of best practices for SQL Server and Pythian’s extensive experience in managing, administering, and configuring database systems in a secure and enterprise-level manner.

Reviewed Items include but are not limited to:

• SQL Server, OS, and Server Hardware Configurations;
• SQL Server Surface Area and Facets;
• Presence of unnecessary database options and features;
• User Logins/roles permissions and memberships;
• Other opportunities to increase security.

Picture 4: Security Review Report sample

I hope this post useful. Let me know if you have any questions in the comments, and don’t forget to sign up for the next post.