The
endowment of Microsoft’s extensive data storage platform, SQL server carries
out breakthrough efficiency in storing and maintenance of data. It is a highly
featured database that is primarily designed to cope with the business
environment. The SQL server supports ANSI-SQL and T-SQL query languages. The
query languages are used to manage and retrieve data in any Relational database
Management system.
Moving to the Database Concept in
SQL
A
Relational Database System like SQL server consists of many objects. Among
them, the database is considered as the most significant-level object. A
database is defined as a structured group which includes objects such as table,
views, stored procedures related to the data that is stored in the tables of
database. SQL server’s powerful RDBMS architecture deployment supports the
storage of data in four system databases.
- master
- model
- msdb
- tempdb
Let
us discuss the role and details of data stored by each of the database in SQL
server.
Master Database
As
the name implies, the master database forms the primary database of SQL server.
It maintains a set of system tables which keeps a complete track of the overall
system. The master database contains the following list of information.
- Details of SQL server configuration settings
- Login information details of the account
- Transactional locks and processes
- Regarding registrations related to server
- Logins from remote sites
Path
Location of Master Database Files on System
On
installing SQL server, the master.mdf files and its corresponding log files
(master.ldf) is created in the location by default
If
the master database gets corrupted and is not recoverable from the backup, then
a user has to again rebuild the master database. Therefore, it is always
advised to maintain a current backup of the master database. As, everything
crucial to SQL server is stored in the master database, so it cannot be
possibly deleted. For this reason master database is often referred as mother or
heart of all SQL Server.
Model Database
The
model database sets a model for the entire newly created database. Technically,
it serves as a template for the SQL server in order to create a new database.
Whenever a user tends to create a new database, the data present in model
database are moved to new database to create its default objects which include
tables, stored procedures, etc. Primarily, the requirement of model database is
not specific to creation of new database only. Whenever the SQL server
initiates, the Tempdb is again created by using model database in the form of a
template. The Model database does not contain any data by default.
Locating
the Model Database Files
During the installing
process of SQL server, the model database file and its related log file (model.ldf)
are saved in in the same location as for master file.
MSDB Database
The
Microsoft database (MSDB) is used by SQL server Management Studio, SQL Server Agent
and SQL server, to store data related to backup and restores history and
information pertaining to task scheduling. Additionally, the MSDB database stores
data pertaining to log shipping, replication and maintenance etc.
List
of Data Which Are Present in the MSDB Database
- Information associated with SQL Server Agent like job history, scheduling of jobs, notifications, etc.
- Configuration data about log shipping
- Maintenance data related to backup created online
- Configuration details about replication
As the volume of configuration
related information keeps on increasing in the msdb database, it is always
recommended to back it up at regular intervals. In the event msdb database
getting corrupted, a user has to rebuild the database by making use of SQL
build script which was set up during the installation procedure of SQL server
on the system.
Location of SQL Build Script
The SQL
build script for rebuilding MSDB database is instmsdb.sql and it is present in path. C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Install
When the msdb database is not present, the SQL build script
will first ensure to again create the database and then rebuild the associated
objects. A user can carry out the processing of instmsdb.sql script with the
help of Query Analyzer application. After successfully rebuilding the msdb
database, a user has to again configure the configuration settings associated
with log shipping, replication and task scheduling.
Tempdb Database
From
its name temporary, it refers that Tempdb database serves as a temporary
storage place for processing of data such as tables or temporary stored
procedures with their respective objects. It can be said that the tempdb works
as a resource for users who are working on a particular instance of SQL server.
The tempdb database stores the following list of data
- Temporary cursors, variables, tables and stored procedures etc
- Objects that are handled by SQL database engine to carry out jobs like sorting
- Row versions which are created during the transaction pertaining to modification of data
Location
of Tempdb files
The
tempdb.mdf files and tempdb.ldf can be located on the same path for Master file
Initially the tempdb file is set to a size of 8.0MB but a
user can further increase its size by making use of the database properties or
by initiating the command ‘Alter DATABASE’ for removing the processing
overhead.
Instances Of Corruption in Database
Often the
database of SQL database gets corrupted or damaged by corruption issues and in
such cases if there is no proper backup of the database, then a user has to
depend on third party solution to get access to the data that is stored in the
database of the server. Learn more about database corruptions here
0 comments:
Post a Comment