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
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.
-
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
-
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
-
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
-
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
-
Install the SQLServer module for PowerShell. This module includes the assessment API.
# su mssql -c "/usr/bin/pwsh -Command Install-Module SqlServer"
-
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
-
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/
-
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"
-
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.
À propos des auteurs
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.
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.
Parcourir par canal
Automatisation
Les dernières nouveautés en matière d'automatisation informatique pour les technologies, les équipes et les environnements
Intelligence artificielle
Actualité sur les plateformes qui permettent aux clients d'exécuter des charges de travail d'IA sur tout type d'environnement
Cloud hybride ouvert
Découvrez comment créer un avenir flexible grâce au cloud hybride
Sécurité
Les dernières actualités sur la façon dont nous réduisons les risques dans tous les environnements et technologies
Edge computing
Actualité sur les plateformes qui simplifient les opérations en périphérie
Infrastructure
Les dernières nouveautés sur la plateforme Linux d'entreprise leader au monde
Applications
À l’intérieur de nos solutions aux défis d’application les plus difficiles
Programmes originaux
Histoires passionnantes de créateurs et de leaders de technologies d'entreprise
Produits
- Red Hat Enterprise Linux
- Red Hat OpenShift
- Red Hat Ansible Automation Platform
- Services cloud
- Voir tous les produits
Outils
- Formation et certification
- Mon compte
- Assistance client
- Ressources développeurs
- Rechercher un partenaire
- Red Hat Ecosystem Catalog
- Calculateur de valeur Red Hat
- Documentation
Essayer, acheter et vendre
Communication
- Contacter le service commercial
- Contactez notre service clientèle
- Contacter le service de formation
- Réseaux sociaux
À propos de Red Hat
Premier éditeur mondial de solutions Open Source pour les entreprises, nous fournissons des technologies Linux, cloud, de conteneurs et Kubernetes. Nous proposons des solutions stables qui aident les entreprises à jongler avec les divers environnements et plateformes, du cœur du datacenter à la périphérie du réseau.
Sélectionner une langue
Red Hat legal and privacy links
- À propos de Red Hat
- Carrières
- Événements
- Bureaux
- Contacter Red Hat
- Lire le blog Red Hat
- Diversité, équité et inclusion
- Cool Stuff Store
- Red Hat Summit