Most DBAs at one time or another have been alerted to a major issue on a SQL Server, only to find out after the standard fire drill to discover the root cause that someone made a SQL Server configuration change such as server-level maximum degree of parallelism (MAXDOP).
This setting change, and many others, are common causes of SQL Server issues. In fact, SQL Sentry provides an Advisory Condition to detect when the MAXDOP setting, among others, is changed. In a recent blog post, SentryOne CTO John Welch walks through how to configure this Advisory Condition to warn you if the MAXDOP setting no longer meets the Microsoft recommendation and automatically adjust it for you.
A Single Source of Truth
Are you looking for a comprehensive way to document important settings across all your servers? Would it be even more useful if that documentation was automatically generated and updated as changes were made? What if you could perform a comparison check against a server’s current settings and how it was configured yesterday, last week, etc. and immediately flag any differences between two points in time?
These are just a few SentryOne Document capabilities that can help you manage your SQL Servers (and a big way SentryOne differentiates itself from many other database documentation tools on the market). There are a lot of inexpensive solutions, or even free open-source tools, that can help you document a SQL Server database and even provide visual schema mapping of that database. But those tools are typically limited to documenting the database, not the entire server. Schema mapping is a valuable and integral part of what SentryOne Document provides, and just one of the many capabilities available in SentryOne Document.
In the screenshot below, you can see an example of the documentation generated by SentryOne Document. This type of documentation can be made available to anyone with permissions through our cloud-hosted web interface or through your local intranet if you opt for the fully installed software.
The Big Picture
SentryOne Document isn’t limited to only SQL Server—it supports the entire Microsoft SQL Server BI stack. You can keep track of SQL Server Analysis Services (SSAS) memory limits just as easily as SQL Server max server memory. In fact, it’s this support of the entire stack that allows SentryOne Document to provide a data lineage map for your data’s journey throughout your estate, from source database to Power BI report, with every hop through a SQL Server Integration Services (SSIS) package or SSAS cube along the way. And, SentryOne Document now provides that functionality beyond SQL Server technologies to platforms such as Salesforce or Tableau. Check out Tyler Lynch’s blog post for more details about those features.
What Changed?
Having up-to-date documentation of all your server’s settings is critical. But when something changes, knowing exactly which setting was changed and what it was originally set to before performance degraded can be extremely valuable in getting your server back to the “last known good” configuration.
These changes can be immediately identified with SentryOne Document’s Version History and Document Comparison. The Side by Side View shows you the complete list of documented settings for both time frames, as shown in the screenshot below.
If you need to quickly drill down and highlight any differences in the comparison, the Diff View quickly brings to your attention exactly what changed between snapshots.
You can see the old value highlighted in red and the new value in green. (Note that the red rectangles in both screenshots were added by me to highlight the area of interest.)
Although I focused on SQL Server settings in this blog post, SentryOne Document provides schema documentation, data lineage analysis, and dependency tracking for all the databases on your server. For example, instead of a server property change, the issue might be a dropped index or modified column in a table that’s critical to your application.
No Plutonium Needed!
If any of these capabilities would be valuable to you, I encourage you to take a closer look at SentryOne Document.
We have a new live online SentryOne Document demo environment you can check out. It’s important to note that the SentryOne Document demo environment doesn’t have our server-level settings included in the documentation, as they are our own hosted servers. But the demo includes great examples of database schema details, along with the rest of the BI stack.
