SQL Server Reference Guide

In my checkered past I've been in charge of a lot of different Relational Database Management Systems (RDBMS). I've worked on everything from COBOL systems to Oracle, from PostgreSQL to Sybase, and MySQL to SQL Server. Most of the time I've used only one of them at a time, and on the platform for which they were written, such as VM on a 3270 terminal, Unix, Zenix, Ultrix, HP-Unix, Linux and others, CPM, DOS, OS/2, Mac OS, OS X and Windows of all flavors. In all of those platforms and RDBMSs I normally stayed away from using third-party tools to manage the systems or the databases. I just learned the tools that came with the product and supplemented anything I had trouble with by writing scripts or programs.

As I moved further in my career, I began to be involved with larger slices of the company. In my current position, I'm a "database architect," responsible for finding the best data solution for multiple requirements. As such we're running DB2, Oracle, MySQL and yes, SQL Server. We're running our production systems on large clusters for a pretty substantial user base.

I also use several operating systems in my work, and at home. I run Windows XP on one system at work, and Redhat Linux on another. Here at home I use two systems to write these tutorials: An x64 system running Windows 2003 Enterprise, and a Mac G5 running OS X. I use the Terminal Services client for OS X to connect to the SQL Server systems running in a Virtual Server on the x64.

How I Write

So when it comes to managing all of the systems I use, I have one of two choices. I can either learn all of the different management interfaces and operating systems, or I can use a tool to manage the systems using one interface. Although I keep up-to-date on the systems we have a large footprint on (Oracle and SQL Server, Linux and Windows), from time to time I need to jump in to a system and run a quick query or reverse-engineer a database schema from one product to recreate it in another. For that I've found a single tool is best.

But which tool? My need is unique in that I need to run a single interface against not only multiple database systems, but from multiple platforms. And I need to do it cheaply.

Enter Aqua Data Studio, from Aquafold. Although I'm not much of a "tool guy," I've been thoroughly impressed with how well this product does what it says. After working with so many platforms and learning how they do things differently, I'm even more impressed with the job Aquafold has done with this tool. In this tutorial I thought I'd share my experiences using this tool with you. I'll focus on the multiple-platform to SQL Server part of how I use it.

What Does it Do?

Working with SQL Server in version 2000, you're familiar with both Enterprise Manager and Query Analyzer. In version 2005, those tools are replaced with the SQL Server Management Studio. The Aqua Data Studio does much of the same work, and from just about anywhere. Here's a quick feature list:

• Query Analyzer
• Schema Browser
• Schema Editor
• Schema Extraction
• Data Definition Language (DDL) Extractor
• Table Data Editor
• Visual Explain Plan
• Storage Editor
• Security Editor
• Simple Import and Export

Although I'm only going to cover SQL Server in this tutorial, Aqua Data Studio can be used with multiple RDBMS systems:

• Oracle (8-10g)
• DB2 (7-8)
• SQL Server (7, 2000 and 2005, including MSDE and Express)
• Sybase Anywhere and ASE (6-15)
• Informix (7-10)
• PostgreSQL (7-8)
• MySQL (3-5)

In addition, you can connect to any JDBC or ODBC data source as well.

Here are the platforms you can run it on:

• Windows
• Linux
• OS X
• Solaris

You can also run it on any platform that supports Java.

I won't cover all of the features in this tutorial today – just a quick overview on how you can install the product, connect to a system and browse around a bit. I'll give you my impressions on how well the product functions and tell you about any limitations I find.

Installation

To get the software, just navigate the Aquafold's site: http://aquafold.com. In the "Downloads" section you'll find everything you need based on the operating system you're working on. The licensing is free for personal use and for educational institutions, and it's around 150.00 U.S. as of this writing for everyone else. They have an older version (2.0.7) that is also free for everyone, but I haven't used that version. I downloaded the latest version (4.5) and told the company that I planned to do a review on it.

I won't cover all of the platforms here, but I will cover the ones I've used: Windows, Linux and OS X.

Windows

The Windows download comes in two flavors: one without the Java Engine and one with. On the system I tested the product with I used the "with Java" version, since the virtual machine I set up to test with didn't have Java.

Once I downloaded the software, it was a simple "Next, Next, Finish" to get it installed. I opened the software, registered an IA64-bit SQL Server 2000 server and browsed around with no trouble:

Windows

Linux

On Linux the install downloaded as a gzipped file, which I decompressed into the /var directory on my test Redhat Linux install. Once there I just double-clicked on the .sh file to load it and hit that same 64-bit SQL Server 2000 system, and ran a quick query:

Linux

OS X

Loading the software on my Mac was just as simple. I downloaded the package, which OS X mounted with a standard installer. Once that was complete I moved the folder it created on the desktop to my Applications area and double-clicked the software. I then registered a SQL Server 2005 server, and ran a quick query against the database:

OSx

A Quick Tour

I'll have to be honest. Although I'm not a professional developer, I have been known to compile a program or two in my day. I've tried lots of languages, from C to Pascal, and I try to keep an open mind about languages. I've even dabbled in LISP. When the Java craze hit I rushed to download the SDK and try my hand at coding a utility or two that I needed. I was highly disappointed. Although I understood the syntax Java had, I found it to be very slow, and doing anything graphically was just out of the question, at least for me (Enterprise Manager in Oracle 9i, anyone?). And platform independent? Well, sometimes. For some things. So I wandered into easier territory like Visual Basic and C#. (Send all flames and jeers to: IhaveHeardItAllBefore@ItDoesntReallyMatter.com) When I heard this application was written in Java I recoiled a little.

But I've been proved wrong. This is one of the smoothest interfaces I've used in a long time, and I absolutely love that it's consistent across all my platforms (see the previous graphics). It's also blinding fast – I've had no issues with the queries I tested it on. To be fair, I didn't pull back millions of rows of data, but the data I did retrieve went very quickly. Although I won't exercise the whole package here, let me show you a few of the more interesting operations here.

First, to register a server you are presented with a myriad of platforms. One of the nicest features is that you can choose Windows Authentication (done here on a Mac) and be able to choose the account to use. Anyone at Microsoft listening? This feature isn't available even in SQL Server Management Studio, which is one of my favorite tools.

Figure 1

Once inside, you have the typical interface of "browse objects on the left and right-click them to perform operations."

Figure 2

Right-clicking a database and selecting the Query Analyzer tool gives you the ability not only to return the rows of data in a grid or textbox, but also a textual ShowPlan:

Figure 3

In addition, this same window gives another panel that shows a pretty detailed Client Statistics area:

Figure 4

In any of these panels you're still able to edit your query. Another useful feature in the Query Analyzer is Auto-Complete. With this feature the system fills out the object names for you as you type. I found this to be a real time-saver.

Figure 5

Let's examine another useful feature before we move on. One of the tasks I have to do on a regular basis is evaluate the database schema of an application, to ensure the best design. Depending on who wrote the application, I have to discover all the tables, columns, types and relationships and diagram them out. There are commercial tools out there that do this sort of thing, but managers are not always willing to open the purse-strings for those sorts of things.

Aqua Data Studio has an ERD generator that works really well. You simply right-click a database, and select Tools and then ER Diagram Generator. Pick the tables, views or schemas that you're interested in and a few moments later you'll have a full diagram to work with. Although this operation can take a while, it does work pretty well.

Figure 6

The diagrams can be sized and laid out automatically. You can view the headers or the full table details, which are always available in a pane on the left-hand side of the screen. There were a few databases I could make this operation fail on, but by and large it worked most of the time.

Impressions

There are a lot of other features I explored with this product, which I don't have the space to cover here. I just really liked this package, from the intuitive interface to the platform and RDBMS ubiquity. It's a great piece of software.

I didn't have any real problems with the package, other than being able to stall it on some of the more complex ERDs I tried to derive. To be fair, I may just not have waited long enough. The Auto-Complete can leave you as well, as it did when I tried to bounce back and forth between screen captures on my Mac. I kept having to refresh the schema and then the Auto-Complete would work again.

What would I add? I'd love to see some platform-specific help features. All of the major vendors have their documentation online, and it would be very useful to put an F1 link in the package that hit the main web page of the vendor's documentation based on the platform you're in.