Database Query Tools
Before the release of SQL Server 2005,
database developers and DBAs used the Query Analyzer for writing ad-hoc
queries or scripts for creating stored procedures, views or functions. Query
Analyzer was a separate client tool that could be invoked from the Enterprise
Manager. From SQL Server 2005, the Management Studio incorporates the query
window side-by-side with the Object Explorer pane. With integrated Visual
Studio interface, it is now possible to take advantage of version control
systems or debugging facilities for SQL scripts. SQL Server now also comes with
another development tool called the Business Intelligence Development Studio
(BIDS). This tool is primarily used for creating Analysis Service,
Integration Service or Reporting Service solutions.
Traditionally, Oracle did not have such
development tools bundled with it. The only query tool that always shipped with
Oracle was SQL*Plus. SQL*Plus is a notepad style command line query
editor. Most experienced DBAs still use SQL*Plus for quick database
administration tasks. Although it is a powerful utility with its own command
set, writing and debugging SQL scripts with it has not been very easy.
This limited debugging facility has always
made developers to use third party query editors like Quest Software’s Toad.
Fortunately, Oracle started to offer a free development tool called the SQL
Developer. This did not originally ship with Oracle 10g, but is now a
standard part of Oracle 11g. SQL Developer allows simple and advanced features
for connecting to multiple databases and writing, debugging and running
scripts. One advantage of SQL Developer that I found over Toad is that it does
not require Oracle client tools to be separately installed and configured in
the developer workstation.
In this article I have tried to cover a very
high level introduction to both Microsoft SQL Server and Oracle architecture.
Although Oracle architecture and its internal workings are widely published in
numerous literatures, most of the inner workings of SQL Server seem to be
“under the hood” and there are not too many good resources out there that talk
about the nuts and bolts. This is probably because the installation,
configuration and maintenance of SQL Server is made really simple by Microsoft
through graphical interfaces and wizards.
I have not covered some of the advanced
topics of comparison between these two platforms. Most notable for DBAs would
be that of backup and restore. While SQL Server backup is relatively
straightforward and uses full, differential or transaction log
backups, Oracle has quite a few options available. Oracle’s database
backups can be whole or partial, full or incremental
and it can be made either offline or online. To further
complicate matters, an Oracle database can be backed up using either operating
system utilities or its dedicated backup product, RMAN (Recovery
The subject of disaster recovery in SQL
Server brings to mind the concepts of replication, mirroring and log
shipping. Oracle’s offerings are quite strong in this area and include data
guards and flashback queries.
The import and export of data is made simple
enough in SQL Server using DTS packages (version 2000 and 7.0) or Integration
Services (version 2005 and latter). A comparable functionality in Oracle can
be found in Data Pump.
Finally, I would also like to encourage
readers to explore how SQL Server’s DBCC CHECKDB is comparable to
Oracle’s DBVERIFY and compare SQL’s linked servers with Oracle’s database
The discussion of all these topics and their
comparisons can easily make up the contents of another article and I certainly
hope to talk about those in future.