Topic:Microsoft SQL Server

From Wikiversity
Jump to: navigation, search
Wikipedia-logo.png Search for Microsoft SQL Server on Wikipedia.
Wikibooks-logo.svg Wikibooks has more on the topic of Microsoft SQL Server.

Before taking this course you should gain some basic knowledge of SQL, by reading Introduction to SQL. If you don't have Microsoft SQL Server installed on your machine yet, don't panic, try downloading the free Express Edition from Microsoft.com/sql.

Basics[edit]

SQL Server has five system databases:

  • Resource (SQL Server 2005 and higher)
  • master
  • model
  • msdb
  • tempdb

Resource[2][edit]

The Resource database is a read-only database that contains all system objects. The Resource database is not displayed in the SQL Server Management Studio object explorer. Generally interaction with the Resource database is indirect, through master database system views and functions that reference Resource database objects. Because the Resource database is read-only, the only time it is modified is when a SQL Server hotfix, service pack or upgrade is installed on an instance.

The Resource database is installed with SQL Server 2005 and higher.

master[1][edit]

The master database records all of the server-wide configuration information for a SQL Server instance, including all server logins and database engine configuration defaults. The master database records the existence of all other databases on the server, including the location of the database files. This database records the initialization information for SQL Server, and in SQL Server 2005 and higher, master provides access to system-level management information through views and functions that reference the read-only Resource database. Always have a recent backup of master available.

A SQL Server System must always have a master database. It must not be deleted.

tempdb[1][edit]

The tempdb database holds all temporary tables, temporary stored procedures and other temporary database objects. It is also used by the SQL Server query engine to serialize intermediate results and for other temporary storage needs. The tempdb database is a global resource, meaning the temporary tables and stored procedures for all users connected to the system are created within it. This database is recreated every time the SQL Server service is started so the system always starts with a clean copy of the database. Because temporary database objects are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.

By default, tempdb autogrows as needed while SQL Server is running. Unlike other databases, however, it is reset to its initial size each time the database engine is started. If the size defined for tempdb is small, part of your system processing load may be taken up with autogrowing tempdb to the size needed to support your workload each time to restart SQL Server. You can avoid this overhead by using ALTER DATABASE to increase the size of tempdb and ensuring that the database Autoshrink option is turned off. Because it is heavily used by the query engine and by all connected users simultaneously, many server performance optimizations are necessarily concerned with making tempdb as efficient as possible.

model[1][edit]

The model database is used as the template for all databases created on a system. When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database, then the remainder of the new database is filled with empty pages (on SQL Server 2005 and higher the "instant initialization feature" improves performance by skipping the zero-fill when new data pages are allocated).

Because tempdb is recreated every time SQL Server is started, the model database must always exist on a SQL Server system.

msdb[1][edit]

The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators. This database is also used to store other application information and configuration data. In SQL Server 2005 and higher, for instance, SQL Server Integration Services (SSIS) packages can be stored in MSDB.

In SQL Server, every database, including the system databases, has its own set of files and does not share those files with other databases.

[1] [2]

References[edit]

  1. http://msdn2.microsoft.com/en-us/library/aa174522(SQL.80).aspx
  2. http://technet.microsoft.com/en-us/library/ms190940.aspx