Voice of the DBA

Category: Blog

Cleaning up the [NOT] NULL for Columns with SQL Prompt 10
SQL Prompt 10 is out and there are a few interesting things that have changed in the product. One of these is the Quick Fixes, inspired by some other IDEs that help developers learn to fix their code.

This post looks at one of the most common things developers ignore, and I’m guilt of this in many of my demos and code.

Defining Columns

When I create a table, I often will write some code like this:
```
CREATE TABLE dbo.EventSchedule

( EventScheduleKey INT IDENTITY(1,1)

, ScheduleName VARCHAR(100)

, StartTime DATETIME2

, EndTime DATETIME2

, Active BIT)

GO
```
This looks fine, and it works. However, it’s a SQL code smell, and it’s something that you should avoid. The problem with this code, is that I haven’t specified the nullability setting for the columns. I am assuming a default for my database, which has worked for me, but as I work with a more diverse set of customers, I know this isn’t a good practice.

SQL Prompt 10 actually catches this. If I screenshot the code, I can see the green squiggly that notes there’s an issue.

That’s a code analysis item that our software has flagged. If I hover over the area, I see this:

That’s good, but if I’m a junior developer, what do I do? What does this mean? One of the very nice things with SQL Prompt 10 is that we provide some guidance, similar to what Visual Studio does.

Look on the left of the image below. I’ve put the cursor on the line with the green squiggly. There’s a little yellow light bulb in the margin. This is a note that we provide a fix here. You can click the keyboard or the yellow icon.

When you do this, you will see that there a few choices. I can fix this (the wrench or spanner icon), get the details for this code issue (the eye icon) or see all issues in my code.

If I click the “fix” icon, I’ll see this:

NOT NULL has been added, but the NOT is highlighted. I can hit <enter> and this will stay there, or hit Backspace and remove the NOT. Either way, I get the code cleaned up.

At least some of it. You see the next green squiggly on the line below.

SQL Prompt continues to be one of the best productivity tools for developers working with SQL Server. If you have it, look for the quick fixes, and code smell squiggly lines. If you haven’t, download a trial today and see how it will help you become a better T-SQL developer.
November 24, 2019
Data Masker Reads Column Classificiations

One of the newer features in Data Masker for SQL Server is the ability to read column classifications and suggest rules to clean the data. I decided to give this a try and see how things work.

Importing Column Classifications

I created a new Masking Set, and I do like the new connection dialog. It works well makes it easy for me to focus my masking on a schema.

From here, I went to the Tables tab to check on the classifications. I didn’t see any, which felt slightly strange.

I went back to SSMS and double checked my work. I do have things classified.

After a quick reach out to the team, I remembered that a plan is an item you need to create and save. In this case, the feature is importing information from the database. I can then work with it in a plan. To get the data, I click the “Export/Import Plan” button at the bottom of the Tables tab.

This brings me a dialog of the plan details. I can choose to import a plan from a CSV file, or from the SSMS classifications. I’ll choose the latter.

Note: plans are specific to a controller. If I have two controllers, I need to import the plan into both (or all). When I click “Import”, the data is added to my plan. I can see that now there are four columns classified.

If I expand one of these nodes, like the Users table, I see that there are columns marked as sensitive and the comment includes the classification. That’s very handy for deciding how to mask the data.

Another welcome improvement is that I can change my plan sensitivity for all the columns in a table at once. In this case, I’ll right click the Sensitivity column for a table. I can then pick “Check”.

Once I do this, all columns are set to check for this table.

I can also multi-select columns (with the CTRL key held down) and then right click. In this case, I can pick three and then choose “sensitive”.

They’ll all be set, and I can see that I need to get to work.

Quick Rules

One other nice thing is that I can create a rule for multiple columns at once. If I have a few selected, I can right click and create a rule. I’ll choose Substitution rule here.

This brings me up a dialog with the columns already in my rule, and now I can pick the specific customizations I need for each.

It’s a little thing, but it greatly speeds up the process of masking data.

Give It A Try

Data Masker is part of SQL Provision, and it’s an amazing tool that allows me to mask data in almost any way I can think of to protect data in non production environments.

There are some other nice enhancements in this v6.3.13.x release. If you haven’t given Data Masker a try, do so today.

November 22, 2019
Containers, Kubernetes, and SQL Server
Abstract: Containers and Kubernetes have become buzzwords in the application development world, but how do these technologies fit with SQL Server? This session will discuss the basics of containers and Kubernetes and explain how we can use containers with databases in SQL Server. We will also briefly look at the future of SQL Server with Kubernetes support.

Level: 100

Slide Deck: ContainersKubernetesSQLServer.pptx

Code: Containers_Kubernetes_SQLServer.zip

Speaking Events:
November 21, 2019
Setting Certificate Backup Permissions for an Instance–#SQLNewBlogger

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

Recently I was testing some security change, and I made a backup of my certificate from one instance. I did this on a share that I’ll use to move the files to another instance. In this case, I had these files in the folder.

The problem is that by default, SQL Server locks down permissions, as it should. That means that the other instance couldn’t access the files, as its service account didn’t have permissions.

I fixed this by opening the security tab for the file. I had to do this for each, but I’ll show one here.

I clicked “Advanced” to get a more detailed view of permissions.

Again, I really need to approve this view of the security settings. Once I acknowledge the UAC dialog, I can see this:

What I needed to do here was add permissions for another service account. Clicking Add lets me select a principal.

For a local account, I need to give the “NT Service” prefix to my account, despite this not being shown as part of the service account settings.

When you click Check Names, this will shorten. If you enter this short version, it won’t work.

In my case, this account really just needs Read permissions.

Click OK, and I see it listed.

That’s it. Now my SQL2017 instance can access the backup and create the certificate.

SQLNewBlogger

This is something I’d expect most people working with SQL Server on Windows would be easily able to do, but showing some knowledge here gives confidence in your abilities.

This took longer to get screenshots than to write. You could easily do something similar.

November 20, 2019