Voice of the DBA

Author: way0utwest

Adding the KeyMap Extension in SQL Operations Studio

I’m playing with SQL Operations Studio a bit to get a feel for what works and what doesn’t. So far, I don’t love it over SSMS, but one of the main reasons is that many of my common keyboard shortcuts don’t work.

Then I saw this:

You can download the extension from here, but you can’t click it to install. You need to follow this process.

First, click the File menu and select “Install Extension from VSIX Package”.

Then find your download.

In a few seconds, you should see the message at the top of SOS.

Restart, and it should load.

As of when I installed it, only a few of the mappings were added. If you click the Extensions icon on the left (lowest box looking one) and then the extension, details pop in the pane. Click the Contributions link to see the commands.

So far things seem to work, but I need to add a couple more for myself.

Of course, until SQL Prompt gets ported, not sure I want to spend a lot of time in this tool.

April 12, 2018
Trekking Through Formatting Forest on the #SQLPrompt Treasure Map

I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I wrote about Code Snippet Cove recently, and this post continues to move across the map.

Formatting Forest

Walking through a forest can be daunting. Living in Colorado, I’ve had the chance to hike and explore the mountains of the state. If you’re near the top and can view landmarks, it seems easy. Walking through some forests, when you can’t see a peak, you get a little worried about which direction to go without a trail. It’s serious, as people die every year in my state because they get lost in the woods.

Writing T-SQL isn’t a life or death endeavor, but it can be frustrating when we see code that’s formatted in a way that we don’t expect. I’ve seen truly ugly T-SQL code (from my perspective) in the SQLServerCentral forums, and I often need to copy and paste it into SSMS to get a sense of what’s happening. I used to reformat by hand, but CTRL+K,Y is an ingrained SQL Prompt habit that lets me get code into a style that I can work with.

The part of the treasure map that talks about formatting is long and detailed, and there are lots of resources that can help you format the code better. Likely, however, you’ll want to open the formatting dialog (shown below) and play with settings to get what you want.

One of the best features, at least for me, is that I can quickly switch styles from my own to any other, such as a corporate standard. With a right click, I can choose another style, CTRL+K,Y to get to some other format, and then reverse that later.

I do this before committing to Version Control, as you should. If you haven’t tried SQL Prompt formatting, get an eval and give it a try. It’s amazing. This alone saves me a ton of time when writing code.

In the next post, I’ll continue on to the Caves of Code Analysis.

April 11, 2018
IoT Success

After quite a bit of press the last few years, the IoT crazy has slowed a bit. I suspect this is because many of the consumer type IoT devices, such as refrigerators and washing machines, haven’t proven to be as useful or exciting as their developers originally thought. With all of the security issues that we’ve seen as well, having more interconnected “things” doesn’t seem to be so desirable for many individuals.

In the industrial world, IoT is still thriving and growing, as businesses see just how inefficient they have been in the past. The way that many people have gotten business done is with lots of hard work and good luck along the way. Certainly farmers and ranchers have counted on instincts and good weather more often than science. Those items still matter, but smaller sensors and connected IoT devices can help reduce some of the risk and ensure better yields from your efforts.

There’s an example of this in a short article about how various IoT devices are being used to help shrimp farmers in Columbia. Some of the uses, such as cameras to detect theft, are obvious, but the additional use of computing power and logic to reduce the need for a human to stare at a screen, vastly increases the chances of knowing when a theft is about to occur. Others sensors are used to check weather and actually monitor the shrimp in their environment, tasks which weren’t feasible for individual companies in the past to perform cost effectively.

The key here really is being able to gather more data and process it efficiently. Lower hardware costs, better algorithms and a variety of programmable sensors enable the capture and examination of this data in a way that can be customized for individual companies. Data can be very powerful for most organizations that perform actions in the analog world. IoT is helping them learn to better understand data they they wouldn’t previously have been able to capture.

There are certainly still potential security problems with off the shelf sensors and grid connections. Anyone that wants to start to use these types of devices needs to understand the networking and potentially limit access to your main databases and systems, perhaps with tightly controlled ETL processes to funnel the data to staging areas. If you can do that, however, there are going to be opportunities to increase the efficiency of your operations, with plenty of data analysis and visualization jobs for the data professionals.

Steve Jones

The Voice of the DBA Podcast

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

April 11, 2018
T-SQL Tuesday #101–Tools
This month’s T-SQL Tuesday is brought to you by Jens Vestergaard. The invitation for #101 is for essential SQL Server tools. Since I work for a tool vendor, you can guess that I’m going to be shaded that way.

This is the monthly blog party on the second Tuesday of the month. You can participate by writing your blog, linking to the invite and leaving a comment there (or a pingback). This is the creation of Adam Machanic, and if you want to host, drop him a note.

My Essential SQL Server Tools

There are really three main tools I’d say that are needed for SQL Server work. At least, these are the ones I need.
1. SSMS – It’s free, and it’s the main tool for connecting to SQL Server. You might like VS Code or some other editor, especially on other platforms, but really SSMS is required for most people.
2. SQL Prompt – It’s expensive for some, but after using this for years, I think the the native intellisense hurts more than helps. Just formatting alone make this critical for me. I’m moving through quicksand, mixed with molasses, when I don’t have Prompt installed.
3. Git – Another free one, but an important one. As we write code, SQL Server will only keep the latest version. Much of what we do means that we need to understand where we came from with code. We do have integrations at Redgate for SQL code, but no matter what, just having a record of work is important, especially with multiple people. I love git, but if you want SVN or something else, use that. Just use something.
These are the main ones. Making these the main ones doesn’t mean that others aren’t important, but it just means these are the top of the list. After these, you can add in others. Here are the ones that I think are important for getting things done in a modern, multi-environment world.

SQL Search – From Redgate, but useful for finding things in a large environment. Way better than browsing.

Plan Explorer – I think SQL Monitor has a nice plan tool, but for those of you writing code and wanting to test it, this is way better than the native plan viewer.

DLM Dashboard – Free, and let’s you see and track those changes in production. Find out when code is being released and you aren’t aware of it.

Sublime Text – I tend to do some work in other languages, and in text, and having a quick, easy to use editor is nice. I think Sublime is great and worth the small fee.

Password Safe – Having long, secure passwords is important. I don’t try to remember them anymore, and I do want separate ones for every site. I like this open source tool, and have ports on my mobile devices.

Dropbox – Critical for me, since I use files on multiple machines. I keep copies of my presentations, password safe files, and more in sync across devices with Dropbox. Plus I can scan in docs from my phone if I need to send and share them.

dbatools – This is an amazing project. I hated working with the sqlps module and really avoided PoSh for many things before dbatools. Give this module a try and you may learn to love PowerShell.

SQLServerCentral – I’m biased, for sure. However, everyone could use a few things that we provide. A community, where you can get inspired and learn things (daily articles and editorial). A way to ask questions and get answers (our forums), and a place to keep scripts that are helpful for you (Scripts and Briefcase). These are important to rounding out your career, beyond just your current job.

There are plenty more choices, and certainly lots of tools within SQL Server. You should know about Extended Events, or Query Store (or Open Query Store) and more. Learn to use the resources you have, practice and test with them, and if you need a paid tool, don’t be afraid of asking. If the tool provides an ROI, it’s worth it. If it doesn’t, tell the vendor why.
April 10, 2018