Voice of the DBA

Tag: SQLNewBlogger

Just Getting Specific Files in PoSh–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Just a quick one today, but a tip I hope I remember. While working on a script, I wanted to just get backup files in PoSh. I tried this:
```
Get-ChildItem -Path $BackupPath -File -Include *.bak
```
That got me this:

Nothing.

Why not? Apparently, you need the –Recurse parameter, even though there are no subdirectories in this folder. A bug, IMHO, but who knows. In any case, adding the parameter makes things work. Thanks, SO.

SQLNewBlogger

This was a quick 5 minute thing. After researching and testing for 10 minutes, and finding the solution, I wrote this up.

You could do that with anything you get working.
October 9, 2019
Shutting Off PowerShell Warnings–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Not really a SQL Server post, but I ran into this minor issue. While running a script, I saw this:

The warnings aren’t a big deal, and in fact, have no bearing on my script. At least, not on this script. Since I needed to give this to a customer as a PoC item, I didn’t want any weird warnings. As a result, I needed to suppress these. A little intellisense in VS Code gave me a hint, but what value to use for the parameter?

A quick search brought me to Server Fault, where I saw the value. I added the SilentlyContinue to my script and messages suppressed.

SQLNewBlogger

This is a helpful tip for me, and I think I showed you how I found an issue, researched, and solved it. It’s good to give a potential employer some confidence that you can work through issues and find answers.

This took me less than 10 minutes. I bet you could do the same thing in the same time. Write about how you solve a small issue and drop it on your blog.

October 2, 2019
Restoring an Expired Certificate–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One common task that you might need to handle if you work with encryption is dealing with a certificate. Whether this is for TDE, SSL, or something else, you will want to ensure that you know how to restore a certificate.

I’ve written about this before (restoring a certificate), but what about restoring an expired certificate? If you forget to replace one in TDE, does it still work?

Tl;dr yes.

Validating a Restore

I backed up an certificate from one instance that had expired. I used this in a customer demo a few years ago, and it was on my system. Here’s what I saw.

I ran the backup script shown to get the expired backup of the cert.

Next, I connected to a new instance and ran this.
```
CREATE CERTIFICATE FinanceCert

FROM FILE = N'C:\SQL\FinanceCert.cer'

WITH PRIVATE KEY

(
     FILE = N'c:\SQL\FinanceCert.pvk',
     DECRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You'

);
```
This completed, but I get the warning that this cert is expired. It’s a warning. SQL Server will still use the certificate to decrypt anything necessary, like a DEK for TDE.

This is a good reason to keep an eye on certificates, as someone might be worried about expired certs. They work, but some people (auditors) do not like to see these in use.

To change the certificate used, see Key Rotation in TDE.

SQLNewBlogger

Anything DR related is a good skill to showcase. Blog about your practicing different skills. The writing will cement the skills further in your mind and employers will appreciate you showing the way you handle things.
September 2, 2019
Finding SQL Configuration Manager in Windows 10–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I went to check a network protocol setting for SQL Server the other day on my (newish laptop) and was disappointed.

This is Windows 10 and on this machine, I’d installed SQL Server 2014, 2016, and 2017. I thought that at least SQL Server 2014 had the SQL Server Configuration Manager installed, but it appears not. I know that this has been a tool that sometimes gets hidden in recent versions, but I was sure I’d seen it here.

Either I’m wrong or Windows 10 has changed.

In any case, the Computer Management MMC plugin has it. You can run this in a couple ways. First, hit the Start menu and type “Computer Man”. You’ll get something like this and can run this:

The other choice is to his Win+R (run) and type “compmgmt.msc”. Both will get you here:

If you now expand the Services and Applications, you’ll see the SQL Server Configuration Manager and the various items underneath it. For me, the top one (most recent?) was the SQL Server 2017 version. The others were below as other snap-ins.

The good thing about this is I can also manage local users and see the local logs, things I sometimes need when configurating SQL Server.

SQLNewBlogger

An easy post that solves a common problem, and shows I know some tips and tricks. How would you rewrite this post? You could show this knowledge with a quick 10 minutes of your time.

August 28, 2019