Thursday, April 10, 2008

Basics: ORACLE vs. SQL Server

An Oracle instance:
Is a means to access an Oracle database Always opens one and only one database
Consists of:
Oracle database processes and buffers (instance).
SYSTEM tablespace containing one centralized system catalog, which is made up of one or more datafiles.
Other tablespaces as defined by the DBA (optional), each made up of one or more datafiles.
Two or more online Redo Logs.
Archived Redo Logs (optional).
Miscellaneous other files (control file, Init.ora, config.ora, etc.).
An SQL Server Instance:
Is a Means to Acess an SQL Server database
A SQL Server installation (an instance) can support multiple databases.
There can be multiple instances of SQL Server on a single computer.
Each instance of SQL Server can have multiple databases.
SQL Server also installs the following databases by default:
• The model database is a template for all newly created user databases.
• The tempdb database is similar to an Oracle temporary tablespace in that it is used for temporary working storage and sort operations. Unlike the Oracle temporary tablespace, SQL Server users can create temporary tables that are automatically dropped when the user logs off. • The msdb database supports the SQL Server Agent and its scheduled jobs, alerts, and replication information.
• The pubs and Northwind databases are provided as sample databases for training.

Each Oracle database runs on one centralized system catalog, or data dictionary, which resides in the SYSTEM tablespace.
Each SQL Server 2000 database maintains its own system catalog, which contains information about:
• Database objects (tables, indexes, stored procedures, views, triggers, and so on).
• Constraints.
• Users and permissions.
• User-defined data types.
• Replication definitions.
• Files used by the database.
SQL Server also contains a centralized system catalog in the master database, which contains system catalogs as well as some information about the individual databases:
• Database names and the primary file location for each database.
• SQL Server login accounts.
• System messages.
• Database configuration values.
• Remote and/or linked servers.
• Current activity information.
• System stored procedures.
Similar to the SYSTEM tablespace in Oracle, the SQL Server master database must be available to access any other database. It is important to protect against failures by backing up the master database after any significant changes are made in the database. Database administrators can also mirror the files that make up the master database.

The Oracle RDBMS is comprised of tablespaces.which in turn are comprised of datafiles. Tablespace datafiles are formatted into internal units termed blocks. The block size is set by the DBA when the Oracle database is first created.When an object is created in an Oracle tablespace, the user can specify its space in units called extents (initial extent, next extent, min extents, and max extents). If an extent size is not defined explicitly, a default extent is created. An Oracle extent varies in size and must contain a chain of at least five contiguous blocks.

SQL Server RDBMS uses filegroup at database level to contains table and indexes.Filegroup is logical in nature and contains number of datafiles
SQL Server formats files into logical units called pages. The page size is fixed at 8192 bytes (8 KB). Pages are organized into extents that are fixed in size at 8 contiguous pages (64 KB). When a table or index is created in a SQL Server database, it is automatically allocated one page within an extent.
SQL Server RDBMS perform automatic recovery each time it starts. It first checks to see all the databases entry in master databases and creatd a threads for each of the databases for recovery.Every database has a transaction log file used during recovery.Each database has its own transaction log that records all changes to the database and is shared by all users of that database.The T-log files are looked during recovery to see the uncommited transaction to rollback and comitted ransaction that was not written to disk are rollforward.
SQL Server transaction log = Oracle rollback segment + Oracle online redo log.
SQL Server has a automatic checkpoint mechanism
that ensure any committed transaction called dirty pages since the last checkpoint are written to the disk.This mechanism ensure faster recovery of the database in case of system failure like power outages.
Oracle RDBMS Perform autometic recovery each time it starts. It looks for if tablespace data are same as online redolog data if not it recover it from online redo logfiles ( Roll forward), and then removes any uncommitted transactions that are found in the rollback segments (roll back).
If Oracle cannot obtain the information it requires from the online redo log files, it consults the archived redo log files.
Oracle uses TNS protocol ( Transparent Network Substrate (TNS) data stream )
SQL Server uses TDS protocol ( Tabuler data Stream )

I will keep the series going and will write/share more on this. I love different RDBMS like Oracle/Mysql etc apart from being a SQL Server DBA. My Blog will mainly contain information revolving around these 3 islands in the ocean of RDBMS.