Today’s post is an unabashedly subjective plug for a bunch of unrelated tools I have come to rely on and love. I’m not really a “tools guy,” so there are no doubt better and worse tools for these tasks out there. I’m not affiliated with any of the vendors or authors. There’s no scientific method here - I just like these, and use them daily, and you might too. Sole criteria for inclusion: if I lost one of these, I would be both sad and less productive.
T-SQL Code Formatter: SQL Prompt Pro
Number one on my list is a good T-SQL code formatter. I really like clean, well-formatted code that is legible. But I’m not fanatical about the details, and I really don’t enjoy spending a lot of time putting spaces and line breaks into some other person’s code. What’s worse - a lot of what I do involves troubleshooting code from unknown sources, mostly ISV’s, that I scrape from performance DMV queries, or Profiler, or existing stored procedures. Often these come through as one long line of unformatted SQL. My best friend: Red Gate SQL Prompt Pro > Format SQL.
Or, as I like to call it, “CTRL+ V A K Y, Shazzam.”
I think I have saved more time on code formatting with this tool than all the time I’ve spent sitting at red lights in my life to date, combined. A bargain at twice the price.
T-SQL Code HTML-ifier / Prettifier: Paste from Visual Studio
I blog occasionally, and as a natural extension of the first item, I wanted a way to get my wonderfully formatted SQL code onto the web, in a presentable form, with little manual labor. For this my online friends pointed me to the combination Windows Live Writer and Paste from Visual Studio (aka VSPaste). Management Studio and Visual Studio use essentially the same RTF format in the clipboard, as far as I can tell, so you can use this little gem to copy T-SQL and paste it straight into Live Writer as nicely-formatted, color-coded HTML. Super handy. Works for any Visual Studio code, too.
Autocompl<tab>: SQL Prompt
Intellisense, auto completion, however it’s branded, I gotta have it. I work in a mixed 2000 (Ack! I know.), 2005 and 2008 environment, so again my buddy is Red Gate SQL Prompt. It seems to coexist happily with Management Studio’s built in, 2008 and higher, Intellisense, but works with older versions of SQL Server too. Everyone that has ever looked over my shoulder while I use it has said, flatly, “I want that. Where do I sign.”
Snippets: SQL Prompt Again
Being lazy, in the coder sense, I love tab-auto completion for custom code snippets too. For this - I fear I am repeating myself - Red Gate SQL Prompt wins again. Customizable snippets of code are very easy to set up and can be shared with teammates. Example: to restore a Litespeed Backup using T-SQL, in the past I would constantly trip over the syntax and spend time looking up the options. I made a snippet, and now I can type lsr<tab>, and, shazzam, I get:
@database = N'' /* 'database_name' | @database_name_var */
, @filename = N'' /* 'backup_file_name' | @backup_file_name_var */
--, @with = N'MOVE ''logicalFile1'' to ''physicalFile1'' '
--, @with = N'MOVE ''logicalFile2'' to ''physicalFile2'' '
--, @with = 'REPLACE'
--, @with = 'NORECOVERY'
--, @with = '' /* 'additional_with_parameters' | @additional_with_parameters_var */
(By the way, I just pasted that code straight in here with Live Writer > VSPaste. I love that!)
T-SQL Execution History: SSMS Tools Pack
Our shop is also cross-platform, so I sit side by side and cross-train with Oracle DBAs. They have Toad, obviously, and one of the best Toad features is that it retains a history of the queries you execute. It doesn’t seem essential - until you’ve tried it for a while, and then you wonder how you ever lived without it. Enter Mladen Prajdić’s SSMS Tools Pack. This is a bundle of useful add-ons for Management Studio, including a Toad-like query history. There’s some overlap in features with SQL Prompt. (Because I have both, I’ve disabled a couple of the SSMS Tools Pack functions that are redundant with SQL Prompt, but Mr. Prajdić makes this very easy with a menu of options for each add-on feature.) On the other hand, that might be great if you can’t afford SQL Prompt.
Plan Analysis: sp_whoisactive + SQL Sentry Plan Explorer
I live in the land of Buy Don’t Build, and in that far country work often looks like, “Hey, there’s a performance issue, on this system you’ve never seen before, that comes from a totally unresponsive vendor. Can you fix that?” I like a challenge, but my best wing-men in this scenario are sp_whoisactive from Adam Machanic and SQL Sentry Plan Explorer. These two, together, allow me to quickly and easily get the plan of a query from the procedure cache on a server, and see what it’s up to. Phenomenal. I’ve even been known to say, “this is what Management Studio ought to do out of the box.” Intellectual property rights aside, I do sometimes wish that the plan viewer and activity monitor in SSMS literally were like these two free tools. Fantastic.
Automated Monitoring and Baselining: Pick one
I have posted before about why I prefer to purchase, rather than build from scratch, a monitoring solution. I am currently using System Center Operations Manager and it’s SQL Server Management Pack, and I like it a lot. We’ve had to do some customization, but it’s a good platform to start from and I found it had 80% of what I was looking for. It is rather complex, and some commitment is required to set it up, but once installed it’s a good way to get to a “single pane of glass,” where everyone can see the same alerts and performance data. I have also used Quest Spotlight on SQL Server Enterprise (“Enterprise” is vital there, it’s not the little free version) and Idera SQL Diagnostic Manager, and those two tools were also very good. They tend to be stronger in terms of depth with SQL Server, and easier to deploy, but without the scalability or reach of SCOM or the ability to easily share a platform with other applications and OS monitoring. In addition, the SQL Sentry and Confio tools look really great to me, but I have not used them in production. In summary, though, I want something watching my servers while I sleep, and recording their performance stats all the time. Vital!
Schema Search: Red Gate SQL Search
It is possible to search through the metadata in a new database with queries, but honestly why bother when there’s Red Gate SQL Search. I must sound like I work for Red Gate at this point, but I don’t, I promise. I use this tool all the time to examine those ISV databases that are new to me, where I need to locate columns or tables or code in a hurry.
Remote Desktop: Remote Desktop Connection Manager
For those tens or hundreds of machines you might need to remote into with RDP, especially all at once, there is Remote Desktop Connection Manager, created within MS, released as a publicly available download. Most days I have this open and use it all day long. Sweetness: it can store not only groups of servers, but also encrypted credentials, allowing one to log into or out of multiple servers all at the same time, even with differing credentials. I love this one.
Cloud Script Clippage: Simplenote
This one is a little harder to explain. Like a lot of DBA’s I have a pile of T-SQL scripts that act like my SQL Server Leatherman, or my Swiss-QL Army knife. These are all little code snippets that are too long to memorize or type, but generally the same from one project to the next – like that “just right” DMV query that shows me exactly what I need. I have kept these in a folder or on a thumb drive before, and I know many people use something like Dropbox to keep this type of stuff on the web, which is a great solution. If you work in a security-sensitive location, though, Dropbox might not be accessible from the machines where you need to work. And in my case, I do a poor job of keeping folders full of individual files neat and tidy, and then a poor job of finding the snippet of code I need later. What I wanted was a simple, web/cloud based tool to keep very basic text snippets. My dream was a little collection of instantly searchable text clippings, accessible from the browser or right in an editor.
I tried a few services without being really satisfied, including Springpad (they abandoned the version of IE I am required to use at work) and Evernote (demands that notes be formatted HTML and not plain text, and thus wrecks SQL code). Most are not suitable for code, but work well for HTML content, like clipping a whole web page.
Then I stumbled upon an improbable set of three tools that work together: Simplenote is a web service that can be accessed by a conventional web site or by a web services API. That means that other people can easily write clients that interoperate with it.
On my Mac, I have a fantastic little program - so lean it’s practically a widget - called Notational Velocity, that can use the Simplenote web service to store text in the cloud. What’s especially sweet about this tool is that I don’t generally interact with the text snippets as if they were files, but instead using instant search and tagging. For me, a much, much better UI. It’s easier to add things, to stay organized, and to find things.
On Windows, I am using a similar lean app called ResophNotes. Both Notational Velocity (Mac) and ResophNotes (Windows) can use the Simplenote web service to store data in the cloud. It’s really useful, and genuinely simple. I’m loving it.
Here’s an image of T-SQL Scripts from the cloud on Simplenote, Notational Velocity (MacOS) and ResophNotes (Windows VM + Fusion Unity) at the same time:
*Sample scripts shown are from Glenn Berry’s blog, run through SQL Prompt formatter and saved to Simplenote. He is a master of just such T-SQL snippets.
Cloud Bookmarks: XMarks
Then there is the illusive problem of bookmarks. Back in the day, most of us had one computer, which sat on a desk plugged into a network, or, if you were really fancy, maybe a laptop on a desk (they were heavy) plugged into a network. Bookmarks were just shortcuts you kept in a folder. Today my normal work environment is a MacBook Pro, with vmware Fusion running Windows 7 alongside Mac OS, an SSL VPN to a work Windows XP virtual machine, and my Android phone. These all work reasonably well in concert for me to get things done. The calendars, email, contacts and so on all (mostly) coordinate via either Exchange or Google and get presented in an integrated client on each platform.
The one last annoying bit that hasn’t worked is bookmarks. There are a ton of bookmark services out there, but for some reason I’ve had a very hard time finding one with all the right features:
- Private (reasonably)
- Accessible through the browser as well as with a plugin or extension
- Well integrated with the browser
I finally settled on XMarks, which, though they had a near-death experience recently, does all that stuff pretty well. They supply a plugin for each major browser that will sync each browser’s bookmarks with the service. (If, for example, you’re in IE or Safari, you are really using IE or Safari bookmarks, and not some awkward, added thingamajig.) They also provide a web interface so that if you are on a machine where you can’t install the plug-in for some reason, you can still get to the bookmarks. This is vital for me because I keep a pretty huge collection of reference links, KB articles, blog posts and so on, and I want to be able to get to them from any of my three OS’s / five browsers, without a lot of effort. The only problem I’ve had is conflict resolution when I have two systems running at the same time, but it’s minor.
Blog Reader: Google Reader
In the last few years Blogs have become absolutely vital - you’re reading this, so there it is! Still I find some people who are slow to come to this realization, and seem to live in a bubble.
The trick with blogs today, though, is that there are too many, and on each one there’s likely to be some noise in between the juicy parts. For this I am a fan of Google Reader. Handy, cloud-based, works in all my browsers / OS’s and on my phone. There’s probably a better reader, but I’m too happy to go looking at this point.
I was a late convert to Twitter and joined only recently (@onupdatecascade). I will say that the SQL community advantage of Twitter, both for camaraderie and for hard technical issues, is real. If you haven’t tried it, do! Nobody will force you to stay, if it’s not useful. I don’t really use it socially, but do use it professionally for SQL Server information and contacts. The default Twitter client isn’t that great, but I do like TweetDeck instead.
The list could go on and on (TFS / Source Control, SQL Compare, PowerShell, others) but these are the items I thought might be interesting, or obscure or harder to find.
Reflecting on all all these tools and gadgets, I realize I didn’t really go looking for them. (Except the bookmarks thing - that was a quest.) This has just been a slow process of accretion, where one day I say to myself, “Where IS that script?” or “I can’t see anything in this query plan,” or “Dang, I didn’t save that query before I closed it.” In most of these situations I just moved on, but at some point I would stop and exclaim, “Alright, today’s the day I stop this nonsense and find a way to solve this issue. Someone out there must have fixed this by now.” And usually, someone has.
What do you use? You’ve probably got medicine for a pain I didn’t even know I had. Drop me a line, or post your favorite tools here.