My SQL Server Development Toolbox

Having the right tools for the job makes the work much more efficient. However, for those just starting out in SQL Server land, it may not be clear which tools are useful or appropriate for a given task. I regularly field questions about which tools I use on my development machines, and why. To that end, I’ve compiled a brief list of what I keep in my SQL Server development toolbox.

SQL Server Development Toolbox

Over the years I’ve built a list of tools that I favor when building data and business intelligence solutions. Below I’ve briefly described some of my favorites.

VMWare Workstation

I don’t typically install my development tools on my physical workstation. For local development, I use VMWare Workstation to create a virtual machine for each development/test scenario. For client-specific dev and test work, I’ll spin up a virtual machine just for that client. I keep templates for each recent major version of SQL Server (2008 and up) that allow me to spin up a clone in a matter of minutes.

Azure Virtual Machines

VMWare Workstation works reasonably well for what I do, but I’m starting to move my virtual machines to the cloud as Azure VMs. Although there are recurring costs with running a cloud-based VM, this configuration gives me more options for scalability, and doesn’t require me to carry around dozens of VMs on my laptop. I expect that I’ll have migrated most if not all of my development VMs to the cloud in a couple of years.

SQL Server Developer Edition

Even if the bulk of the database development will take place on a different SQL Server instance, I almost always install the Developer Edition of SQL Server on my development workstation. For small testing and development tasks that do not require “real” data, having a local copy of SQL Server is very useful.

During the installation process, here are a few options that I typically set:

• I install the BI services (SSIS, SSAS, SSRS, DQS, or MDS) that I’ll be using for the project or task. If it’s a generic BI development workstation, I install all of these.
• Skip the documentation components. I can Bingoogle what I need.
• Include client tools connectivity and backward compatibility
• I add my Windows login account to the administrators group on the database engine, and Analysis Services (if installed)

Below is a screenshot of the typical options I install for a generic workstation. For a project- or task-specific VM, I install only those elements I expect to need.

SQL Server Management Studio

This one is a no-brainer; nearly every data professional who interacts with SQL Server uses SQL Server Management Studio (SSMS) to for DDL and most DML operations. Although you can get away with not installing SSMS on the server itself (and some would say this is a good practice anyway), having SSMS installed on your workstation is essential.

Depending on which version of SQL Server you’re installing, this may be an option to include with the base installation of the SQL Server engine. Starting with SQL Server 2016, though, the installation of SSMS is decoupled from the installation of the engine. You can click through to the SSMS bits from the SQL Server install UI as highlighted below, but it’s a separate download weighing in at 784Mb for the current version as of this post.

Once SSMS is installed, these are a few configuration preferences that I set.

Uncheck Prevent saving changes that require table re-creation. Among one of the more annoying defaults is that SSMS disallows any metadata change initiated from the UI designer which requires the table to be dropped and recreated. To do this, I navigate to Tools –> Options –> Designers and uncheck the box labeled Prevent saving changes that require table re-creation.

Use the Windows beep to announce query completion. This handy feature will trigger the Windows default beep sound when a query completes in SSMS. To enable this, I go to Tools –> Options –> Query Results and check the box beside Play the Windows default beep when a query batch completes. Occasionally I’ll get creative and will also change the Windows beep sound to something fun, usually a sound from Star Wars or a voice clip from Napoleon Dynamite.

Change the Results To Text default. In Tools –> Options –> Query Results –> SQL Server –> Results to Text, I change the output format to Custom delimiter, set the Custom delimiter to a pipe symbol (|), and update the setting for Maximum number of characters displayed in each column to 8192 (the maximum allowed in this setting). These are the most common settings I use when outputting data to text, either to the query message window or when writing to a file.

Turn on line numbers. It’s a federal law that every code editor must show line numbers (okay, maybe it’s not a federal law, but it should be). To turn on line numbers in SQL files, I go to Tools –> Options –> Text Editor –> Transact-SQL and check the box next to Line numbers.

Use connection colors for known servers. A useful but underutilized attribute of SSMS is that it can apply a color ribbon to known SQL Server instances. To enable this, I go to the Connection window, enter the server name, switch to the Connection Properties tab, and select a custom color for that SQL instance. From then on, opening a query window will display the custom color I set for that instance. I like using red for production, yellow for UAT, green for test, etc.

SQL Server Data Tools

To develop code for projects using SQL Server Integration Services, Reporting Services, or Analysis Services, I install SQL Server Data Tools (SSDT). Just like the latest version of SSMS, SSDT is a separate download and install, but the latter has been that way since version 2012.

First, let’s deal with the confusion about what tool(s) you need to make SSDT work on your machine. As much as I enjoy working with Microsoft and their data products, the company has not done a good job of branding, rebranding, and staggering releases on the BI development environment. Below is a brief summary of the state of the tools. Hold on tightly, because this alphabet soup gets confusing in a hurry:

• If you are developing BI assets for SQL Server 2005 or 2008, you need the Business Intelligence Development Studio (SSDT) version specific to that version of SQL Server. BIDS is part of the regular SQL Server installation, and can be installed during the installation of the engine or on its own using the same media.
• If you are developing BI solutions for SQL Server 2012, you can use either a) SQL Server Data Tools (SSDT) 2010, or b) SQL Server Data Tools – Business Intelligence (SSDT-BI) 2012. For the latter, make sure you get SSDT-BI, not SSDT! Even though the 2010 version of SSDT is appropriate for BI development, the 2012 version of SSDT does not include the templates for SSIS, SSAS, or SSRS.
• For BI solutions that will be deployed to SQL Server 2014, you’ll need SSDT-BI 2013. Again, don’t mistakenly install the similarly-named SSDT 2013, because that version alone will not contain the templates for BI development.
• For BI development on SQL Server 2016, you’ll use SSDT 2015. Yes, another branding change here, as Microsoft reverts back to the simple SSDT name (just like in SSDT 2010!) for business intelligence development. Here’s the good news with this change, however: SSDT 2015 has a project-level version selection that lets you target prior versions of SQL Server. With that change, you can use SSDT to develop SSIS, SSAS, and SSRS projects as far back as SQL Server 2012 (even further back with SSRS). With any luck, you’ll no longer need multiple versions of SSDT, instead using SSDT 2015 for all of your BI development.
• For all of these tools (BIDS, SSDT, SSDT-BI, and SSDT redux), remember that they all run in the Visual Studio shell. However, you don’t need to install Visual Studio separately; installing BIDS or SSDT(-BI) will automatically install the necessary Visual Studio shell in addition to those BI templates for SSxS development. Also keep in mind that installing Visual Studio alone (even the high-end commercial versions) will not install the BI templates.

This may seem like an unnecessary sidetrack into the history of Microsoft BI development tools, but there are still quite a few shops running 2012, 2008, and even 2005 business intelligence projects. Getting the right version is critical, as is understanding the tooling needs when the time comes to upgrade.

For my toolbox, here’s what I use: When I am developing BI solutions for SQL Server 2012 or newer, I use SSDT 2015. Most of the time, I also install Visual Studio 2015 Community Edition as well, which allows me to create C# libraries in addition to BI projects. Visual Studio 2015 Community Edition also includes the Team Explorer tools for using source control (more on that in the next section). When building solutions for deployment to SQL Server 2005 or 2008, I simply install the BIDS tools specific to that version.

Team Explorer Plug-In

I promise, this is the last rant about the development tools: As hard as it is to believe, SSDT does not include the plug-in necessary to connect to Microsoft’s Team Foundation Server. This requires yet another download and install. This isn’t always required, as some organizations don’t use TFS for source control, or in some cases – gasp! – don’t use source control at all. I usually go ahead and install this anyway – it’s a small install, and it makes it easier to demonstrate TFS to clients and other users who are not yet using any form of source control.

BIDS Helper

BIDS Helper is a great suite of small add-ins for SSIS, SSAS, and SSRS projects. It runs purely on the client (no server deployment needed), and has been around long enough that it’s quite stable. BIDS Helper for BIDS/SSDT/SSDT-BI version up to and including 2014 can be found on Codeplex (hopefully they’ll be migrating that code, since Codeplex is shutting down soon), and newer versions can be found in the Visual Studio Gallery.

BimlExpress

I’m a big fan of using Biml for SSIS development. The simplest tool to use for that is BimlExpress, an SSDT plug-in that allows you to create and edit Biml files directly. Biml Express is a free tool, and it makes composing Biml code much easier than in older versions of the Biml compiler that ran only in BIDS Helper.

Notepad++

For any data professional, having a good text editor is critical. Notepad++ has been my go-to free tool for years. It handles large-ish files relatively easily, and can be configured to show unprintable characters such as carriage returns and line feeds.

7Zip

If you need to zip or unzip files, 7Zip is a great free tool for that. It even has a command-line version that you can use for automation, like I described in this post about zipping files using SSIS.

WinSCP

For secure transportation of files (such as SFTP or FTP-S), WinSCP is both simple and free. It can be run from the UI or via command-line, the latter of which I demonstrated while showing how to use WinSCP to access secure FTP servers.

GnuPG

To decrypt or encrypt files, GnuPG is my go-to tool. Like many of these other utilities, it can be run in its own UI or through an automated process, making it easy to integrate into SSIS packages and other processes.

Red Gate Toolbelt

I’ve been using the Red Gate tools for a decade or so – particularly SQL Prompt, SQL Compare, and SQL Data Compare – and find these incredibly valuable for database development and BI projects. Full disclosure: I get a complimentary license of the Red Gate tools for my involvement in community activities. However, I’d be singing the praises of these products even if I didn’t get to use them for free.

Conclusion

Every good craftsperson has a cache of reliable tools, and practitioners of data management and business intelligence are no different. These are the items I keep in my SQL Server development toolbox. Hopefully you’ll find some of these as useful as I do.