Anmelden / Registrieren Konto

In May of 2019, the Red Hat Insights team introduced a new set of Red Hat Insights recommendations for Microsoft SQL Server running on Red Hat Enterprise Linux (RHEL). These rules alert administrators to operating system level configurations which are not conforming to the documented recommendations from Microsoft and Red Hat. 

A limitation of these rules was that they primarily analyzed the operating system and not the database itself. With the latest version of Red Hat Insights and RHEL 8.5, this changes with the introduction of Microsoft’s SQL Assessment API. This post will provide an overview of this tool as well as walk through how to set it up.

What is SQL Assessment API?

Microsoft’s SQL Assessment API provides a mechanism to evaluate the database configuration of SQL Server for best practices. The API is delivered with a ruleset containing best practice rules suggested by the SQL Server Team. While this ruleset is enhanced with the release of new versions, the API is built with the intent to give a highly customizable and extensible solution. So, users can tune the default rules and create their own. 

The SQL Assessment API is supported by PowerShell for Linux (available from Microsoft), and Microsoft has developed a PowerShell script that can be used to call the API and store its results as a JSON formatted file. With RHEL 8.5 RHEL’s insights-client now uploads this JSON file and Insights at the Hybrid Cloud Console presents the results in an easy-to-understand format.

Red Hat Insights Advisories generated by the SQL Assessment API

Red Hat Insights Advisories generated by the SQL Assessment API

What rules are available?

Here are the new rules interpreted for SQL Server by Insights. More are planned to be added over time.

Rule

What it does

Microsoft SQL Server performance degrades when both the affinity mask and the affinity I/O mask are enabled on a CPU.

This rule warns you if two conflicting settings overla potentially degrading performance.

Memory-optimized Azure VM sizes offer a high memory-to-CPU ratio that improves Microsoft SQL Server database performance.

This rule is triggered if you’re not running with enough memory in an Azure VM environment. Lack of memory potentially degrades performance and can even cause downtime.

Collation conflict prevents code execution when the Microsoft SQL Server database collation does not match the master collation.

This rule notifies you that an application database is using a different collation than the master database, which prevents code execution. The identified issue can cause loss of functionality and downtime.   

Microsoft SQL Server database recovery from an unexpected accident will fail when full backup has not been performed for more than seven days.

This rule makes sure that you are performing backups regularly so that database recovery is possible. The identified issue can cause data loss.

Microsoft SQL Server query performance gets degraded when the index is fragmented heavily.

This rule warns you if your SQL Server database index has become fragmented, which can degrade performance.

Thread starvation will occur when the value of maxdop of Microsoft SQL Server exceeds the number of processors.

This helps you make sure that thread starvation doesn’t occur due to setting a higher maximum degree of parallelism than the number of processor cores available. 

Memory competition can occur when the Microsoft SQL Server max memory value is higher than system memory.

This rule warns you to reset SQL Server’s maximum memory to below system memory to avoid performance degradation from swapping.

Microsoft SQL Server query performance gets degraded when the statistics become out-of-date.

The Query Optimizer uses statistics to create query plans that improve query performance in the Microsoft SQL Server. When the statistics become out of date, Insights warns you because this can result in less than optimal query performance. 

The auto-growth settings for each TempDB data file should be the same for optimal performance in the Microsoft SQL Server.

A Proportional Fill writing mechanism is used to write the data inside the data files. This rule reminds you that you want TempDB data files to be of equal size for the highest possible degree of parallel operations efficiency.

The number of tempdb data files should depend on the number of logical processors for optimal performance in the Microsoft SQL Server.

This rule warns you when a host is running as a Microsoft SQL Server and the number of tempdb data files does not match the number of (logical) processors. 

Microsoft SQL Server performance degrades when the tempdb data files are not the same size

A Proportional Fill writing mechanism is used to write the data inside the data files. This rule reminds you that you want TempDB data files to be of equal size for the highest possible degree of parallel operations efficiency.

Microsoft SQL Server database recovery from an unexpected accident will fail when transaction log backup has not been performed for more than 24 hours

This rule makes sure that you are performing backups regularly so that database recovery is possible.

Setting up SQL Server Assessments

To configure the Microsoft SQL Assessment API to provide information to Red Hat Insights, the DBA needs to take the following steps.

  1. In the database you wish to assess, create a login for SQL Server assessments using SQL Authentication. The following Transact-SQL creates a login. Replace  <*PASSWORD*> with a strong password:

USE [master]
GO
CREATE LOGIN [assessmentLogin] with PASSWORD= N'<*PASSWORD*>’
ALTER SERVER ROLE [sysadmin] ADD MEMBER [assessmentLogin]
GO
  1. Store the credentials for login on the system as follows, again replacing <*PASSWORD*> with the password you used in step 1.

     # echo "assessmentLogin" > /var/opt/mssql/secrets/assessment
     # echo "<*PASSWORD*>" >> /var/opt/mssql/secrets/assessment
  1. Secure the credentials used by the assessment tool by ensuring that only the mssql user can access the credentials.

     # chmod 0600 /var/opt/mssql/secrets/assessment
     # chown mssql:mssql /var/opt/mssql/secrets/assessment
  1. Download PowerShell from the microsoft-tools repository. This is the same repository you configured when you installed the mssql-tools and mssqlodbc17 packages as part of SQL Server installation.

           # yum -y  install powershell
  1. Install the SQLServer module for PowerShell. This module includes the assessment API.

# su mssql -c "/usr/bin/pwsh -Command Install-Module SqlServer"
  1. Download the runassessment script from Microsoft’s examples GitHub repository. Ensure it’s owned and executable by mssql.

# /bin/curl -LJ0 -o /opt/mssql/bin/runassessment.ps1 https://raw.githubusercontent.com/microsoft/sql-server-samples/master/samples/manage/sql-assessment-api/RHEL/runassessment.ps1 
     # chown mssql:mssql /opt/mssql/bin/runassessment.ps1
     # chmod 0700 /opt/mssql/bin/runassessment.ps1
  1. Create the directory that will store the log file used by Red Hat Insights.  Again, make sure it’s owned and executable by mssql.

   # mkdir /var/opt/mssql/log/assessments/
# chown mssql:mssql /var/opt/mssql/log/assessments/ 
# chmod 0700 /var/opt/mssql/log/assessments/  
  1. You can now create your first assessment, but be sure to do so as the user mssql so that subsequent assessments can be run automatically via cron or systemd more securely as the mssql user.

# su mssql -c "pwsh -File /opt/mssql/bin/runassessment.ps1"
  1. Insights will automatically include the assessment next time it runs, or you can initiate Red Hat Insights by running this command: 

# insights-client

Because SQL Server Assessments can take 10 minutes or more to complete, it may or may not make sense for you to run the assessment process automatically every day. If you would like to run them automatically, Red Hat’s SQL Server community has created  systemd service and timer files to use with the assessment tool. You can download these from Red Hat’s public SQL Server Community of Practice GitHub site.

You’ll find two files there, mssql-runassessment.service and  mssql-runassessment.timer. Both files should be installed in the directory: /etc/systemd/system/ as follows:

# cp mssql-runassessment.service /etc/systemd/system/
# cp mssql-runassessment.timer /etc/systemd/system/
# chmod 644 /etc/systemd/system/

At this point you can enable the timer with:

# systemctl enable --now mssql-runassessment.timer

Conclusion

Red Hat continues to work together with Microsoft to enhance the overall experience for SQL Server administrators on the RHEL platform. With RHEL 8.5, we’ve made strides in performance, security enhancements, and manageability all further enhanced by Red Hat Insights.  Learn more about the benefits of RHEL for SQL Server on the Microsoft SQL Server on Red Hat Enterprise Linux overview page.


About the authors

Louis Imershein is a Product Manager at Red Hat focussed on Microsoft SQL Server and database workloads. He is responsible for working with Microsoft and Red Hat engineering to ensure that SQL Server performance, management, and security is optimized for Red Hat platforms. For more than 30 years, Louis has worked in technical support, engineering, software architecture, and product management on a wide range of OS, management, security, and storage software projects.

Read full bio

John Spinks is a Senior Principal Technical Marketing Manager for Red Hat. He acts as a subject matter expert for Red Hat Management products including Satellite and Insights. Previous experience includes almost 10 years as a Technical Marketing Engineer for NetApp in RTP, NC.

Read full bio