Voice of the DBA

Tag: Redgate

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
The Redgate Way

Recently Matt Hilbert, from Redgate Software, wrote a piece on our blog about our journey to DevOps. It’s a great read, summing up some of the things that we’ve learned in our journey across the last decade. Matt is a great writer, and it’s worth a few minutes of your time to check it out and think about all the things that we’ve been through.

I’ve known some people at Redgate for 18+ years, and I’ve worked there for 12, so I’ve had the chance to see quite a few changes. When I started, teams worked for long periods of times, in what is really a waterfall methodology. They went through substantial phases of development, testing, and beta releases, with Brad McGehee and myself having plenty of time to learn a product and then know it would be stable for a year or so.

That slowly started to change, as Matt describes, with teams moving to new methods of building software, experimenting and learning. The Prompt team was one of the most ambitious, working in pair programming and finding ways to release almost on demand. Over time, other teams caught up and built some amazing processes. We even had two teams working on some products, with alternating two week sprint cycles to allow them to release every week. That was an impressive coordination of software development teams.

Even today, I’m constantly impressed by teams. They aren’t all always rock stars, but they often exceed my expectations. I will see some slowdowns when teams change their people, process, or tooling, but then they will leap forward. The Data Masker team has been impressive lately, with some fantastic productivity improvements being added to the software. I apologized recently for not taking the Data Catalog team seriously for almost a year, but they have done more than I ever imagined with that tool in the last six months.

We do continue to improve our software development skills at Redgate. We do release often, but really, we try to also improve the quality of our software, improve the skills of our developers, while working to retain talented individuals and help them enjoy their chosen career. We still produce bugs, we never get everything done as fast as sales, marketing, or even me, would like, but I do think that the last ten years has been an incredible growth as set of software development teams. Now our challenge is more closely aligning all teams to work in a loosely coupled, but tightly integrated fashion.

DevOps is really a better way to build software for most organizations and teams. It often doesn’t change a lot about the actual code we write, but it does get developers, infrastructure staff, and management to rethink how we work, especially how we work together. That’s the hardest part to get through to many customers. This isn’t an install-a-tool-and-things-are-great system. Tools do help, but your attitude, your focus, and your willingness to work as a team are more important.

Read about our journey. We’ve taken 1,000 steps, but there is more for us to learn, change, and implement as we move forward. Think about how you would want to change things at your company, and maybe pass this along to your manager. It’s not easy, and it might not be quick, but it’s an incredible journey. It’s also very much worth the time and effort you put into it.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

November 21, 2019
Getting Started with SQL Prompt 10

This week we released SQL Prompt 10, which is an exciting milestone for us. I remember when I discovered this little gem for Database Weekly and sent it over to Redgate as something they might want to buy. They did and have made dramatic and amazing improvements over the last decade+.

I’ll have a few notes on new features, some of which have been leaking out in v9 already. We’ve started to get away from holding all features until a new release and slowly trickling them out, sometimes as experimental feature flagged items.

There is a new Welcome window,which I think is a nicer way to show off a feature than the tool tips. I can see a few things at a glance, have some links, and get this back from the SQL Prompt | Help menu at any time.

I think SQL Prompt is the best intellisense tool for SQL Server, and many people agree. If you’ve never tried it, you can get an eval today and see what you think. If you already use it, know that we’re still investing in the tool and driving it forward for the new data platform on SQL Server 2019.

November 15, 2019