Managed Service Account Naming Convention

A while back I had to help a customer update their naming standard for their Managed Service Accounts, so I thought I would share some of those details. The DBAs and other administrators had a difficult time determining which service account belonged to which server and to which service. They had a corporate requirement to have separate accounts for each service; for example, one for the engine and one for the agent. Their current naming convention was just to have an incrementing number at the end. SVCSQL1$ = DB engine service SVCSQL2$ = Agent service SVCSQL3$ = Full Text service...

Continue reading...

Get Certificate Details for SQL Encryption In-Transit Using PowerShell

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 make it mandatory for all connections to use this...

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

PASS is Gone

On January 15, 2021, PASS will cease to exist. https://www.pass.org/PASS-Blog/ArticleID/888/ArtMID/99177/preview/true I cannot tell you how much this disappoints me. PASS has been around for my entire database career, and now it is gone. I would not be where I am today without PASS. PASS events helped see what I was missing while siloed at my previous company. It took me about 10 years before I really got involved with PASS, and once I did, I was hooked. I could not believe how many people wanted to share their SQL experiences with the world. As a result, I too wanted to...

Continue reading...

Management Studio Find & Replace Using Regular Expressions

In my last Management Studio tip, I demonstrated how you can drag and drop column names to a query window. The only part I do not like about that is that all the column names are on a single line. I prefer to have on column name per line, and to quickly fix this we are going to use a Regular Expression (RegEx) within the Find and Replace dialog box. For those not familiar with RegEx, it allows you to search through text using a multitude of options. In our example, we are going to use RegEx to quickly find...

Continue reading...