Panagis Artoumas

28 October 2021

• Redgate Monitor
• Monitoring Large Estates
• Database Performance Monitoring

Tracking SQL Server Configuration Across All Your Servers Using SQL Monitor

If someone makes unexpected or unauthorized changes to the configuration of any of your SQL Servers instance or databases, it could affect their performance, availability or even security. It’s therefore important that DBAs understand what each of the instance- and database-level settings do, and then documents how they should be set on each of their servers, to meet security and performance best practices.

They then need to record and track the values of the most important configuration settings, on each of their production and development servers, so that they can investigate any significant discrepancies between what they are and what they should be. In theory, you can tackle this task with a mixture of homegrown scripts, export files and spreadsheets. However, as server estates grow and diversify, incorporating both on-premises and cloud instances, and serving multiple and interconnected applications, maintained by different teams, it becomes an increasingly difficult challenge.

SQL Monitor is ideally placed to relieve the burden of this task. Firstly, it is already authorized to collect, store and analyze all of the server, instance and database-level configuration data that you need to track, across your monitored estate. Secondly, in V12, we’ve made big improvements to the Estate Configuration page, making it very easy to review all these SQL Server and database configuration settings in one place, track variance, and to compare settings across instances.

Overview of the Estate Configuration page

You’ll find the Estate Configuration page within the Estate menu of the SQL Monitor web interface. As of release 12.0, the Estate Configuration page reports on-prem server configurations only, but support for Amazon RDS SQL Server, Azure SQL Managed Instances, and Azure SQL databases is coming soon.

Let’s take a walk through the different areas of the page and see how they can help you manage and track your SQL Server configuration.

Figure 1: The new estate configuration page

Configuration change log

The Configuration change log provides an overview of any configuration changes made to servers in your estate, over the last seven days. For each change, it records the name of the monitored server, the property that changed, its previous and new values, and the date that the change was detected.

This is helpful for performing a quick review of any changes, so that you can spot any unexpected or unauthorized ‘drift’ in configuration. Note that, if enabled, the configuration change alert can warn you of changes in SQL Server configuration, and then you can use the configuration change log to investigate exactly what happened.

Configuration variance

This section offers a visual summary of how a selection of key SQL server properties and options are configured, across your estate. If documented security policy dictates, for example, that configurations such as CLR Enabled or Ole Automation Procedures are disabled, then the configuration variance feature will reveal any violations immediately.

In Figure 2 we can see that the Common Language Runtime, which specifies that user assemblies can be run by SQL Server, is enabled for 2 instances, and disabled for 6.

Figure 2: Configuration variance for CLR property

To get more context, click on the property, and a window opens containing a description of the property and its value per server, as shown in Figure 3. By examining the values per server, you can check whether a configuration setting differs from its documented value or has drifted outside the ‘normal range’ compared to other servers, potentially indicating a misconfigured server. In our example, this window reveals immediately the two instances that have the CLR configuration enabled.

Figure 3: Detailed information for CLR property

Configuration table

The configuration table provides detailed insight into configuration across your estate and is intended for when you want to spend a bit more time drilling into specific settings, and the relationships between them.

Detailed documentation of server and database configuration settings

Each row of the table represents a monitored server, and each column shows a property. The properties are categorized into four groups: security, environment, performance and database. The first three groups contain server- or instance-related properties, whilst the latter contains only database-level configurations. Certain database-level configuration settings, such as MAXDOP, might override the corresponding instance-level setting.

Simply use the Add or remove properties link, in each group, to establish the set of server and database-level configuration options that you want to track in detail, across your estate. If you click on an instance, you can drill down and review the configuration of individual databases

Figure 4: Dropdown for adding/removing properties

Comparing configuration between servers

You can compare the configuration of two or more servers simply by selecting the servers of interest and clicking the Compare selected button, as shown in Figure 5. For example, you can use it to compare the configuration of different replicas of an availability group. Also, if you provision a new server, you can use it to compare its configuration against a ‘baseline’ server.

Figure 5: Comparison of configuration options between servers

Exporting configuration settings

After checking your configuration, you might want to export the data to an Excel spreadsheet for external analysis or share it with your colleagues, as shown in Figure 6.

Figure 6: Exporting configuration to an Excel spreadsheet

Conclusion

If you’re managing just a small handful of on-prem servers, then it’s possible to record their configuration, and track any changes, using custom queries, scripts and spreadsheets. However, as server estates grow and diversify, so this approach becomes increasingly time-consuming and difficult to maintain.

We hope that the Estate configuration page in SQL Monitor removes this burden. It provides DBAs with all the tools they need to track and analyze the configuration of their SQL Servers, and to quickly compare configuration between servers. It should enable them to spot discrepancies or worrying anomalies very quickly.

We first released an early preview of the Estate configuration page back in early 2021, collecting only a limited set of configuration options, and offering a high-level overview of variance in the settings. It’s come a long way since then. We’ve introduced a much richer selection of configuration options, according to industry-standard benchmarks, and worked hard to ensure it scales to large-estate monitoring.

All these improvements have been based directly on customer research and feedback, so please do try it out in SQL Monitor v12, and continue to send us your feedback, so that we can continue to improve this and other SQL Monitor features.