SQL Server database best practices now available through Red Hat Insights

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.

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

2. 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

3. 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

4. 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

5. Install the SQLServer module for PowerShell. This module includes the assessment API.

# su mssql -c "/usr/bin/pwsh -Command Install-Module SqlServer"

6. 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

7. 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/  

8. 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"

9. 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.