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.
» 
Comment and Contribute