Oracle Database to SQL Server Comparisons


One of the initial obstacles a database administrator encounters is learning where features of his/her system live or reside on a less familiar system. Steve Callan approaches this feature comparison by taking SQL Server and mapping its features back into Oracle.

No, this
isn’t one of those “why my RDBMS is better than your RDBMS” comparisons. One of
the initial stumbling blocks a DBA on one system encounters is learning or
trying to figure out where features of your system live or reside on the other (less
familiar to you) system. Let’s approach this feature comparison by taking SQL
Server and mapping its features back into Oracle. By way of time and
constraints, this comparison will not be 100% inclusive of either system, but
it will show (as much as possible) the SQL Server to Oracle mapping using what
you can see in SQL Server Management Studio (SSMS).

The Object
Explorer pane in SSMS serves as a good frame of reference to do the reverse
mapping. I could have used Oracle and mapped over to SQL Server, but two reasons
negated that. The first is where do you find a convenient one-stop interface in
Oracle? Definitely not in SQL*Plus, and it would be hard to argue that Database
Control’s interface is anywhere near as utilitarian as SSMS. The other is that
most readers of this article are coming from an Oracle background, and we want
to take what we know about Oracle and see how that fits or works in MSSQL.

The versions
used here are Oracle 10gR2 (platform does not matter) and SQL Server 2005 on
Windows Server 2003 (also R2). There are some slight differences between MSSQL
2005 and 2008, but as far as the Windows OS is concerned, it won’t be an issue.
Oracle 10g is still in wide use as is MSSQL 2003/Server 2003, so the comparisons
made here will work for most of the people most of the time.

Database Engine

Let’s start
with a fully collapsed tree in Object Explorer.

a fully collapsed tree in Object Explorer

Much like
what SQL*Plus can show at the command line interface with respect to who you
are logged in as, the same appears at the top showing which database engine I’m
connected to. The database engine (SID) is named WIN2003, which also happens to
be the name of the Windows server. A one-to-one match like this generally
implies that the default instance name chosen during installation is “dot” or
the name of the server. The version of MSSQL is also shown, as is who is
connected in this particular session. By looking at the connected user or
account (and assuming someone wouldn’t create an account explicitly named this
way), we know the login was validated via Windows authentication. Can you tell
by what is shown if SQL Server authentication is enabled? No – you would have
to go into Properties>Security to see which option (Windows only or both) is
selected. The authentication mode is analogous to letting Oracle maintain
security via its internal mechanisms or using OPS (operating system
authentication).

What else
could you safely assume about what the connected user can do? By default, the
Administrator (the user who started the SSMS session) will have “sa” privileges
(sa being analogous to a combination of SYS and SYSTEM). If you look at the
various MSSQL-related groups on Windows, you won’t see Administrator explicitly
listed. In Oracle, looking at the ORA_DBA group, Administrator is listed as a
member of the group.

Right-click the connection and select Properties.

Right-click the connection and select Properties

Many of the
settings buried in each “page” correlate to parameters in Oracle; not so much
on the General page, but definitely so in the rest. One setting of particular
importance has to do with how much memory MSSQL is allowed to use. Click Memory
and the display changes to what is shown below.

One setting has to do with how much memory MSSQL is allowed to use

The value of
2147483647 MB is for all practical purposes unlimited. You would definitely
want to set this to something lower than what the OS has available. Does this
memory setting represent ALL of the memory used by MSSQL? No – it represents
the buffer cache, which is typically the largest memory component of MSSQL.
MSSQL has other things going on outside of the buffer cache, and here is where
Oracle and MSSQL are similar: it can be somewhat difficult to determine how
much memory overall, each system uses. Microsoft has performance counters,
which can be selected in a management console, and the information presented
therein can help you come up with an answer to “how much memory is SQL Server
using?”

Several items
on the Advanced page will also be of some interest.

Server Properties - Advanced Page

Although
there are some Net Services-like comparisons within MSSQL’s Surface Area Configuration,
the Remote Login Timeout is the closest to what is found in the sqlnet.ora file
for connection timeout settings. This may be one of the first places you go to
if remote clients (across a slow network) are experiencing timeout issues while
trying to connect.

Within
Oracle, parallel query or parallel execution operations can greatly help – or
hinder – a database’s performance. The same holds true in MSSQL. Two factors
come into play: the cost threshold and the max degree of parallelism. MSSQL
also has an optimizer, and if it determines the cost is above 5, then
parallelism will be considered. Okay, if parallelism is considered, how much is
allowed? The zero setting means unlimited (up to the number of CPUs, and is
also constrained by the edition, Standard versus Enterprise). Unfortunately (or
not, depends on your viewpoint), this is an across the board setting. We can
open up Oracle for maximum use of parallelism, but it can also be throttled
overall and at the table level.

Databases

Alright, we
understand what “database” means, and as far as tables and indexes are
concerned, the two systems are not that different. Looking at the list of
databases shown below, what do we know about WIN2003?

Object Explorer

Right off the
bat we know that at most, what we are seeing is a slightly more than default
installation. The reason we know this is because of the ReportServer (and its
temp DB) database being present. SQL Server Reporting Services (SSRS) was
either selected during installation or installed during another “setup.exe”
session.

The System
Databases are of more interest to us right now.

The System Databases

Four
databases are always present in MSSQL: master, model, msdb and tempdb. The easy
analogy between systems is that master and msdb correspond to the SYSTEM and SYSAUX
tablespaces in Oracle. Oracle does not have a model database, but it does have
templates, so model in MSSQL is quite similar to the templates you see in
Database Configuration Assistant.

Oracle does not have a model database, but it does have templates

The tempdb
database lives on as the TEMP tablespace in Oracle, matching many of the same
features and purposes. The tempdb is re-created whenever the MSSQL instance
starts.

How do you
create a database in MSSQL? One way is to use the wizard (right-click
Databases>New Database…). Once created, add tables, indexes and programmability
(triggers, stored procedures) and off you go. Another way is to plug in a
database file (and possibly a transaction log file) via an attachment process
or from restoring a backup. An attached or restored database isn’t necessarily
new (they had to have come from somewhere else), but they may be new to your
instance. This process also leads to the idea of cloning.

How would you
clone a production database into something else (e.g., a development database)?
MSSQL offers several ways to clone a database. One of the simplest methods is
to take (or use) a backup of the source and use the BAK file for a database
restore (right-click Databases>Restore Database…). Database Control in
Oracle offers a Clone Database feature (at extra cost). You can also use RMAN
or some type of backup (even export/import, traditional or datapump).

The database
level is where a huge departure between Oracle and MSSQL takes place. In
Oracle, we are archivelog mode or not, and that applies to everything (minus
read only tablespaces). In MSSQL, a database runs in one of three
recovery models
(full, simple and bulk logged). Instead of having archived
redo logs filling up an archived log destination and hanging the instance, with
MSSQL you can grow the transaction log (LDF file) to such an extent that you
fill up a disk and cause the same problem. Taking periodic backups of the
transaction log (and then truncating the log) prevents this problem in the FULL
model. It then becomes analogous to a more granular point in time recovery
capability.

Database
files between the two systems are somewhat similar. Many, if not most MSSQL
databases will have two files: the database file (MDF file extension) and a transaction
log file (LDF file extension). You can have more than one MDF file and even
have file groups. The drill down path to find file information in MSSQL (and
yes this is also easily done via T-SQL commands) is found in the properties of
a database (on the Files page, shown below).

Database Properties

By default,
we get the PRIMARY filegroup. Other filegroups can be used/created, and one
reason for that would be for use of partitioned tables. For a production
database, the Autogrowth rate/setting can be important. You can almost think of
this as a percentage based extent growth setting in Oracle. The options for
growth are similar to what Oracle offers.

Change Autogrowth for masterlog

As you can
see, MSSQL can grow files in one of two ways, cap the size or not, and
determine who should grow a file. Just as users can spend time waiting for file
growth in Oracle, users (or the system itself) can spend time waiting for file
extend operations in MSSQL. If a file is going to grow a lot, then grow it in
larger chunks at a time, but not too large because then you spend (too much)
time waiting for that too. Lastly, do you want a file to be able to eat up all
remaining disk space? If not, then restrict the file growth, just as you would
in Oracle.

Summary

In the next
part of this series, we’ll continue with more of the mappings from SQL Server
to Oracle.

»


See All Articles by Columnist

Steve Callan

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Latest Articles