Oracle Migration Workbench - Part 4 - Page 2

December 8, 2004

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.

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.