Oracle Migration Workbench - Part 4

Wednesday Dec 8th 2004 by Steve Callan

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:

1) "Wow, I wish Oracle did that."

2) "Gee, 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 installation.

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 that great.

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.


SQL Server


Bulk Copy Program (BCP)

SQL Net/Oracle Net

Named pipes and TCP/IP sockets

(named pipes is for connecting to older versions of SQL Server)



Bequeath session

A local or "dot" connection

Oracle Enterprise Manager

Enterprise Manager console

Redo logs

Transaction logs

Trace "trc" files

Debug symbols

Alert log

"ERRORLOG." with older ones named as errorlog.1, errorlog.2, and so on

Install actions oraInventory log

*.out file in the \install directory


Service Packs

Sys and System

The "sa" account and database owners (dbo)

ORA_DBA group

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 Server 2000.

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 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.

Click for larger image

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 Northwind database.

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 completed successfully.

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.

Click for larger image

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 infomwb_ww@oracle.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 Wizard.

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.

In Closing

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

Mobile Site | Full Site