WASD with SSMS Tools Pack 2.7.2 Review

I have been using Windows Azure SQL Database (WASD) for a little while now, and I think it would be a good idea to start a WASD Tools Review series on tools that work well for WASD. To kick off the series, I would like to explore top 8 features of SSMS Tools Pack 2.7.2 that have worked well for me in WASD development / administration (which also works well for on-premise SQL Server instances too).

As you may already know, SSMS Tools Pack was originally built for SQL Server 2005 and has since then been enhanced to keep up to date with later versions of SQL Server. If you have not used it before, download it from here and read a comprehensive review from Grant Fritchey.

All the options that are offered by SSMS Tools Pack 2.7.0 work for SQL Server, but not all of them are relevant for WASD instances. For example, the pre-configured “DB – DBCC SHOWCONTIG” custom script. At the time of writing, this option is not automatically checked by the tool for its validity to be run on WASD instances. Running this script would result in the following error:

Msg 40518, Level 16, State 1, Line 1
DBCC command 'SHOWCONTIG' is not supported in this version of SQL Server.

This may change in the future though – if you find that you would like to suggest some enhancements on any parts of the tool, I would highly encourage you to please provide the author (Mladen Prajdić) with some feedback.

A Quick Introduction

Once you installed SSMS Tools Pack add-in for SQL Server Management Studio, you can right click on items under the WASD instance on Object Explorer similar to below.

Options / configurations are usually accessible from the SSMS Tools menu.

Below are my top 8 favourite features (not in any particular order) that I use quite frequently or find most useful when working with WASD (equivalently for SQL Server instances too).

1. Create CRUD

This is useful for generating a stored procedure for each Create, Read, Update and Delete actions on data pertaining to each table in the database.

2. Generate Insert Statements

This is a very handy script to export data out as Insert statements.You can also choose select tables instead of the whole tables in the database.

Below is an excerpt of the generated Insert scripts:

3. Search Database Data

This is probably one of the most handy features out there. As a Consultant, sometimes I have to search a specific keyword and it could  be like looking for a needle in a haystack. This makes my job easier – although probably best not to be executed on a large database that does not currently perform well.

Fortunately, while executing the search, should you find it is taking too long, you can cancel the search.

Once the search completes, it returns the SELECT statement of where the keyword is found in the data. In the below example, I am looking for any record (in any table / view) that contains the word “Daenerys”.

4. Run Custom Scripts

Do you have custom scripts that you run regularly against a server? Well, you can do this for WASD server too, as long as it is relevant. Below is an example of Update Statistics script that has been pre-configured upon your SSMS Tools Pack installation.

It is possible to add / modify custom scripts and where they can be executed against. To do this, go to the toolbar and choose SSMS Tools > Run Custom Scripts > Options.

5. Window Connection Coloring

Using Window Connection Coloring, you can quickly find out what server the active script is connected to. As an example, the blue one below is configured to connect to my WASD instance, while the fuchsia is connected to my local database instance.

The Window Connection Coloring > Options on the SSMS Tools menu allows you to configure more colours for more database instances,

6. Show / Hide EPA

If you are into performance tuning, Show / Hide EPA (Execution Plan Analyzer) may be just for you. When Include Actual Execution Plan is enabled, you can further show (or hide) EPA. It gives a suggestion list on how you can improve the performance of your query. Please note that this is just a suggestion, and that you should perform some testing on how your proposed action(s) will effect the environment as a whole.

By the way, there are many more options within EPA. Make sure you right click on the EPA area to check them out on your free time.

7. SQL Snippets

I have to admit, I’m a typist. I love my keyboard. But this particular feature is just super cool. If you are with an application / web developer background, you are probably familiar with Sublime Text’s Code Snippet feature. Well, this works almost just like that.

When I put on my Data Analyst hat, in a day I could be typing a lot of “SELECT COUNT(*) FROM .. ” or “SELECT TOP 10  * FROM .. ” for quick investigations. (No, I don’t use SELECT * FROM in my stored procedures / any production related activities :D). SQL Snippets feature quickly becomes my favourite. It saves me time by just typing SSC or SST. You can configure more SQL Snippets too or change ones that have been preconfigured for you.

To access this options, simply go to SQL Snippets > Options from SSMS Tools menu.

8. Tab Sessions History

Every now and then your machine shuts down unexpectedly or SSMS stops functioning. Then you lose your queries. (Yes, they taught us to save those queries since we were young) or perhaps you accidentally closed the query without saving it thinking that you don’t need it again. Well, guess what? SSMS Tools Pack Tab Sessions History feature is your little insurance for you.

You can access it by simply pressing Ctrl+K Ctrl-T. Or simply go to SQL History > Tabs Sessions Management. From the SQL History menu, you can also configure the interval between the tab session saves.

How Much Does it Cost?

It depends on which version of SSMS you have installed. Prices may also change after this post’s publish date, so it would be wise for me to refer you to the simple Licensing page from the official site.

Wrap Up

Remember the saying “Work smarter not harder”? Well, SSMS Tools Pack can definitely helps you towards that. You still need to ensure that you customise it the way you work or want to work.

Further Reading

SSMS Tools Pack 2.0 by Grant Fritchey

SSMS Tools Pack (official site) by  Mladen Prajdić