SQL Server Reference Guide

Mar 28, 2003

␡

⎙ Print

< Back Page 353 of 365 Next >

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.

< Back Page 353 of 365 Next >

🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Privacy Notice

Overview

Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security

Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children

This site is not directed to children under the age of 13.

Marketing

Pearson may send or direct marketing communications to users, provided that

Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
Such marketing is consistent with applicable law and Pearson's legal obligations.
Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out

Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

As required by law.
With the consent of the individual (or their parent, if the individual is a minor)
In response to a subpoena, court order or legal process, to the extent permitted or required by law
To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
To investigate or address actual or suspected fraud or other illegal activities
To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links

This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020

Email Address