Page 62 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes Learn database planning essentials
Describes the purpose of the database and how it affects the design
Provides guidelines for creating a database plan to fit your purpose

Explains the concept of synonyms and the benefits of using synonyms
Introduction

A database in SQL Server is made up of a collection of tables. These tables contain data and other
objects, such as views, indexes, stored procedures, user-defined functions, and triggers that are
defined to support activities performed with the data. The data stored in a database is typically
related to a particular subject or process, such as inventory information for a manufacturing
warehouse.

5.1 System Databases

SQL Server includes the following system databases.

Table 5.1

System database Description
master Database Records all the system-level information for an instance of SQL Server.
msdb Database Is used by SQL Server Agent for scheduling alerts and jobs.
Is used as the template for all databases created on the instance of SQL Server.
Modifications made to the model database, such as database size, collation,
model Database
recovery model, and other database options, are applied to any databases
created afterward.
Is a read-only database that contains system objects that are included with
Resource Database SQL Server. System objects are physically persisted in the Resource database,
but they logically appear in the sys schema of every database.
tempdb Database Is a workspace for holding temporary objects or intermediate result sets.

5.1.1 Modifying System Data

SQL Server does not support users directly updating the information in system objects such as
system tables, system stored procedures, and catalog views. Instead, SQL Server provides a
complete set of administrative tools that let users fully administer their system and manage all
users and objects in a database. These include the following:
Administration utilities, such as SQL Server Management Studio.
SQL-SMO API. This lets programmers include complete functionality for administering
SQL Server in their applications.
Transact-SQL scripts and stored procedures. These can use system stored procedures and
Transact-SQL DDL statements.
These tools shield applications from changes in the system objects. For example, SQL
Server sometimes has to change the system tables in new versions of SQL Server to support new
functionality that is being added in that version. Applications issuing SELECT statements that
directly reference system tables are frequently dependent on the old format of the system tables.
Sites may not be able to upgrade to a new version of SQL Server until they have rewritten
applications that are selecting from system tables. SQL Server considers the system stored

56 LOVELY PROFESSIONAL UNIVERSITY