Voice of the DBA

Author: way0utwest

Managing SQL Prompt Code Analysis Rules

I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try.

SQL Prompt includes new Code Analysis rules that help you write better code. This iteration of the rules in v9 are items that are highlighted with a green squiggly line. These are basic rules, and may not apply in your environment, and you may want to disable some of these. Here’s how.

Imagine you have a simple query like this one:

As you can see, there are green squiggly lines under some code. These are static Code Analysis warnings from SQL Prompt. If I put my cursor on the line, I’ll see the warning about the old style column alias:

In this case, I know this is rule ST02, and it’s not a warning I care about. I like the equals alias construct and don’t want to constantly see this, so I’ll disable this rule.

First, open the SQL Prompt menu. There are a few new items shown below. The “Enable Code Analysis” is below the other Prompt “Enable Suggestions”. There is also the “Manage Code Analysis Rules”. Select that item.

When the dialog opens, we see a number of rules broken into sections. Each of these is numbers, and has a prefix. Best Practices have BP prefixes, Deprecated items use DEP, etc.

Scroll down to the Style rules, which have ST prefixes. We can see that ST002 is the Old-style column alias. Uncheck this box and click “Save”.

Now the rule is disabled. If I wait a couple seconds, I’ll see the green lines disappear.

There are all sorts of items that SQL Prompt tracks as a part of Code Analysis. We’re looking to understand how you use these rules, what exceptions you need, and any additional rules you’d like to see added. Send us feedback at UserVoice.

Give SQL Prompt a try today and see how it can improve coding and feel free to let me know how you like this new feature.

January 29, 2018
No Default CONTEXT_INFO()

I was doing a little testing of Row-Level Security (RLS) for the stairway, and one of the ways that some people implement RLS is with CONTEXT_INFO().

I haven’t every really used CONTEXT_INFO in production, though it’s been around for some time. This is a way of setting some session information, as this data is stored for the connection. However, since it can be reset by the connection, it’s value isn’t necessarily trustworthy from a system perspective.

If you don’t set this with SET CONTEXT_INFO, then a NULL is stored there for on-premises systems. This makes sense, there’s no initialization there.

In Azure SQL Database, however, you get a GUID that’s a unique value. That’s good to know, as if you’re checking if this is NULL, you might assume you have something stored there, and since you need to CAST this back to the original datatype, this might cause issues.

This could be a good way to store data for a single session, but beware. If the session drops and reconnects, you’ll lose your data.

January 29, 2018
IoT Increases Efficiency

There is no shortage of articles about problems with IoT, especially with consumer based IoT products. In fact, many of the ideas and sales are gimmicky with dubious value for me, but I know what some people get excited that they can change their home thermostat from the car or have the fridge send them an inventory, or even have a tracker on their keys.

There certainly are some great implementation, especially in healthcare, but I think there is a lot of IoT that can really help in business, some of which will create new opportunity for us as data professionals. As the costs for sensors come down and their capabilities rise, I’m sure more people will come up with interesting ways that they can be used.

I spent quite a few years earlier in life working in the restaurant industry, and I’ve spent my whole life shopping in grocery stores for ingredients to cook with. I know margins are low in these businesses, and there is a lot of waste. When I ran across this article on using IoT in the food industry, I was skeptical that it would become commonplace, but I think I might be wrong. Keeping food fresh has a noticeable impact on profits and sales, and cheap sensors that can help measure data is important in all stages. From the supply chain to your fridge, if data can inform you of those foods that are nearing the end of their lifetime, perhaps you’d make different decisions in shipping, in promotions in restaurants, or even in the recipes you pick for tonight.

And I’m sure there are some of you that would love to chat about the source and age of the lettuce in your salad at a restaurant. That’s not for me, but IoT is going to change our lives in many ways and give us lots of data with which to work.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

January 29, 2018
Better Security with dbatools

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

One of the things that I see many junior SQL Server people struggle with (or forget) is the need to get logins onto multiple servers. This is often in situations where some sort of DR or HA technology is being used between instances. An admin will set up a second server, add logins, and forget about the entire system.

Until something fails. Then their failover works perfectly, or mostly, or good enough, and they move on with their day. A few days later

The cmdlet, Sync-DbaSqlLoginPermissions, is designed to help solve this issue. I certainly could use this to quickly sync logins (SQL or Windows) between two instances, but it’s great for an automated, regular sync between two HA or DR instances. If you set this up as a job, it won’t necessarily catch every login if servers fail between the syncs and a login is added, but this will give you some added protection over a long term as you make changes on one system.

This is a way to solve some cross instance issues that are hard in T-SQL, but simple in PoSh. Kudos to the dbatools team for this cmdlet.

January 26, 2018