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.
Sugli autori
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.
Altri risultati simili a questo
Ricerca per canale
Automazione
Novità sull'automazione IT di tecnologie, team e ambienti
Intelligenza artificiale
Aggiornamenti sulle piattaforme che consentono alle aziende di eseguire carichi di lavoro IA ovunque
Hybrid cloud open source
Scopri come affrontare il futuro in modo più agile grazie al cloud ibrido
Sicurezza
Le ultime novità sulle nostre soluzioni per ridurre i rischi nelle tecnologie e negli ambienti
Edge computing
Aggiornamenti sulle piattaforme che semplificano l'operatività edge
Infrastruttura
Le ultime novità sulla piattaforma Linux aziendale leader a livello mondiale
Applicazioni
Approfondimenti sulle nostre soluzioni alle sfide applicative più difficili
Serie originali
Raccontiamo le interessanti storie di leader e creatori di tecnologie pensate per le aziende
Prodotti
- Red Hat Enterprise Linux
- Red Hat OpenShift
- Red Hat Ansible Automation Platform
- Servizi cloud
- Scopri tutti i prodotti
Strumenti
- Formazione e certificazioni
- Il mio account
- Supporto clienti
- Risorse per sviluppatori
- Trova un partner
- Red Hat Ecosystem Catalog
- Calcola il valore delle soluzioni Red Hat
- Documentazione
Prova, acquista, vendi
Comunica
- Contatta l'ufficio vendite
- Contatta l'assistenza clienti
- Contatta un esperto della formazione
- Social media
Informazioni su Red Hat
Red Hat è leader mondiale nella fornitura di soluzioni open source per le aziende, tra cui Linux, Kubernetes, container e soluzioni cloud. Le nostre soluzioni open source, rese sicure per un uso aziendale, consentono di operare su più piattaforme e ambienti, dal datacenter centrale all'edge della rete.
Seleziona la tua lingua
Red Hat legal and privacy links
- Informazioni su Red Hat
- Opportunità di lavoro
- Eventi
- Sedi
- Contattaci
- Blog di Red Hat
- Diversità, equità e inclusione
- Cool Stuff Store
- Red Hat Summit