A High Level Comparison Between Oracle and SQL Server – Part IV
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 Manager).
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 links.
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.