Components

SQL Server does not use conventions of schemas and instances; Oracle does. In SQL Server, each database is an autonomous application hosted within SQL Server. The database is a logical definition that associates the objects within the database, and then identifies the level of access granted to users and groups.

The defined structure for SQL Server and for the data systems it is hosting, is kept in a set of system tables that are roughly equivalent to data dictionaries in Oracle. The illustration shows the SQL Server defined structure.

• The master database records all of the system-level information for the SQL Server system. It records all login accounts and all system configuration settings. master records the existence of all other databases and the location of the primary files that contain the initialization information for SQL Server and user databases.
• The model database is used as the template for all the databases created on the system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the context of the model database.
• The tempdb database is the temporary storage area used by all databases. It serves the same function as the Oracle "system global area."
• The msdb database is used by the SQL Server Agent component for scheduling alerts and jobs.

In addition to the logical constructs maintained within SQL Server, there are a number of system services that are implemented. Unlike Oracle, which implements a set of system services for each instance, SQL Server initiates only one set of system services for all instances. The MSSQLServer service is the primary service responsible for SQL Server. The SQLServerAgent service is the service responsible for the SQL Server Agent component and manages alerts and scheduled jobs.