Database Architecture
Starting with a standard installation of both databases, there are a number of differences to be aware of right off the bat. A SQL Server instance refers to a functional installation of the product with all appropriate binaries and allows the creation of multiple user databases, each with its own hierarchy of objects and settings. When someone refers to a SQL Server instance, he or she is typically referring to the installation along with the memory and other server resources it can consume. A database in SQL Server can mean system or user database and this is where the actual data is stored. In contrast, Oracle users are generally referring to the physical files when they mention database. There really isn’t an equivalent for Oracle schemas in SQL Server 2000 but for all practical purposes, you can loosely map databases in SQL Server to Schemas in Oracle. While Microsoft’s documentation lists 16 instances as the maximum “supported” number, this is not a physical limit. You can create 17, 30, or even 120 instances depending on how big a server you have, of course unsupported by Microsoft! Functions equivalent to the Log writer, Database writer and Oracle shadow processes are provided by multiple threads in sqlservr.exe process. This is similar to the functioning of the ORACLE.EXE process on Windows platforms.
Instance and Database
In Oracle, processes (background processes) and memory allocated make up an instance. Database refers to the physical files (.dbf and .log etc). Having a database is not necessary to run an instance. If the instance is not part of parallel server configuration (Real Application Clusters) the relationship between an instance and a database is always 1:1. The main properties of an Oracle instance are specified using the initialization parameter file (pfile or spfile). When the instance is started, the parameter file is read and the instance is configured accordingly.
In SQL Server, an instance refers to processes, memory allocated and physical files associated with default system databases used by that particular installation. By default SQL server installation comes with a set of system databases (with its own hierarchy of objects and settings).
In SQL Server, the settings for the instance are stored in the registry, master/resource database and msdb database.
To an Oracle DBA, creating a database means creating an entire database system that contains control files, redo logs and, data dictionary and tablespaces. In the coming section, we will discuss what these objects are and how they are mapped to the SQL Server equivalents. The instance creation tasks are accomplished as part of the installation process. Hence creating a database in SQL Server implies adding a user database to the already existing system databases. One SQL Server instance can hold more than one user defined database and the instance and database ratio is 1:32767.
Memory Structures
Memory management is one area that is a constant source for confusion and claims of superiority and accusations of inferiority. In reality, both Oracle and Microsoft use similar, industry proven data structures and techniques such as latching for managing memory, at least at the conceptual level. The key difference is in execution. Oracle chose the path of allowing much finer granularity in user control down to specific memory buckets for different uses while Microsoft opted to have the system manage memory allocations
completely by default.
Below picture compares the memory structures in each database’s address space. There are a lot of similarities in the how each RDBMS carves out the total address space into specific memory structure for different uses. The biggest difference here between Oracle and SQL Server is how they are managed.
Blocks, Pages, and Extents
The smallest unit of logical storage in Oracle is a block (1block=2K) and the equivalent in SQL Server 2000 is a page(1 page =8k). Oracle allows user defined block sizes ranging from 2K to 32K while SQL Server has a fixed page size of 8K. A collection of pages is known as an extent for both databases but SQL Server extents are fixed at eight pages so you have fixed sized extents at 64K. Oracle extent sizes are again, user defined. SQL Server does not have an equivalent to Oracle’s segment.
Redo, Undo, and Transaction Management
There is one major difference in the way Redo and Undo is managed in SQL Server, compared with Oracle. A minimum of one Transaction Logfile is present in each of the “databases” and performs both redo- and undo related functions. Because of the way it is done, this basic architectural difference can cause confusion and some restrictions in the way SQL Server 2000 works.
Another major difference is the way locking is done. Although both RDBMS lock at the lowest level possible (i.e., row level) and support shared and exclusive locks, Oracle reads are not blocked by writes (and vice versa), while in SQL Server 2000, writes can block reads. This can be overcome by allowing what is known as “dirty reads,” and is an indirect result of how undo is logged. Dirty reads allow uncommitted data to be read by another process, so the application designer needs to be aware of this feature and cater to either possible transaction inconsistency on the one hand or reduced concurrency on the other. A DBA also needs to understand, monitor and be able to resolve locking issues in SQL Server more effectively.
Database storage functions
In SQL, database has a primary datafile (default .mdf extension) and a zero or more secondary data files (default .ndf extension). Having multiple data files are fairly common in SQL Server for either performance or scalability reasons. Each database also has at least one log file (default .ldf extension)
In SQL Server, file groups are of two types, primary and secondary. System wide tablespaces in Oracle include,
System Tablespace (Permanent): The System tablespace stores the data dictionary for the instance/database, which is some what equivalent to the master/resource database in SQL Server.
Sysaux Tablespace (Permanent): The Sysaux tablespace stores all auxiliary database meta data related to options and features such as RMAN, Job Scheduling, AWR repository, etc. Sysaux tablespace can be roughly equated to the msdb database in SQL Server.
Temp Tablespace (Temporary): The Temp tablespace contain data that persists only for the duration of a user’s session. Oracle uses Temp tablespace as a sort work area and for join operations involving internal work tables. Similar to Tempdb database in SQL Server.
Undo Tablespace (Undo): An Oracle database has a method of managing information that is used to roll back, or undo, changes to the database. The information consists of records of the actions of transactions, primarily before they are committed. Undo records provide read consistency (avoiding reader-writer blocking scenarios) by maintaining the before image of the data for users who are accessing the data at the same time another user is changing it.
Data File: Similar to SQL Server, data file is an operating system file to store objects and data. One or more data files are logically grouped together to make a table space. One data file is associated with only one tablespace. Unlike SQL Server, there are no Primary/Secondary data file types in Oracle.
There are major differences in the way Undo is managed in SQL Server. UNDO management is implemented in SQL Server as part of the transaction logs of each database which handle both redo and undo related functions.
Security and Authentication
In SQL Server 2000, a DBA creates a login account, roughly equivalent to the CREATE USER in Oracle. This login account can be authenticated via one of two modes—Windows security where a user is authenticated at the OS level and logs on to the database transparently, and a mixed mode where user-id and password is supplied. Role based security is available. Every database has a special user called DBO (DataBase Owner) that owns all the objects in that database by default.
Backup and Restore
As with any modern RDBMS, SQL Server 2000 provides for inbuilt backup and recovery capabilities. The transaction log plays an important part in this operation, just as the redo logs do in Oracle. There is no concept of hot backup as with Oracle—all full backups are done when the Database is up and running.