SYSTEM DATABASES

SQL Server Database Administrator needs to have a sound knowledge of System Databases in
SQL Server. System databases are an integral part of the SQL Server product as it depends on
the System Databases to function. Having a good knowledge of System Databases will help the
Database Administrator to perform day-to-day tasks effectively.

Types of System Databases

 Master Database

 Model Database

 MSDB Database

 Resource Database

 Tempdb Database

Master Database

 Records all the system-level information for an instance of SQL Server.

  It is also called the heart and soul of SQL Server.

 Every instance of SQL Server will have an independent Master database; as it captures
instance level configuration information.

 The information which is captured in the Master database includes SQL Server instance
level configurations, linked server configurations, SQL Server Logins, Service Broker
Endpoints, System Level Stored Procedures, and System level Functions etc.

 The system and user databases related information such as name and location for user and
system database are captured in Master database.

 If master database is corrupted or if it is not available then the SQL Server Service will
not start.

 The Master database basically consists of two physical files, namely master.mdf (data
file) and mastlog.ldf (log file).

Model Database

 The Model database is basically used as a template when creating databases in SQL
Server.

 Basically SQL Server takes a copy of Model database whenever a user tries to create a
new database in SQL Server.

 This also means that if a user creates any tables, stored procedures, user defined data
types or user defined functions within a Model database; then those objects will be
available in every newly created database on that particular instance of SQL Server.

 The Model database basically consists of two physical files namely Model.mdf (data file)
and ModelLog.ldf (log file).

MSDB Database

 It is used by SQL Server Agent for scheduling alerts and jobs.

 MSDB also stores information related to configuration of Service Broker, Log Shipping,
database backups and restore information, Maintenance Plan Configuration,
Configuration of Database Mail, Policy Bases Information of SQL Server 2008 etc.

 The MSDB database basically consists of two physical files namely MSDBData.mdf
(data file) and MSDBLog.ldf (log file).

 If the MSDB database is corrupted or damaged then scheduling information used by SQL
Server Agent will be lost. This will result in the failure of all scheduled activities.
Tempdb Database

 It is a workspace for holding temporary objects or intermediate result sets.

 This database stores all the temporary tables and the temporary results generated by
group by, order by, distinct clause etc.

 Tempdb is created every time SQL Server is started

Resource Database

 The Resource database is a read-only database that contains all the system objects that are
included with SQL Server.

 The Resource database does not contain user data or user metadata.

 The Resource database makes upgrading to a new version of SQL Server an easier and
faster procedure.

What is a Warehouse?

 A data warehouse is the main repository of the organizations historical data.

 In other words the data warehouse contains the raw material for managements decision
support system.

 A data warehousing system can perform advanced analysis of operational data without
impacting operational systems.

OLAP v/s OLTP

 We can divide IT systems into transactional (OLTP) and analytical (OLAP).

 OLTP stands for Online Transactional Processing

 OLAP stands for Online Analytical Processing

 OLTP systems provide source data to data warehouses or describe processing at

operational sites.

 OLAP systems help to analyze data or describes processing at a warehouse.

OLTP

 is characterized by a large number of short on-line transactions (INSERT, UPDATE,

DELETE).

 The main emphasis for OLTP systems is put on very fast query processing, maintaining
data integrity and an effectiveness measured by number of transactions per second.

 In OLTP database there is detailed and current data.

OLAP

 is characterized by relatively low volume of transactions.

 Queries are often very complex and involve aggregations

 For OLAP systems a response time is an effectiveness measure.

OLAP applications are widely used by Data Mining techniques. In OLAP database there is
aggregated, historical data, stored in multi-dimensional schemas (usually star schema).
 OLTP System OLAP System
Online Transaction Processing Online Analytical Processing
(Operational System) (Data Warehouse)

Consolidation data; OLAP data

Operational data; OLTPs are the original
Source of data comes from the various OLTP
source of the data.
Databases
To control and run fundamental business To help with planning, problem
Purpose of data
tasks solving, and decision support
Reveals a snapshot of ongoing business Multi-dimensional views of various
What the data
processes kinds of business activities
Short and fast inserts and updates initiated Periodic long-running batch jobs
Inserts and Updates
by end users refresh the data

Relatively standardized and simple queries Often complex queries involving

Queries
Returning relatively few records aggregations

Depends on the amount of data

involved; batch data refreshes and
Processing Speed Typically very fast complex queries may take many
hours; query speed can be
improved by creating indexes
 Larger due to the existence of
Relatively small i.e Mb-Gb of data
Space Requirements history data i.e. Gb-Tb of data

Typically de-normalized with fewer

Database Design Highly normalized with many tables tables; use of star and/or snowflake
schemas

Operational data is Instead of regular backups, some

critical to run the business, data loss is environments may consider simply
Backup and Recovery
likely to entail significant monetary loss reloading the OLTP data as a
and legal liability recovery method