MCPMag.com

Sign up for our newsletter.

I agree to this site's Privacy Policy.

SQL Advisor

Free Alternatives to SQL Server Management Studio

There are some nifty, better-than-SSMS tools that cost nothing to use. One of them is already in your toolbox and you might not know it.

I'm down on SQL Server Management Studio right now. I basically just spent a day trying to swap out the evaluation version of SQL Server 2008 R2 with the Express version, and SSMS just wouldn't install, apparently because some components of the evaluation version wouldn't uninstall. It's a long, ugly story that I won't bore you with, but a Web search shows that I'm certainly not alone in my frustration, which is cold comfort indeed, as they say.

Graham O'Neale's blog post illustrated problems similar to what I experienced. Note that he starts out with: "Ok, I'm angry...."

So are a lot of other people. Aaron Bertrand wrote a detailed account of his attempt to uninstall the evaluation edition and ultimately had to resort to registry hacks. "That was WAY too painful," he said. I feel that pain. So do dozens--hundreds, thousands?--of others. I wish Microsoft would just fix the damn thing.

Anyway, after many hours of exasperation, I decided to give up and started wondering what alternatives to SSMS were out there for developers wanting to mess around with SQL Server. Being of little coinage, my main requirement was simple: they had to be free.

Turns out, all I had to do was hit the Window key and type "ISE." (Well, Microsoft got THAT right.)

That command brings up the Windows PowerShell Integrated Scripting Environment (ISE). PowerShell is typically described as a tool for automation tasks or a "command-line shell designed especially for system administrators." But it can do much, much more.

Coincidentally, in my day job as technical editor of MSDN Magazine, I'm currently working on an article about PowerShell and how its seamless integration with the .NET Framework allows developers to do some pretty cool things. This article (to be published in the July edition) describes how to use the WPF PowerShell Kit to build a nifty WPF present value calculator.

I asked the author if he thought PowerShell would be of benefit to SQL Server developers. "PowerShell is absolutely, without a doubt, hands down a valuable for tool for Devs to work with SQL Server (and more)," he replied.

So I've been fooling around with it for a few hours now. It looks promising.

Version 2.0 comes with Windows 7 and Windows Server 2008 R2 out of the box, ready to be used. It can be run from the command line or in the ISE, which I prefer. To get started running scripts, you have to change the default execution policy, as explained here.

Then, to work with SQL Server, you need to install a couple "snap-ins," one for the SQL Server Provider and one to run SQL Server Cmdlets (pronounced "command-lets"). With Windows Server 2008 R2 and SQL Server 2008 R2, this is simply a matter of entering the following commands while in PowerShell: Add-PSSnapin SqlServerProviderSnapin and Add-PSSnapin SqlServerCmdletSnapin. You can read more about that in this TechNet article and this MSDN article.

After you're all set up, you're ready to start interacting with your SQL Server databases with regular Transact-SQL commands with the Invoke-Sqlcmd Cmdlet.

For example, here's a query against the Northwind database (Fig. 1 shows the result in PowerShell):

invoke-sqlcmd -query "SELECT Employees.EmployeeID, Employees.FirstName, Employees.LastName, Orders.OrderID, Orders.OrderDate
FROM Employees JOIN Orders ON (Employees.EmployeeID = Orders.EmployeeID)
WHERE Orders.orderdate > '5/5/1998'
ORDER BY Orders.OrderDate" -database northwind -serverinstance acer | format-table

SQL Server query against Northwind executed in PowerShell

Figure 1. A SQL Server query against the Northwind database executed in PowerShell. (Click image to view larger version.)

Fig. 2 is that same query as executed in SSMS (yes, I finally did get it to install).

... and this one in SSMS

Figure 2. The same query as executed in SQL Server Management Studio. (Click image to view larger version.)

Obviously, SSMS offers some features that PowerShell doesn't, such as the handy Object Explorer pane. But what I've shown is just the beginning of what you can do with PowerShell in the place of SSMS. I'll be exploring PowerShell more as time allows, and I'd also like to investigate other options, such as LINQPad 4 and the free version of Toad for SQL Server.

But I'm sure there are many readers out there who have already followed this path. So I'd love to have you share your experiences with the rest of us. What problems have you had with SSMS? What free alternatives do you recommend? Any experience with LINQPad or Toad? What do you like/dislike about these or various other options? Please comment here or drop me a line.

About the Author

David Ramel is an editor and writer for 1105 Media.

comments powered by Disqus

Reader Comments:

Thu, May 31, 2012

Personally, I love LINQPad. I can connect to MySQL, Oracle, or SQL Server (and probably others, but those are the ones we have). I can write SQL or LINQ queries. And I can write C# or F# executable snippets. And it comes with a ton of examples.

Fri, May 27, 2011 Aaron Kempf Tacoma, WA

you should include 'Microsoft Access Data Projects' as an alternative to SSMS, I just -LOVE- being able to copy / paste a table / view / sproc instead of right-click create script and execute script. I think that Access Data Projects provide a superioer experience to SSMS especially for data entry / testing. Want to loop through all the views in a database and email to a bunch of friends? Thats -easy- to do in ADP, but not in SSMS.

Thu, May 26, 2011 PAUL JEMIOLO FAN

So...I think the point of the rant was just that you should use a test machine for evaluation software. But since a lot of us stay current not by the grace of generous employers who pay us to train and experiment, but by spending time our our PC at home instead, wouldn't it be cool if you could use eval software there without inspiring a PAUL JEMIOLO meltdown? Actually, it would be more than cool. And I think Microsoft realizes that too. Sorry Paul -- I don't think it counts as whining. Your rant on the other hand...well, Mr. poopy pants...you just might want to think about that.

Thu, May 26, 2011

Right on, Paul. I have more partitions on my 3 test computers than ... Just scratch and sniff, not sniffle! Love free EaseUS to re-partition.

Wed, May 25, 2011 David Ramel

Well, if you don't consider the ISE a GUI, you could use something like this: PowerGUI: http://www.powergui.org/index.jspa

Tue, May 24, 2011

Misleading article name. Powershell is NOT an alternative for SSMS which has a GUI. I had hoped to read a comparison of free GUI based SSMS alternatives, so this article was a huge disappointment.

Fri, May 20, 2011 John Houston, TX

Though Paul may have been a little harsh, he is right on the money... When using evaluation software you should always walk in the path that what you are installing it on may not be capable of running afterwards, no matter who wrote the software.

Fri, May 20, 2011 Paul Jemiolo Fredericksburg, VA

OK, I'll start of by saying that Microsoft has pissed me off from time to time. That being said I'd like to say to everyone out there who is complaining actually, "Whining" about not being able to uninstall X version of X MS software eval edition, T.S.! And that is NOT short for Terminal Services. By its very nature eval software is not to be installed on any machine you don't care about needing to be rebuilt from the ground up. Hasn't anybody ever read the warnings included with installation of eval software? If you are "experimenting" with new software on a machine that cannot be wiped and rebuilt then the onus is on YOU not Microsoft. This may be partially their fault because they are so good at what they do you expect to never suffer the consequences of "unprotected behavior" . Let's see, while being the largest software company in the world providing jobs for millions of us around the globe (yes I'm talking to you who has a great job, a fat a$$, a fatter bank account than most and a nice house because of Microsoft), being attacked by foreign governments because you make a product that dominates the marketplace because it's easy to use and reliable. Provides thousands of free tools to make our jobs easier, YOU want them to be perfect in releasing eval software that conforms to every make and model of dilapidated machine you may be using to test it. Have you ever heard of virtual machines? I think there's a free version of virtual server out there for you to use and it's supplied by...OH Yea! Microsoft! Grow up, deal with life and thank God for Microsoft, your livelihood depends on them. If you really don't like Microsoft then I suggest you start a new career working with Wordpress, MySQL, Oracle, digging ditches, serving burgers or better yet making whine out of sour grapes. Whatever you choose, just remember when you install eval software (where applicable) expect to rebuild the machine. Paul Jemiolo

Add Your Comment Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above