Check SQL Server Instance and Database Best Practice Settings

By: Jeffrey Yao | Updated: 2022-10-25 | Comments (2) | Related: > Monitoring

Problem

When I join a new company, I usually take over the responsibility for many SQL Server instances. How can I quickly assess the environment to see how well those instances are configured and how well the databases are managed?

As an example, I once joined a company where I found the "Max Server Memory" setting was not configured correctly. For example, the physical memory was 64 GB, but the max server memory was set to 60 GB. I could not convince others to change it because no issue has been reported due to the setting. It would have benefitted me and been convincing if Invoke-SqlAssessment had been available to show them the results.

Solution

From a big-picture perspective, we all want to see that the Microsoft-recommended best practices are followed in our SQL Server instance configurations or database setups, such as multiple data files for TempDB, using instant file initialization, setting max server memory properly, etc. Usually, we have to write our own scripts to do such assessments, which are not necessarily updated with the new SQL Server releases.

Fortunately, Microsoft has introduced the capability in the SQL Server PowerShell module via the cmdlet Invoke-SqlAssessment.

This tip will explore how we can perform assessments across multiple SQL Server instances and save the results in a centralized table. Invoke-SQLAssessment can even use customized rules for assessment. For example, we can make a rule that all databases should have a specific login, like 'sa', instead of a user's account as database owner, or we want to ensure that a database is not having more than one log file, etc. The sky is the limit to how we define our business rules for assessment.

Environment Setup

I always prefer a dedicated server for the DBA team, where they can access all SQL Server instances. On this dedicated server, we install a dedicated SQL Server instance, and if needed, we can also install SSAS and SSRS. Then, we can install the SQL Server PowerShell module.

Quick Introduction to Invoke-SqlAssessment

Invoke-SqlAssessment can assess the following SQL Server management objects:

Microsoft.SqlServer.Management.Smo.Server
Microsoft.SqlServer.Management.Smo.Database
Microsoft.SqlServer.Management.Smo.AvailabilityGroup
Microsoft.SqlServer.Management.Smo.FileGroup
Microsoft.SqlServer.Management.Smo.RegisteredServers.RegisteredServer
String containing path to any object of the above types
Collection of objects

The first two objects are likely the most essential to every SQL Server instance. This tip will demonstrate the assessment of SQL Server instances and databases.

Here is an example of whether a SQL Server instance has its "Max Server Memory (MB)" configured properly. I will use my local SQL Server instance, i.e., [localhost\sql2019], the only instance on my local computer.

My physical memory is 16GB, and I have set the "Max Server Memory (MB)" to 14.5GB = 14848MB, as shown below.

MaxServer Setting physical mem=16GB Max memory=14848 MB

import-module sqlserver;
Get-SqlInstance -ServerInstance localhost\sql2019 | 
Invoke-SqlAssessment -Check maxmemory -FlattenOutput;

The assessment result shows the "Max Server Memory" is configured too high, indicating the value should be 12,398 MB (roughly 12GB) or less.

Assessment Items

If we want to see what items can be assessed or checked, i.e., the value after parameter check, we can do the following. To check the SQL instance level items, it is:

Get-sqlinstance -server localhost\sql2019 | get-sqlassessment | sort-object -property ID

As the image below shows, we can see lots of check items.

assessment items for sql server instance (part_1)

This includes the one we used, i.e., MaxMemory.

assessment items for sql server instance (part_2)

To check items for a database, we can easily do the following (Note: dbatools is a user database):

Get-sqldatabase -server .\sql2019 -database dbatools | get-sqlassessmentitem | sort ID

Now, if I want to check the full backup status of the database, i.e., [dbatools]:

Get-sqldatabase -server .\sql2019 -database dbatools | invoke-sqlassessment -check fullbackup -flattenOutput

As we can see, the database has not been backed up (full backup) for more than seven days.

If we want to check all items, we remove -check parameter:

Get-sqldatabase -server .\sql2019 -database dbatools | invoke-sqlassessment -flat

Build a Solution to Collect Assessment Results

With the flexibility of Invoke-SqlAssesment, we can build an assessment framework for various SQL Server objects, including instances, databases, or high availability groups.

The following script is to scan a group of SQL Server instances (via a parameter input) and save the result in a centralized table.

-- we first create a table in a centralized server / database
-- I use my local sql instance / database as a central repository
-- i.e. [localhost\sql2019].[dbatools]
USE [DBAtools]
GO
drop table if exists dbo.SQLInstanceAssessment;
go
-- central repository table
CREATE TABLE [dbo].[SQLInstanceAssessment](
   [CheckId] [varchar](60) NULL,
   [Severity] [varchar](12) NULL,
   [Server] [varchar](60) NULL,
   [Message] [varchar](2048) NULL,
   [LogDate] [datetime] NULL default getdate(),
   [BatchNum] [int] NULL,
   [id] [int] IDENTITY(1,1) NOT NULL primary key,
)

After the table is created, we can run the following PowerShell script to get the assessment values into this table.

# doing sql server assessment and save the result to a central repository
# min severity is warning (i.e. no info level assessment needed)
# I especially filter out check item 'DeprFeaturesInJobs' due to too many items reported back
import-module sqlserver;
$svr_list = 'Server01', 'Server02'; # replace it with your own server list
$central_svr = '.\sql2019'; # replace it with your own central server
$central_db = 'dbatools'; # replace it with your own central db on the central server
get-sqlinstance -ServerInstance $svr_list | Invoke-SqlAssessment -FlattenOutput -MinSeverity warning | where checkid -ne 'DeprFeaturesInJobs' |  
select checkid,  severity, @{l='Server'; e={[regex]::match($_.targetpath, "\'(.*)\'").groups[1].value}}, message |
Write-SqlTableData -ServerInstance $central_svr -DatabaseName $central_db -SchemaName dbo -TableName SQLInstanceAssessment;
 
$qry = 'update dbo.SQLInstanceAssessment set batchnum = (select max(isnull(batchnum, 0)) +1 from dbo.SQLInstanceAssessment) where batchnum is null';
Invoke-Sqlcmd -ServerInstance $central_svr -Database $central_db -Query $qry;

All assessment results for Server01 and Server02 are stored in a central table, as shown below. You can check multiple SQL Server instances simultaneously if you put all instance names into $svr_list variable.

Summary

In this tip, we discussed how to assess the best practice implementation in SQL Server environments using a cmdlet (Invoke-SqlAssessment) from the SQL Server PowerShell module and store the assessment results in a central table. If we schedule a SQL job to do such collection weekly for SQL Server instances/databases, we will surely avoid many unnecessary problems and keep the environment healthier.

Next Steps

Please take a look at a few useful links on assessments:

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-10-25

Quickly Evaluate SQL Server Best Practices with Invoke-SqlAssessment