Voice of the DBA

Tag: tools

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
Using Better Tools

There are good tools available to help you work with your SQL Server database or build better applications. Microsoft has built some, most of which I think are basic and not great, but plenty of third parties have offered products in the Microsoft ecosystem that can help you build better systems. I work for a software vendor (Redgate Software). We build all sorts of tools to help you work with SQL Server. I enjoy working for Redgate because I think we build some great software that’s valuable, and I hope you check us out. Most of our our utilities cost money, but we have some cool, free tools, like SQL Search and DLM Dashboard.

I get that some organizations don’t have the budget for third party tools. That’s too bad, but there are some good tools out there, and I think many of us vendors do provide value in saving you time and effort in working with the Microsoft platform. If that’s worth the cost, you should consider using tools. At the very least, you should be aware of the free and paid extras out there and consider the ones that help you.

What I find strange is that some orgs don’t allow any third party tools, free or paid, because they don’t come from Microsoft. They may even disallow utilities like sp_Blitz, sp_whoisActive, and the dbatools project. What I don’t understand is why there is a blanket ban on software from companies other than Microsoft? How can you not take advantage of these tools? I get that many companies might not want a developer or DBA installing some random software on their system whenever they want. There are good reasons to not do that, but there are also good reasons to test and use actual code that is useful, even if produced by someone else.

What’s also interesting is that rarely find an issue with Ola’s backup scripts, so why would Minion Backup or these other tools be different? After all, most of these are open source, so you can see the code. It’s really no different than the code that an employee might write, howover many companies don’t have employees that can write this software. You can see the Powershell for dbatools on Github, so what’s the issue? You can test this code like you might test your own code. In fact, you should aways do this, but you can also count on other people having tested this code as well, perhaps in ways you wouldn’t think of exercising it. You might even be doing this, with employees cut and pasting code from one of these utilities on your system and passing it off as their own work.

Every company might need restrictions on code that goes to production systems. Certainly they should have ways to patch and update this code, especially as many of the issues found from open source software stem from organizations not applying patches. All code should be tested and verified, whether written by FTEs, contractors, purchased, or downloaded from the Internet. However, once you can verify code, there shouldn’t be restrictions on deploying code just because it wasn’t written by Microsoft. After all, many in the community might write better code than Microsoft, and often do.

Take advantage of the tools that are out there. Use free ones if you can and buy third party products if they give you value for your money. However, don’t just say that we can’t install something because it’s not on the install media. Your build process should be scripted, treating configuration as code, and add those useful (tested) tools to all your systems.

Steve Jones

The Voice of the DBA Podcast

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

January 4, 2018
KDiff3

As I write more code, especially database code, and I collaborate with others (or myself), I find that I have the need to merge code more and more. It’s inevitable, and since I work across a couple machines, I even find that I need to merge my own code at times.

One way to do this well is use a merge tool of some sort. KDiff3 is a popular one, that’s free, and it’s one that integrates nicely with SQL Source Control, which I also use for various tasks.

If you install KDiff3 (use “choco instal kdiff3”) then you get a basic tool that allows you to compare files. If you start it, you’ll get a simple interface that lets you select multiple files and view them together in one interface. If you don’t use version control, apart from making a mistake, you also probably manage code like this.

Or you might have this:

Either way creates problems. If you have multiple people doing this, then you have other issues.

KDiff gives you a nice interface to see the differences between files. As you can see below, I have the changes marked in each file.

I can edit the files directly, or choose to merge them together. If I do the merge, I’ll get a third window that has the merged code.

As you can see, if I click the merge area, I can choose the code from either of the original files.

On the right is a scroll bar that lets me know where in the file the differences are.

Software developers think nothing of these types of tools, but DBAs aren’t as easily used to using tools like this. I’d suggest that you download kdiff3 and give it a chance in trying to reconcile any code between team members or servers.

And start using Version Control. It’s easy and incredibly useful.

April 18, 2016
Basic SQLCMD–#SQLNewBlogger

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

I had the need to connect from the command line recently, and decided to make a quick post on using SQLCMD, as I had an issue.

SQLCMD is a command line utility that comes with SQL Server. I know many people don’t use command lines, but they are handy at times. I recently opened a command prompt.

I then typed SQLCMD. After a delay, I got this:

The issue here is that I don’t have a default instance on this machine. All of mine are named. I need to provide a –S parameter, with a server name (and possibly instance name).

I do that and I’m connected. By default, SQLCMD (and osql) try to use Windows Auth. The 1> indicates that the utility is ready for T-SQL queries. You need to know your language here as there’s no help.

I can enter code, and check my user name. I do this, and get a 2>. The end of a batch is indicated with “GO” and this will execute the batch. You can see how this works below:

I can use this to make my code easier to read. I can format code as I would in an editor, though be aware you can’t go back and edit previous lines.

If I enter go, I’ll get this:

Not so easy to read. I have to scroll up to even figure out what the display is:

As you can see, using SELECT *, or retrieving too many columns make results hard to read. You would to wise to pick only those columns you need to return.

To leave SQLCMD, you can type exit, which will return you to the command prompt.

This is a short look at SQLCMD. The older, osql, utility functions the same way, and both are good, lightweight ways to connect to your SQL Server instance.

April 14, 2016