SQL Server Architecture

System Databases

During the installation of the Database Engine, the following system databases are generated:

• master

• model

• tempdb

• msdb

master Database

The master database is the most important system database of the Database Engine. It comprises all system tables that are necessary for your work. For example, the master database contains information about all other databases managed by the Database Engine, system connections to clients, and user authorizations.

Because of the importance of this system database, you should always keep a current backup copy of it. Also, the master database is modified each time you perform an operation such as creating user databases or user tables.

model Database

The model database is used as a template when user-defined databases are created. It contains the subset of all system tables of the master database, which every user-defined database needs. The system administrator can change the properties of the model database to adapt it to the specific needs of their system.

tempdb Database

The tempdb database provides the storage space for temporary tables and other temporary objects that are needed. For example, the system stores intermediate results of the calculation of each complex expression in the tempdb database. The tempdb database is used by all the databases belonging to the entire system. Its content is destroyed every time the system is restarted.

The system stores three different elements in the tempdb database:

• User objects

• Internal objects

• Version store

Local and global temporary tables, which are created by users, are stored in the tempdb database. The other objects stored in this system database are table variables and table-valued functions.

Internal objects are similar to user objects, except that they are not visible using catalog views or other tools to retrieve metadata. There are three types of internal objects: work files, work tables, and sort units. Work files are created when the system retrieves information using particular operators. Work tables are created by the system when certain operations, such as spooling and recovering databases and tables by the DBCC command, are executed. Finally, sort units are created when a sort operation is executed.

Optimistic concurrency uses the tempdb database as a place to store versions of rows. Hence, the tempdb database grows each time the system performs the following operations, among others:

• An INSERT, UPDATE, or DELETE statement is executed in a snapshot transaction

• A trigger is executed

msdb Database

The msdb database is used by the component called SQL Server Agent to schedule alerts and jobs. This system database contains task scheduling, exception handling, alert management, and system operator information; for example, the msdb database holds information for all the operators, such as e-mail addresses and pager numbers, and history information about all the backups and restore operations.

Disk Storage Structure

The storage architecture of the Database Engine contains several units for storing database objects:

• Page

• Extent

• File

• Filegroup

The main unit of data storage is the page. The size of a page is always 8KB. Each page has a 96-byte header used to store the system information. Data rows are placed on the page immediately after the header.

The Database Engine supports different page types. The most important are

• Data pages

• Index pages

When you create a table or index, the system allocates a fixed amount of space to contain the data belonging to the table or index. When the space fills, the space for additional storage must be allocated. The physical unit of storage in which space is allocated to a table (index) is called an extent. An extent comprises eight contiguous pages, or 64KB. There are two types of extents:

• Uniform extents

• Mixed extents

Uniform extents are owned by a single table or index, while mixed extents are shared by up to eight tables or indices. The system always allocates pages from mixed extents first. After that, if the size of the table (index) is greater than eight pages, it switches to uniform extents.

Utilities and the DBCC Command

Utilities are components that provide different features such as data reliability, data definition, and statistics maintenance functions. The following utilities are described next:

• bcp

• sqlcmd

• sqlservr

bcp Utility

bcp (Bulk Copy Program) is a useful utility that copies database data to/from a data file. Therefore, bcp is often used to transfer a large amount of data into a Database Engine database from another relational DBMS using a text file, or vice versa.

E.g to copy the the table into a file :

bcp AdventureWorks2012.Person.Address out “address.tx” -T -c

The bcp command in exports the data from the address table of the AdventureWorks database in the output file address.txt. The option –T specifies that the trusted connection is used. (Trusted connection means that the system uses integrated security instead of the SQL Server authentication.) The option –c specifies character mode; thus, the data is stored in the ASCII file.

sqlcmd Utility

sqlcmd allows you to enter Transact-SQL statements, system procedures, and script files at the command prompt.

E.g to run a script in a file and put the results in a file :

sqlcmd -S localhost -i C:\Scripts\test.sql -o C:\Output\report.rpt

DBCC Command

The Transact-SQL language supports the DBCC (Database Console Commands) statements, which are commands for the Database Engine. Depending on the options used with DBCC, the DBCC commands can be divided into the following groups:

• Maintenance

• Informational

• Validation

• Miscellaneous