Voice of the DBA

Tag: syndicated

Useful #SQLPrompt Tips

One of the tools that Redgate write is SQL Prompt. This might be my favorite product, and I’m constantly impressed by the improvements that the dev team releases. In fact, I’m also impressed by the rate at which they produce changes, with updates happening every week or two.

At this point, the product has a lot of features, some of which I’ve forgotten about, and a few that slipped by me. At SQL in the City streamed last year, I watched a short piece from Carly Meichen that taught me a couple things. I’ll write about a few of them, but take a look at this video and see if any of these will help you become more productive.

January 23, 2017
Installing dbatools on WS2012 R2

This is actually a simple, short post, but writing helps me remember things, and since I had to learn this, I decided to write something short.

I have a number of Windows Server 2012 R2 VMs. I need to get rid of these and upgrade to WS2016, since installing SQL 2016 is a pain on these VMs. However, for the time being, I’m stuck with them.

I was looking to try some of the dbatools modules. I popped open an elevated command prompt and

This is because that module isn’t in this version of PoSh. I checked, and I could see this is v4, and Install-Module was added in v5.

I had to go to the download page to see what my options were. One of these is to download the modules myself, but that seems like a pain. Instead, I used this command:

Invoke-Expression (Invoke-WebRequest -UseBasicParsing https://dbatools.io/in)

That worked great, and I had the modules installed.

Nothing much new in this post that isn’t in the dbatools download page, but I did learn a bit more about PoSh and its versions. Since I’ve gotten used to working on Windows 10, it’s good to remember that previous versions of the OS will have different capabilities.

In this case, the pain of using WS2012R2 means I’ll try to find time and upgrade to WS2016 as soon as I can.

January 20, 2017
What is a Database Master Key?

The encryption mechanisms in SQL Server are interesting, and they work well, but they are somewhat poorly named. I ran across a few people struggling to understand, so I decided to cover the concepts in a series of posts. This one looks at the Database Master Key (DMK).

Not the Master Database Master Key

This is one of the more poorly named objects in the SQL Server platform. Or perhaps the “master” database is the one that is not named well. In any case, the DMK has nothing to do with the master database. Instead, the DMK is the base encryption key inside of a database. This is the key that secures all other keys

There can be a DMK in each database that you have, including master. For some features, such as TDE, you must create a DMK in the master database. For others, you would create a DMK inside of the user database.

Protecting a DMK

By default the DMK is encrypted and protected by the Service Master Key (SMK), which is the key that protects the instance. This means when a database is opened and used, the service account can decrypt the SMK and use that key to decrypt the DMK. You can optionally also protect the DMK with a password.

Even more optionally, you can break the encryption link between the SMK and DMK. In this way, you could need a password on the DMK, which would have to be entered each time a user wanted to use a key protected by the DMK.

You need to ensure the password for the DMK is protected and available, as you will need it if you restore the database to another instance.

To use the DMK, an account needs the CONTROL permission on the database.

The DMK is a symmetric key. It is uses the AES_256 algorithm in SQL 2012+. Prior to that it was with Triple DES.

January 19, 2017
Using Out-GridView To Pick Parameters–#SQLNewBlogger

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

I was editing an article on PoSh and learned something cool. I can use Grid-View to surface an object in a pipeline and then change the items passed through to the next command.

Here’s how it works. Suppose I have a simple comment, like Get-SqlDatabase. I can run this and get a series of database. (command and output shown here).

Get-SqlDatabase –ServerInstance .\SQL2014

I get a bunch of data, but I don’t want all the items. For example, maybe I just want the “SimpleTalk” databases.

Get-SqlDatabase -ServerInstance .\SQL2014 | Where-Object { $_.Name -like ‘SimpleTalk*’}

I don’t want to edit this Where-Object clause all the time. I want something simple to easily fix this.

Enter Out-GridView

Instead of changing my filter, I can do this graphically. I can pipe my output to Out-Gridview, and I’ll get this.

All of my output data appears. No big deal, right? Is this useful? It sure is. Look at the filter item above. I can type in there, and my data is filtered.

How can I use this? Suppose my code is this:

PS SQLSERVER:\SQL\Plato\SQL2016\Databases> Get-SqlDatabase -ServerInstance .\SQL2014 | Out-GridView -PassThru | Backup-SqlDatabase -CompressionOption Default –Script

Here I’m getting a list of databases, passing them to Out-GridView, and then sending the results to Backup-SqlDatabase, which will return a script to back up all my databases.

I’ll run this, and then enter a filter in the grid.

I can now highlight all these rows (if that’s what I want).

This time I have an “OK” button in the lower right corner of the grid.

When I click “OK”, my filtered list is returned to the pipeline and send to the backup command.

This is a quick way to work with the parameters in your pipeline in an ad hoc way. Use Out-GridView to filter and select the rows you want to return to the rest of your script.

January 18, 2017