UPDATED Jan 4, 2024 – Several people had mentioned they could not get the example code to work. This was because I chose to overflow a text column which only outputs the detailed error messages for SQL Server 2017 and above. I have since updated the code to use a numeric value which has been verified to work on SQL Server 2012 through 2022. Recently, one of my customers considered enabling Trace Flag 3625 on one of their SQL Servers, but they wanted a way to quickly test its functionality beforehand. For those of you that are unfamiliar with that...
Continue reading...Configuration
Get Certificate Details for SQL Encryption In-Transit Using PowerShell – UPDATED
In a previous blog post, I showed you how to use PowerShell to retrieve the details of the certificate being used for encryption-in-transit, aka TLS encryption. I have received a lot of feedback about that script and one of the most common requests was how to run it remotely. I have finally modified the script to do just that. The previous version required you to run it locally on a server. It would return the certificate information for each SQL instance that it detected. The new version can be run remotely, but it will expect you to pass in a...
Continue reading...Get Certificate Details for SQL Encryption In-Transit Using PowerShell
There is an updated version of this script here. In my opinion, all customers should be encrypting connections to their SQL Servers to secure data in-transit, but that’s a discussion for another day. Today, I wanted to discuss how you can use PowerShell to lookup details on the certificate being used for that encryption. For this example, we have three SQL Server instances each configured slightly different for encrypted communication. Using the SQL Server Configuration Manager, we can see the first instance is configured to use a certificate named “deathstar.govlab.corp (SHA256)”. The FORCE ENCRYPTION option is set to YES to...
Continue reading...Finding the NTFS Allocation Unit Size for Mount Points
The 64Kb Allocation Unit size is one of the easiest SQL Server best practice items to complete during your new builds, but from time to time, I still see this one overlooked. And I know this topic has been beat to death over the years, so we are not going to cover the why you should format volumes using 64Kb Allocation Size. We are just going to cover how to check the current value. In our setup, we have a server with 4 disks attached; two are mounted with drive letters (M: and S:) and two are mounted as folders...
Continue reading...SQL Server has encountered % occurence(s) of cachestore flush for the % cachestore (part of plan cache) due to some database maintenance or reconfigure operations
Have you seen this type of an informational message in your SQL errorlog before? If so, then I hope the timestamp associated with it was not during the middle of your peak processing time. This is a good indication that someone has been tinkering with some of the SQL configuration settings. SQL Server offers a lot of wonderful configuration options; many of which can be changed dynamically using sp_configure without the need of restarting the SQL Server service. The downside to the dynamic changes is sometimes they can have a detrimental effect on performance, and that’s what this “informational” message is tell...
Continue reading...
