Voice of the DBA

Tag: Redgate

Prompt in ADS

Ever since VS Code and Azure Data Studio came out, people have been asking for SQL Prompt to port over. I’ve been right there with you, though unsure of the value, given how UI dependent Prompt is for users.

Things have changed.

There’s an EAP program, with an early look at the formatting part of Prompt in ADS.

I’ve been using this for a few weeks, and it’s different, but it works well. All my styles from SSMS are ported and linked, and it’s a quick set of keystrokes, albeit different ones, to format code.

I’m curious to see what others think, so join the program if you want formatting in ADS and are willing to give some feedback.

December 13, 2019
Take the 2020 State of Database DevOps survey
It’s still 2019, but we’re looking forward to 2020 and wonder how your world has changed since our survey last year. There are a number of questions and this will probably take 20 minutes to complete, but you have a couple incentives:
- Completed surveys donate $1 to UNICEF
- You could win a 64GB iPod Air
You can take the survey here: https://www.surveymonkey.co.uk/r/FWCVCBJ

Feel free to pass this along to your friends as well.
December 2, 2019
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