Celebrate Your Independence with Free Tools

Been working on this blog post for a while, thought I'd share today an inventory of tools I use regularly that are free to the DBA.

ZoomIt
https://technet.microsoft.com/en-us/sysinternals/zoomit.aspx
A nearly ubiquitous free Microsoft-provided download to provide for easy zooming, live zooming, drawing on your screen, and more. It is something that makes a big difference when communicating to an audience. Won't make you a better presenter, but will make your content clearer and easier to communicate. Be sure to practice with it before you go live. :)

ShareX
https://getsharex.com/
An open source alternative to other screen recording, screen capturing, even making quick .gifs and uploading them to social media, image sharing applications and more.

WinMerge
http://winmerge.org/
An open source alternative to BeyondCompare that has many of the same features, including file and folder structure comparison. I commonly use this to compare large result sets to make sure that my query tuning hasn't drifted the outputs, or to compare two queries to find differences. It's a quick and easy application when this type of text or file comparison is needed. I've been using it for years for these purposes.

Visual Studio Code
https://code.visualstudio.com/
A Microsoft-provided open source rich text editor that has a lot of the features you see in expect in Visual Studio, but in a lightweight, quick-loading text editor that compares favorable to other paid alternatives. It's replaced other alternatives for me for lightweight code editing.

Plan Explorer
https://www.sentryone.com/plan-explorer
SentryOne (formerly SQLSentry) released a user-friendly, feature rich, useful alternative to the SSMS execution plan viewing options that is particularly useful when analyzing massive, complex query execution plans and breaking down costs. The guidance provided is obviously solid technically and can deliver valuable insights. It even has integration with SSMS. A premium paid version has collector service and database.

Also: You should of course also check out new features that have been added to SSMS recently, including Live Query Stats and the Query Store. SSMS itself is a little obvious for this list.

Remote Desktop Connection Manager (RDCMan)
https://www.microsoft.com/en-us/download/details.aspx?id=44989
Not to be confused with Remote Desktop Manager or any similarly-named browser extensions, RDCMan is a free Microsoft-provided tool that makes it easy to organize remote desktop connections, securely store credentials for groups of RDC's, for example in a domain. This is a dream tool for folks who have many RDC's with many credentials in many different domains.

Sidebar: Do you get an annoying popup when you open your RDCMan application, "There were problems decrypting some credentials"? That's the built-in encryption that keeps your stored credentials secure. You'll get that (and lose the stored passwords) when you migrate from one machine to another. You'll have to open the .rdg file and remove the password hashes. There are instructions in the RDCMan help to migrate a personal certificate along with the .rdg file for proper migration, it's the only way to have remembered passwords transfer to another device. (I've been meaning to blog about this over time, but didn't feel it was worthy of its own post.)

OneNote
https://www.onenote.com/
Okay, maybe this isn't free, but it comes with your Office365 account or office license for Word and Excel, so unless you're in a no-Microsoft environment, you have access to OneNote for free.(Teachers and students globally can also get all of Office365 for free.) Had I known about OneNote back in college I'd have been using it then. OneNote is a fantastic information-gathering tool. It can do quite a lot more than a simple note-taking platform. We share password-protected OneNote notebooks on SharePoint for secure team-based collaboration and note taking. And the search function can find word matches in your notes and handwriting, and even in your screenshots, photos, and audio recordings, something you can't do with stacks of legal-size notepad paper. I've presented using OneNote as a whiteboard before as well, and it performs admirably with your drawing pad or tablet touchscreen.

Caveat: By default, the Ctrl+1 zoom feature of ZoomIT (above) and the Ctrl+1 checkbox feature of OneNote make for exciting note taking. The good news ZoomIT is easy to close and re-open.

Slack
https://www.slack.com/
The ubiquitous rich persistent chat app has set a very high bar for other collaboration tools, including the me-too Microsoft Teams. It's free but you have to pay for history and some other features. It's got a million different integrations, is very popular in the tech world for internal corporate team communication, but also in the social activism communities and technical user community, especially in the SQL Server community. The richness of the medium allows for better responses when you're crowd-sourcing SQL help than the twitter hashtag #SQLHelp, which is still popular. Speaking of which...

Twitter
http://www.twitter.com
There is no larger or more popular platform for short-format communication this day and age, used by everyone from teenagers, heads of state, even teenaged heads of state. Hashtags are topics, so posting a question accompanied by #SQLHelp will allow others anywhere in the world to see your post if they follow that topic. Twitter has most impact during live events, especially physical events like conferences, but also sporting events and breaking news. The next time you're at a SQLSaturday event, check out the hashtag for the event, for example, #SQLSatBR at SQLSaturday Baton Rouge. If you're at PASS Summit in the fall, try #SQLSummit. Also, reading about their architecture for outrageous growth scale is interesting.

Quick unsolicited advice: If you're on Twitter, you should try to keep your professional and personal lives separate. You know you shouldn't launch political tirades on LinkedIn or in front of your coworkers or at the Thanksgiving dinner table. You should use your professional platforms to interact with technical peers and colleagues and coworkers, and to represent your employer (and future employers). Use your personal, potentially anonymized account(s) to hurl insults at sports rivals and to argue over whether the dress was blue or gold. As a consultant who tried to keep public work and politics separated, the benefits to this strategy are clear. If you're a government employee, this is probably required of you too.

Microsoft Assessment and Planning Toolkit
https://technet.microsoft.com/en-us/solutionaccelerators/dd537566.aspx
Does a yeoman's job of collecting IT infrastructure inventory in your environment, including SQL Servers, via a variety of connection methods and all without an agent. Generates a bunch of reports that CIO's and managers will eat up. Has delivered good value with minimal investment in time or expertise.

PowerBI
https://powerbi.microsoft.com/
Yep, there is a capable free version for organizations, assuming those organizations have an Azure tenant/Office 365. It is also free for individuals who are not in those organizations.

Vark
http://www.vark.com
Aardvark is a really neat social tool that allows you to source answers come from other users who have signed up for knowledge in general categories. It's really going to change the world.
Just kidding of course, but it was cool in a make-the-world-small kind of way for a while there before Google bought then killed it in 2011.

TRY_CONVERT broken by WITH SCHEMABINDING in SQL Server 2012 Data Tools build

Here's a bug you might find if attempting to schemabind an object that uses the new TRY_CONVERT function.

To reproduce, run the following code in a SSDT database solution:

CREATE VIEW [dbo].[aview] WITH SCHEMABINDING 
AS 
SELECT id = TRY_CONVERT(int, id) 
FROM dbo.arealtable

The database project should build with this error:

Error 3 SQL70561: Cannot schema bind view '[dbo].[aview]' because name 'int' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself. C:\Users\...\Documents\Visual Studio 2010\Projects\...\Views\dbo.aview.sql

This view is perfectly valid in SQL Server, and can be created without a problem. In a Data Tools database solution, it prevents the solution from being built with the above error.

In the above screenshot, "try_convert(int" is underlined in red because SSDT isn't recognizing the syntax correctly.  Again, this only appears when the view is created with schemabinding.

Note that in my bug report, the project's target platform is SQL 2012 and the project's database settings compatibility mode is SQL 2012, using Microsoft Visual Studio 2010 Version 10.0.40219.1 SP1 and SQL Server Data Tools 10.3.21101.1.

This has been marked as "resolved" on Microsoft Connect, and the fix "will appear in an upcoming release of DACFx/SSDT", as of March 2013: https://connect.microsoft.com/SQLServer/feedback/details/772804/try-convert-broken-by-with-schemabinding-in-sql-server-2012-data-tools-build#details

UPDATE: Confirmed that this is now resolved in recent releases of SSDT.

Visual Studio Schema Compare frustration

In Visual Studio Schema Compare, if you have a problem where the Export to Editor is grayed out, the Write Updates is unavailable or the Schema Update Script window is blank, it is in my experience one of two things.

1. One or both of your connections is to a SQL Server, and you don't have adequate permissions.  See here.
2. One or both of your connections is to a Database Solution, and you need to rebuild your database project(s).  If you can't rebuild your database because of errors... fix them, and rebuild the database project.  Make sure you have the sqlcmdvariables chosen for the compare and setup correctly between your database projects, if you have more than one. 
   
   

How I Learned to Stop Worrying and Love the Powershell

So, as a precursor to my next blog post where I actually do something SQL related with Powershell, I'll post my first two powershell scripts here.  They are completely unrelated to SQL, but was my first exposure to Powershell 2.0 and all its majesty.

I am a consultant, and my Cisco VPN client app has half a dozen different VPN connections.  I spent a lot of time on client sites, or, on a VPN to the client side from the office or the home office.  Needless to say, there's lots of stuff I do (such as non-billable with like this exercise, and other efforts that are billable) while not on a VPN.

And since I don't want certain apps running on my machine while I'm on VPN, that I usually run while I am not on VPN, I wrote a pair of Powershell scripts, one called onvpn and another called offvpn.

Here's what they do:

offvpn.ps1 starts up my google calendar sync (which syncs my google calendar to exchange to my iphone, very handy), tweetdeck, firefox, and MSN Messenger.

Clear-Host
if (-not (Get-Process googlecalendarsync -ea 0) ) { Start-Process "C:\Program Files\Google\Google Calendar Sync\GoogleCalendarSync.exe" }
if (-not (Get-Process firefox -ea 0) ) { start-process "C:\Program Files\Mozilla Firefox\firefox.exe" }
if (-not (Get-Process msnmsgr -ea 0) ) { start-process  "C:\Program Files\Windows Live\Messenger\msnmsgr.exe" }

if ( Get-Process vpngui -ea 0 ) { Stop-Process -name vpngui }
if ( Get-Process vpnui -ea 0 ) { Stop-Process -name vpnui }

what does onvpn.ps1 do?  Just the opposite, plus a few more things like the amazonmp3downloader app that only starts up on demand.  It also starts up my vpngui client and my Remote Desktop Client manager app, which I'll inevitably be using over the VPN client.

Clear-Host

Get-Process firefox -ea 0 | % { $_.CloseMainWindow() } 
if ( Get-Process googlecalendarsync -ea 0 ) { Stop-Process -name googlecalendarsync }
if ( Get-Process msnmsgr -ea 0 ) { Stop-Process -name msnmsgr }
if ( Get-Process amazonmp3downloader -ea 0 ) { Stop-Process -name amazonmp3downloader }
if (-not ( Get-Process vpngui -ea 0 ) ) { Start-Process "C:\Program Files\Cisco Systems\VPN Client\vpngui.exe" }
if (-not ( Get-Process RDCMan -ea 0 ) ) { Start-Process "C:\Program Files\Remote Desktop Connection Manager\RDCMan.exe" }

The Get-Process call determines if the processes are running before starting or stopping them.

One small annoyance is that the Stop-Process command isn't that graceful. Firefox always starts up with the "well, this is embarrassing" screen asking me if I want to try and restore the previous session. If there was a way to more gracefully shut down these apps, I'd be open to it.

(Also, as a corollary, I figured out how to post nice code.  The Visual Studio 2010 PowerTools has a copy-to-HTML feature.  Much nicer, eh?)

Questions and comments are welcome.

UPDATE: Updated the onvpn.ps1 script to use a kinder, gentler way of shutting down some apps.  See the comments.

Spare the switch, fix the WinMerge

Are you like me and you prefer the open-source WinMerge as your text comparison/merge tool?

And are you like me, annoyed when you compare and get the "Cannot open file C:\Documents and Settings\....\TFSTemp\......  The system cannot find the file specified." error?

The easy workaround is that you have to close WinMerge before calling it again from Visual Studio.

This is because the really popular string of switches out there (just google "winmerge visual studio") includes the /s switch.

The /s "limits WinMerge windows to a single instance," apparently by bringing up that annoying error.  Not the most graceful way to do that.

Anyway, drop that /s switch.  Visual Studio will be able to open multiple compare windows up for you.  This might have other implications, but at least educate yourself as to what it does, and why that error above isn't really an error.

Here's the switch list I use: 

/e /x /dl %6 /dr %7 %1 %2

 Reference this online manual doc for explanations on the switches.


"There is no off position on the genius switch. " -David Letterman

TFS Sidekicks

I've do a lot of DB Pro solutions and DBA code reviews at my office and on client sites. This free sidekick add-on for Visual Studio has been invaluable.

http://www.attrice.info/downloads/index.htm

Whenever I want to look at files included in a TFS changeset, or all files checked in to TFS for one work item, or comparison across changesets, I find this Visual Studio plugin extremely handy.

For example, I use it for Code Reviews, to compare changes across all Changesets for a given Work Item. It has a quick function to compare the latest version of a file to the latest version of a file before this work item. Ridiculously efficient, I've forgotten how I'd do this before.

Very quick install, low-impact. I imagine it could be very handy for lots of different Source Control-related sources and diffs in Visual Studio that you just can’t do with Visual Studio alone. Anyone else use it?