Part four of this series goes into more detail about SQL
Server and how it compares to Oracle. This part of the series also covers the setup and preparation
to use Oracle Migration Workbench to migrate the Northwind database from SQL Server to Oracle.
At the end of Part Three,
we finished installing SQL Server and created a user account that has access to
the Northwind database. In Part Four, we will go into more detail about SQL
Server and how it compares to Oracle. This part of the series also covers
the setup and preparation to use Oracle Migration Workbench to migrate the
Northwind database from SQL Server to Oracle.
SQL Server versus Oracle
To be more precise, that is
SQL Server 2000 versus Oracle9i. By and large, I believe the supporters of each
product are firmly encamped, that is, they believe theirs is THE database
product and all others pale in comparison. In a segmented market where product
share is widely distributed by platform and type of user, there is no reason to
fault anyone for his or her belief about which product is the best, because "best"
for one person may not be "best" for the other. There is an
historical analogy, which parallels the RDBMS "mine is the best"
mindset, and it has to do with word processing.
If you were on computers in
the late 1980's/early 1990's, you may recall the word processing war among WordStar,
WordPerfect, MultiMate, DisplayWrite and the emerging Microsoft product called
Word. Lotus had something too as I recall. Whichever product you started with,
you tended to stay with it. My favorite was a product named ProWrite, and it
was definitely Off-Broadway in terms of being an actor. Who rules today?
Microsoft Word has become, for all practical purposes, the lingua franca
of the word processing world. One of the top hits on a Google search for MultiMate
is about a cow mating program. Ouch. Not to mention all those brain cells I
stressed trying to remember the 40-plus MultiMate Ctrl-Alt-Shift F-whatever
keystroke combinations to make a document look halfway decent - who would put
up with a program like that in today's world of software?
Part of the purpose of this
series, as far as Oracle DBAs are concerned, is to provide some background for
you about other RDBMS products (MySQL and SQL Server as mentioned, and this may
extend to DB2). For open source, inexpensive database needs for a small web
site, MySQL certainly fills the bill. As a small-to-medium business, the answer
is a bit murky as to which product suits your need. Pricing, performance, ease
of use, and reliability are critical considerations is selecting a product.
If you are coming from the
Oracle world and you take the time and effort to get into some of the features
of SQL Server, you are going to walk away with both of these impressions:
I wish Oracle did that."
I hope Oracle does that in its next release."
SQL Server's interface and
easily accessible functionality, presented either via GUI-based menus or
supplied by ready-to-use scripts and procedures, is far superior to what Oracle
offers out of the box, and that superiority extends to something you only get
once: a chance to make a first impression. The appearance of SQL Server tools,
by the very nature of its one and only platform, capitalizes on the Windows
look and feel. Oracle, on the other hand, uses Java-based GUI interfaces,
which, without getting into Java's limitations on GUI, just does not have the
same pizzazz as a true Windows-based interface. Then again, you will not see
SQL Server on a UNIX-based platform. The "Which is the better server
platform, Windows or UNIX?" debate is for someone else to moderate.
If you have a firm grasp of
Oracle's architecture, learning SQL Server's setup is a relative breeze. You
know what has to be done or what should take place in Oracle, so your task is
to learn how SQL Server accomplishes the same things. However, there are
several key differences in the "how" department.
A quick overview of the
differences at this point will be helpful. There are plenty of other sources
which cover these differences. A Prentice Hall book titled "Microsoft SQL
Server 2000 Database Administrator's Guide" is one such reference. It
includes a chapter about migrating from Oracle to SQL Server, and you can see
how Microsoft's approach to what makes a database work is similar and different
from Oracle's approach. The documentation which comes with Migration Workbench
includes a side-by-side comparison of these database systems. SQL Server, like
Oracle, runs on ACID. That's "ACID" as in Atomic, Consistent, Isolated,
and Durable. If a database system can't pass the acid test, so to speak, you
cannot be certain about the accuracy and consistency of its data, and without
that, what good is it?
Databases, data dictionary and control files
SQL Server installs with six
databases: master, model, tempdb, MSDB, pubs, and the one of interest for us
and Migration Workbench, Northwind. The master database is analogous to a set
of centrally managed control files. Instead of having separate control files
for each database as in Oracle, SQL Server uses the master database for all databases
on the server (we will assume there is only the default instance on the server
and no named instances to keep things simple).
The model database is like
Oracle's templates used by DBCA. Instead of using template information, SQL
Server uses a template database, so an actual database is used to create a new
one that you can alter and configure. The tempdb database is used for sorts,
joins, and whatever else requires temporary space, and its Oracle counterpart,
present in each database, is the temporary tablespace. MSDB keeps track of
jobs, alerts and events.
The last two databases
created during an installation of SQL Server are used for training. They are
the pubs and Northwind databases, and are similar to scott/tiger and the
HR/SH/etc. default schema in Oracle9i and later. For a production or "real"
default instance, the pubs and Northwind databases can be dumped after
The user interface
Considering the Windows
environment, Oracle has SQL*Plus available via the application and by the
command line interface (ignoring iSQL*Plus for the time being). SQL Server
provides SQL Query Analyzer (shown in Part Three) and two command line
interfaces (OSQL and ISQL; we will also ignore any web version). SQL Server's
interface is heads and shoulders above the Windows version of SQL*Plus. It is
almost like comparing Mr. Universe to a 98-pound weakling, the difference is
Rename a database? SQL
Server's built-in procedure sp_renamedb (exec sp_renamedb 'old name', 'new name')
is about as simple as it gets.
How do you want to see the
output of a query? Grid, text, or send results out to a file? Take your pick
from the menu, and was that with or without an execution plan?
The Object Browser setting
maximizes the on-screen real estate, and it is easy to toggle back and forth.
Hiding the Object Browser allows more queried data to be seen on screen.
Other similar features
The table below lists a
comparison between the two products.
Bulk Copy Program (BCP)
SQL Net/Oracle Net
Named pipes and TCP/IP
(named pipes is for
connecting to older versions of SQL Server)
A local or "dot"
Oracle Enterprise Manager
Enterprise Manager console
older ones named as errorlog.1, errorlog.2, and so on
*.out file in the \install
Sys and System
The "sa" account
and database owners (dbo)
System admins are added to
the local administrator group
Standard port of 1521
Standard port of 1433, but
is set dynamically for the TCP/IP socket(s), and ruled by IANA, just like
Oracle (www.iana.org), but Microsoft
actually "owns" this well-known port.
ms-sql-s 1433/tcp Microsoft-SQL-Server
ms-sql-s 1433/udp Microsoft-SQL-Server
The list covers a wide range
of features, so it really does help if you have a firm grasp of Oracle before
starting SQL Server. The next section of this article will deal with the
process of using Migration Workbench to migrate the Northwind database from SQL
Server. A screen shot of part of Northwind's structure is shown below, and we
will be looking for these tables, views, and stored procedures in our Oracle
database once the migration is complete.
Oracle Migration Workbench and SQL Server
Two preliminary steps that
must be completed prior to starting the Migration Workbench utility are
creating a new ODBC data source and installing the executable JAR file for SQL
If you are using Windows XP
(and this is probably true for Windows 2000 as well), the version of the DLL
file on your PC will be new enough (it must be version 3.70.06.23 or higher).
You can find the DLL file in C:\WINDOWS\SYSTEM32. Right-click the file and
select the Version tab, then look at the Product Version information.
Follow the instructions in
Chapter 3 of the User's Guide (the "Before Migrating From Microsoft SQL
Server" section) to create a new data source. The series of screen shots
that follow are what you will see in a step-by-step order. Access the ODBC Data
Source Administrator via Administrative Tools (from the Control Panel or
Start>Programs if that is enabled on your PC). Click Add while in the User
DSN tab view to start the addition process.
Scroll down the list until
you see SQL Server. Highlight it and click Finish.
Complete the fields as shown
below (assuming you want to use the Northwind database; you can use pubs or
whatever else you want).
My user "Steve" is
set up to use OS authentication.
Click Next and select the
Accept the default settings
as shown and click Finish.
Review the setup information
and test connecting to the data source if desired.
I opted to test and the test
After clicking OK on the
setup window, returning to the ODBC Data Source Administrator window shows that
the data source named Northwind is using the SQL Server driver. Click OK to
dismiss the window.
Now we are ready to make Migration Workbench work with SQL Server. If you do not already have it, you will need the plug-in for SQL Server 2000. Oracle Technology Network, at the time of this writing, was reorganizing their download area, so you may get a "page not found or DNS server error" when clicking on the link at http://www.oracle.com/technology/software/tech/migration/workbench/index.html.
If you encounter this error,
send an email to email@example.com
and ask to have the JAR file emailed to you (it is only 542KB in its compressed
state, and 575KB uncompressed). If you navigate to the download page directly,
you can skip the survey.
Once the plug-in is
extracted and placed in the plugins directory of your OWMB installation, you
will see that the "Select Migration Source" window (what you see
after starting Migration Workbench) now reflects MySQL and SQL Server. Select
SQL Server and note that the previous Oracle Model for the MySQL migration will
be truncated. And like before, Migration Workbench will start the Capture
We will finish Part Four
here. If you want to continue on your own, follow the instructions in the
wizard. Without modifying the SQL Server data source, you will encounter a
number of errors as shown below.
At this point in the
process, we are ready to create a new Oracle Model - well, almost. There is
some detailed reading in the User's Guide you have to perform because the "online"
SQL Server migration process requires manual adjustments or editing of some SQL
Server objects (views and stored procedures in the Northwind case). In Part Five,
we will perform the migration process and look at what has to be done to
prepare a SQL Server database for use with Oracle Migration Workbench.
See All Articles by Columnist Steve Callan