SQL Server System database

Every SQL Server relies on four primary system databases, each of which must be present for the server to operate effectively. New database administrators and those who have never set up a server from scratch may find these databases mysterious or intimidating, but it’s important to understand their purposes and some of the basic maintenance that should be performed on them to keep your system running smoothly.  Let’s go over the big four system databases.

master

The master database stores basic configuration information for the server. This includes information about the file locations of the user databases, as well as logon accounts, server configuration settings, and a number of other items such as linked servers and startup stored procedures.  While it is possible to rebuild the master database, a better practice is to take regular backups and to restore the database if a problem arises.  Restoring the master database involves a few more steps than a user database, so it’s probably a good idea to practice this on a non-production machine before you need to try it in a real downtime situation.

model

The model database is a template database that is copied into a new database whenever it is created on the instance.   Database options set in model will be applied to new databases created on the instance, and any objects created in model will be copied over as well.  Even on a system where new databases are created infrequently, the model database must exist because it is used to create tempdb every time the server starts.  It’s a best practice to backup model whenever a change is made.

msdb

The msdb database is used to support a number of technologies within SQL Server, including the SQL Server Agent, SQL Server Management Studio, Database Mail, and Service Broker.  A great deal of history and metadata information is available in msdb, including the backup and restore history for the databases on the server as well as the history for SQL agent jobs.  This database can grow out of control in some circumstances, so it’s important to make sure that you are only keeping as much history as you expect to make use of.  Like the other databases mentioned so far, regular msdb backups are recommended.  While the database ships in the simple recovery model, it is a best practice to change to the full recovery model and take log backups if you are making use of the backup and restore history tables.

tempdb

The tempdb system databases is a shared temporary storage resource used by a number of features of SQL Server, and made available to all users.  Tempdb is used for temporary objects, worktables, online index operations, cursors, table variables, and the snapshot isolation version store, among other things.  It is recreated every time that the server is restarted, which means that no objects in tempdb are permanently stored.  Tempdb configuration is a large and important topic, as misconfiguration can have a major impact on the performance of an entire server.  You can review the best practices for tempdb in this whitepaper.  Since tempdb is non-permanent storage, backups and restores are not allowed for this database.  You can also get a nice view of your tempdb database and get an idea of whether it is experiencing performance issues using SQL Diagnostic Manager‘s tempdb monitoring feature, which you can access during a free trial.

Some Additional Information a DBA should be know about:

-Report Server Database

-How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

-Guest User Account

-Properly Sizing the TempDB Database

-How to determine transaction log use

-Data Transformation Services

-SQL Server Integration Services

-Properly Sizing the TempDB Database

-Missing SQL Server Agent History

-Security

-Compliance

-Sources for Database Information - SQL Server 2000 to 2005 Crosswalk

 -Restoring the master Database

 

1-    What are the basic functions for master, msdb, model, tempdb and resource databases?

Master database is used to store information of all the databases on the SQL server. The server cannot start if the database is not configured properly.

The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.

The tempdb is used to store temporary objects such as global and local temporary tables and stored procedures.

The model is essentially a template database used in the creation of any new user database created in the instance.

The resoure Database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

2: True or False - Can you create objects in the Master, Model and MSDB databases?

True.

3: Is it a good idea to create objects in the system databases?

In general , objects should not be created in the system databases.  In general, it is a best practice to create a separate database for user defined objects that would be used instance wide or create the objects in each of the needed databases.  From a DBA perspective, it is a common practice to create a dedicated DBA database in order to perform custom administrative tasks.

If objects are needed for future user defined databases those can be created in the Model database.

4: Does Reporting Services create system databases during the installation process?

Yes.  The databases are:

ReportServer - Stores the reports related data.

ReportServerTempDB - Temporary database storage for Reporting Services.

5: Can you move system databases?

Yes - Here are the key commands:

Master - Configuration changes with a stop and restart to the SQL Server instance.

Model - sp_detach_db and sp_attach_db

MSDB - sp_detach_db and sp_attach_db

TempDB - ALTER DATABASE command

6: Do users need explicit rights to the user defined databases?

No - Rights are granted through the Public Role and Guest user.

7: What are the typical objects that are created in the TempDB database?

Temporary tables (#temptable or ##temptale)

Table variables

Cursors

Work tables

Row versioning

Create or rebuild indexes sorted in TempDB

8: If the model database has the database recovery model set to full, what is the impact?

When new user defined databases are created, they will have a full recovery model.  This means that transaction log backups will be needed to maintain a reasonably sized transaction log.  If not the transaction logs will continue to grow indefinitely.

9: Are all DTS packages stored in MSDB?  Are all SSIS Packages stored in MSDB?

All DTS packages do not need to be stored in the MSDB database, but that was the trend in SQL Server 2000.

All SSIS Packages do not need to be stored in the MSDB database.  Storage on the file system or in the MSDB database are more a matter of preference as opposed to an industry trend thus far.

10:  Does the size of the TempDB database really make a difference and how should TempDB be sized?

In situations where temporary objects are created in T-SQL code (i.e. temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc.) the TempDB database makes a significant difference in overall performance.  In these situations the database should be sized appropriately and moved to a separate disk drive in order to support the IO requests.  If not, the default location and size may be appriopriate.

In situations where TempDB is not used in the applications, then

11: Are all SQL Server Agent configurations stored in the MSDB database?

No - Some of the configurations are stored in the registry.

12: Please explain the relationship between logins and users in the system and user databases.

Logins - All logins reside in the master database

Users - All users reside in the master database, other system databases and in the user defined databases.

13:  With the upgrade from SQL Server 2000 to SQL Server 2005, the system objects changed.  Can you name three of the mapped objects between the two versions of SQL Server?

Here are three examples, but others do exist:

System databases

SQL Server 2000 - master.dbo.sysdatabases

SQL Server 2005 - master.sys.databases

Database files

SQL Server 2000 - master.dbo.sysaltfiles

SQL Server 2005 - master.sys.master_files

IO Statistics

SQL Server 2000 - fn_virtualfilestats

SQL Server 2005 - sys.dm_io_virtual_file_stats

14: Can you explain the differences in restoring the Master database versus a user defined database?

In order to restore the Master database the SQL Server instance must be in single user mode.

After the Master database is restored, the SQL Server instance restarts.

A different set of restore errors may occur as compared to user defined databases.  One example is if different databases exist from the backup to the current time period, errors will be recorded related to suspect databases.

15:  What is the Resource database and in what version of SQL Server was it introduced?

The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.

The Resource database was introduced in SQL Server 2005.

Files and File groups

Database Files

SQL Server databases have three types of files:

Primary data files

The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.

Secondary data files

Secondary data files make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several secondary data files. The recommended file name extension for secondary data files is .ndf.

Log files

Log files hold all the log information that is used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.

NOTE:In SQL Server, the locations of all the files in a database are recorded in the primary file of the database and in the master database. The SQL Server Database Engine uses the file location information from the master database most of the time.

SQL Server files have two names:

logical_file_name

The logical_file_name is the name used to refer to the physical file in all Transact-SQL statements. The logical file name must comply with the rules for SQL Server identifiers and must be unique among logical file names in the database.

os_file_name

The os_file_name is the name of the physical file including the directory path. It must follow the rules for the operating system file names.

SQL Server data and log files can be put on either FAT or NTFS file systems. We recommend using the NTFS file system because the security aspects of NTFS. Read/write data filegroups and log files cannot be placed on an NTFS compressed file system. Only read-only databases and read-only secondary filegroups can be put on an NTFS compressed file system. 

When multiple instances of SQL Server are run on a single computer, each instance receives a different default directory to hold the files for the databases created in the instance.

Using Filegroups

There are many advantages to using filegroups to manage the database workload. A filegroup may contain many datafiles, and the properties of all the datafiles can be managed simultaneously with a filegroup.

Primary and Secondary Filegroups

A primary filegroup contains the primary datafile (mdf) and possibly secondary datafiles (ndf). All system tables are allocated to the primary filegroup.

A secondary filegroup (also called a user-defined filegroup) contains secondary datafiles (ndf) and database objects.

The default filegroup contains objects which were created without an assigned filegroup. The primary filegroup is the default filegroup unless another filegroup is specified.

Logfiles are never part of a filegroup. We learned about logfiles in yesterdays post. The logfile tracks all the changes that have taken place since the last database backup, whereas datafiles have the file extension .mdf or .ndf, logfiles always have the .ldf extension.

In the figure below we have one datafile called RatisCo_Data.mdf in the SQL folder of the C drive (C:\SQL\RatisCo_Data.mdf). Since we didn’t specify any filegroups, SQL Server automatically placed it in the primary filegroup for us. We also created a logfile in the same location (C:\SQL\RatisCo_Log.ldf). That file was not placed inside a filegroup, since logfiles are never part of a filegroup.

Our next example will create one datafile in the primary filegroup and two datafiles in the secondary filegroup (also known as the user-defined filegroup).

We can accomplish this with the following steps:

1. Create one mdf (main datafile) in the primary filegroup.
2. Create two ndfs (secondary datafiles) in a user-defined filegroup called Order_Hist located on a separate drive.
3. Create the ldf (log datafile) on a separate drive.

Each data file has its properties set in its own set of parenthesis.  If you have two parentheses after a filegroup name then that filegroup will have two datafiles (like the [OrderHist] filegroup in the code below). The framework for the code is seen here.

If our goal is to put the mdf on the C: the two ndf files on the D: and the log on the E: then our code would be completed by doing the following:

QUESTION-

1-Find Current Location of Data and Log File of All the Database

 

2-In which Files does SQL Server Actually Store Data?

Any SQL Server database is associated with two kinds of files: *.mdf and *.ldf. *.mdf files are actual physical database files where your data is stored finally. *.ldf (LOG) files are actually data, which is recorded from the last time data was committed in the database.

3-Define the rules for designing Files and File groups in SQL Server.

  A file or file group can only be used by one database. For example, the files abc.mdf and abc.ndf contains data and objects for abc database, cannot be used by any other database.
A file can be a member of only one file group.
Data and transaction log information cannot be part of the same file or file group.
Transaction log files can't be part of a file group.