Feed abonnieren

table { border: #ddd solid 1px; } td, th { padding: 8px; border: #ddd solid 1px; } td p { font-size: 15px !important; }

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.


Über die Autoren

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. Louis joined Red Hat as part of the acquisition of Permabit Technology Corporation, where he was VP of Product.

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
UI_Icon-Red_Hat-Close-A-Black-RGB

Nach Thema durchsuchen

automation icon

Automatisierung

Das Neueste zum Thema IT-Automatisierung für Technologien, Teams und Umgebungen

AI icon

Künstliche Intelligenz

Erfahren Sie das Neueste von den Plattformen, die es Kunden ermöglichen, KI-Workloads beliebig auszuführen

open hybrid cloud icon

Open Hybrid Cloud

Erfahren Sie, wie wir eine flexiblere Zukunft mit Hybrid Clouds schaffen.

security icon

Sicherheit

Erfahren Sie, wie wir Risiken in verschiedenen Umgebungen und Technologien reduzieren

edge icon

Edge Computing

Erfahren Sie das Neueste von den Plattformen, die die Operations am Edge vereinfachen

Infrastructure icon

Infrastruktur

Erfahren Sie das Neueste von der weltweit führenden Linux-Plattform für Unternehmen

application development icon

Anwendungen

Entdecken Sie unsere Lösungen für komplexe Herausforderungen bei Anwendungen

Original series icon

Original Shows

Interessantes von den Experten, die die Technologien in Unternehmen mitgestalten