Voice of the DBA

Author: way0utwest

Opening .SQL Files in SSMS 2016 by Default #SQLNewBlogger

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

This is an easy one, but you’ll want to try this yourself.

As I’ve been trying to move off the various SSMS versions I have installed to the new, free, updated SSMS 2016, I’ve found that on every system (4 so far) that the default action for .SQL files is to open SSMS 2012 or SSMS 2014. That used to be OK, but it’s annoying. I have SSMS 2016 open (maybe just SSMS?) and waiting on the long load times for 2012/2014 SSMS, especially in demos, is annoying.

If I right click a .SQL file, I get the chance to choose, but what do I choose?

A little experimentation shows that I want SSMS, not SQL Server Management Studio. So let’s change that.

First, open the Control Panel. On Windows 10, this is the Win+I key combination. I find it hard to know where some things are located, but I like the Search in the upper right.

When I type “default,” I get a list of things. The fourth one down is the one I want to click.

This brings me a list of each file type and the app. Boy, there are a lot of types to go through. You’ll want to scroll down about two thirds of the way to find .SQL.

Once I find the file, I click the icon on the right, where it says SQL Server Management Studio. This brings up a “Choose an app” dialog as shown below. I click the SSMS choice.

Now when I double click a file, it just opens in SSMS (2016). Quickly.

SQLNewBlogger

A handy tip. This shows I saw a problem and fixed it. I’m sure some of you could write this in 10 minutes. Some might even built a .reg file or some other automation to show off skills that would help you fix this.

August 11, 2016
Keyboard Hardlines

A few years ago, I had a keyboard die. At the time, I needed something quickly and ended up with a Logitech wireless model that included a mouse. I’m not sure of which model, but I’ve ended up getting two or three more Logitech devices over the years. I think I go through a keyboard every 18-24 months, though the mice seem to last longer. For me, having devices available without cords is more important for the mouse than the keyboard, but since Logitech will bundle them, I get both at the same time. I’m currently with a K350 keyboard and an M510 mouse, both of which seem to hold up well.

However, I’m at home, usually working alone, so I haven’t been concerned much about security. In corporate environments, I remember playing jokes by moving someone’s mouse to my desk, and giving them a disconnected replacement, trying to mimic their movement. Or doing the same with a keyboard. That’s a great typing challenge if you ever try it.

I thought about those times when I read this piece on the security of wireless keyboards, or maybe the lack of security. Apparently a relatively simple device can intercept and replace, or just record, keystrokes made on a variety of keyboards. These devices use their own dongles, not a Bluetooth connection, and security is non-existent. Perhaps I’ll take one to the Redgate office this fall and see who’s actually vulnerable.

Actually, I think this is an issue, especially in large companies that may regularly hire consultants. Who knows what temporary workers might do with information they can gather by just sitting in proximity to a domain administrator. Or a DBA. We might be disclosing passwords to all our systems. Even malicious insiders may end up with access they shouldn’t have, including the ability to run queries or commands under another individual’s account.

Since keyloggers are cheap and simple, perhaps this seems like an unwarranted concern. However making the move to actually touch someone else’s machine, and add a device that could be noticed is much more dangerous than just sitting, or even standing with a tablet, by someone’s workspace. Even a simple conversation, asking an admin to look up information could result in a breach of security.

Fortunately this hack targets a few devices, and doesn’t work against Bluetooth devices. However, there are attacks against those, and against wireless access. I know some environments have very sensitive information, or even tight security regulation. I really hope we don’t find our administrators going back to hardlines for administrative access from specific machines, or even only using secured wired because we can’t strong wireless security for our devices.

Steve Jones

The Voice of the DBA Podcast

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

August 10, 2016
What’s a Database Pipeline?
One of the things I ran into a few years ago was the idea of a software pipeline. A set journey across machines and stages that your software takes. In modern, DevOps style development shops, this usually means:
- A development machine(s)
- A Version Control System (VCS)
- A CI/Build server/system
- An artifact repository
- Test server(s)/system(s)
- Production server(s)/system(s)
Usually software flows along this path as code is written, compiled, and moved from one stage to the next. You may have more or less stages in your environment.

For databases, this should be similar, with databases in
- development
- test/QA
- staging/pre-production
- production
You could also have other stages like integration, beta, user acceptance, etc. I’ve seen people with 7, 8, or 9 databases in their environment, all a part of their entire development and operational areas.

The database development pipeline is the set of databases that your code flows through. We’ve represented this with visually in DLM Dashboard as a flow, allowing up to 5 environments to be defined. You can see one of my pipelines in this image:

Keeping code flowing smoothly along this pipeline is the goal of any development effort. We know this at Redgate, and our mission is to build tools to help you do this efficiently.
August 10, 2016
Data Overload

Data rich and information poor. I think that describes most of the companies I’ve worked for. It’s a theme in this piece from Forbes, noting that many companies use only a fraction of the data they have to make decisions. Certainly I think that many of us that work as data professionals might note that most queries access a portion of our data, often the newest data, with older data sitting on storage systems, constantly powered on, but rarely included in a report.

Retrofitting archival processes into an existing application can be hard. Even more so when there is rarely queried data that clients want to be sure is still accessible somehow. The Stretch Database feature in SQL Server 2016 might help, but I bet it’s a long time before most of us have all our systems on SQL Server 2016 or later versions, let alone finding someone to pay for this feature.

There are real costs to keeping this data around, first and foremost of which is the stress for us as developers and DBAs as we try to tune queries the must run against larger and larger data sets. Actually, I’m only somewhat kidding. Management and clients might not care about this, but having to work against larger and larger can be stressful for technical professionals.

There are other, more concrete and measurable costs to keeping this data around. The cost of power and larger storage systems. With many companies keeping multiple copies of production systems around for different purposes, these can be noticeable costs. There’s also the time factor. If our systems run just 10% slower, that’s potentially 10% less business we can handle. Or maybe all that extra data means more annoyance and frustration from our customers due to slow systems.

We are going to get more and more data in our systems. While much of this data may be useful, if we’re overloaded, we may not be able to take advantage of the information. We also might get erroneous results if we don’t recognize that data gets old, and the value we might have from a row today might not exist in a few years. We should also realize that at times we have lots of data that isn’t useful at all for our organization.

I really would start thinking about the ways in which we can actually remove older data from our systems, with archival to cold systems, or even deletion if we’ve moved copies of data to other applications, such as data warehouse systems. Or maybe just deleting data we know isn’t going to provide any information. Above all, remember that warehouses will fill up at some point, unless you buy more and more (expensive) storage. Keeping all data accessible might not be the best decision for your organization.

Steve Jones

The Voice of the DBA Podcast

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

August 9, 2016