Use Oracle's FREE SQL Developer as a Microsoft SQL Server GUI on OSX

I'm on a Mac, my company uses a SQL Server, and since there is not native SQL Server Enterprise manager client I was forced to find a workaround. One common solution is to have aVirtual Machine running windows but to me that approach feels like using a sledgehammer to pound in a finishing nail.  Thanks to this post on stackoverflow I am able to use Oracle's FREE SQL Developer application to connect to Microsoft SQL Server Instances on my MacBook Pro running Lion.  I've used SQL Developer for Oracle development for a number of years now so the learning curve was pretty small for me but anybody who is familiar with SQL Server Enterprise Manager should be able to find their way around pretty easily, however there are a few things to be aware of which I'll discuss a bit later.


Installation


Before you can install SQL Developer you'll need to make sure that you have the JDK installed on your machine.  I have the Java for Mac Developer Preview installed on my machine from: https://developer.apple.com/downloads/.


Once the JDK is installed (you can check by opening a terminal and typing  javac -version ) the next step is to download and install SQL Developer from Oracle's website here.  Once SQL Developer is installed you'll need to get the jDTS plugin files from: http://sourceforge.net/projects/jtds/files/, at the time of this writing the most current version is 1.2.5.  Once the zip is download and extracted move the entire folder someplace where your account will have execute permissions on the jdsts-1.2.3.jar file.  I have a lib directory in my User folder where I keep all my third party .jar files.

You now have everything you need to get SQL Developer executing SQL queries, you just need to do some simple setup in SQL Developer.  Open SQL Developer and as of version 3.0 go to Tools -> Preferences  and expand the "Database" node.  Next click on Third Party JDBC Drivers click on "Add Entry...".  From this menu browse to the /jdts-1.2.5-dist folder you copied in the step above and select jdts-1.2.5.jar (or whichever version corresponds to the version you downloaded) and click "OK".

Configuration

SQL Server Connection Tab

Once you have the plugin installed exit out to the main SQL Developer IDE and add a new connection by either: clicking on File -> New -> Database Connection or by clicking on the green + icon in the "Connections" tab.   You should now see a "SQLServer" tab where you can enter the connection information for your SQL Server.

Using SQL Developer to Query Microsoft SQL Server Databases

Select Default Database

If you have not used SQL Developer before there are a few things to be aware of.  Since it is primarily an Oracle IDE you have to think in a somewhat "Oracle" way when running queries on your database. Since Oracle is a schema-centric database server you'll save yourself lots of typing if you set a default database for your current connection.   If you don't you'll have to type the fully qualified table name for each table you reference in a query (e.g. database.owner.tablename).  To do this right click on the database you'd like to use and click on "Select Default Database" in the context menu.  Once you do this you can do normal SELECT x FROM Table queries and leave out the fully qualified table name.  You'll have to do this each time you connect to SQL Server.   

SQL Developer will allow you to do many of the basic database tasks you can do in Enterprise Manager, however there are quite a few limitations. You can view your Table/View structure and data, you can also view most of the other objects in SQL Server like Stored Procedures and Functions.   SQL Developer will also allow you write DML (Data Manipulation Language) statements like Update, Delete, and Insert.   You can also write some DDL (Data Design Language) statements like ALTER TABLE.  However, the one huge shortcoming of SQL Developer is its inability to recognize the "BEGIN" keyword.  This means that any statement that uses BEGIN, like transactions, stored procedures, and functions will all generate a syntax error and not execute.

One nice "benefit" of using SQL Developer is the "Format" context menu.  When I'm writing code I often get SQL statements in debugging code, however, its usually one long string, debugging this code in Enterprise Manager usually meant having to manually update format the SQL, however in SQL Developer I can paste the code into the SQL Worksheet, right click, and click on "Format" and have a nicely formatted SQL Statement.  

Conclusion

While its not a complete replacement for Enterprise Manager I do find that SQL Developer will allow me to do many of the things I spend 90% of my time doing to my databases.  The best part is that this is a completely Free and somewhat mature solution.