Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Dec 8, 2004

Oracle Migration Workbench - Part 4 - Page 2

By Steve Callan

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.

Oracle

SQL Server

SQL*Loader

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)

Tnsping

ODBCPing

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

Patches

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 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM