Skip to main content
Our Tech Ideas

SQL Server Databases

Database:  Database is a container which stores data and data objects. It manages data and allows fast storage and retrieval of that data.

Types of Databases in SQL Server:

There are 2 types of databases,

  1. System databases
  2. User databases

1. System Databases:

System databases will be created as a part of SQL Server installation. SQL server-internal operations will be performed using system databases.

User databases are created based on organization requirements we are creating externally and stores data.

System databases available in SQL server from 2000

  1. Master
  2. Model
  3. Msdb
  4. Temp DB
  5. Distribution DB (only available while replication configuring)

From SQL server 2005 along with the master, model, msdb, and tempdb a new database introduced called resource DB.

SQL Server Master Database:

  • The first database loaded by SQL server on services start is master DB. Once master DB starts then only other databases will start. it is just like a brain to SQL server.
  • Master DB stores critical information like SQL server configuration such as CPU info memory configurations sp_configure setting
  • Master stores other system databases information in ‘sys databases’ table.
  • In master DB we have ‘sys alt files’ table which contains the location of all system databases MDF and ldf will be stored. To start any database it has to come to master and then start the database.
  • SQL logins will be stored in ‘syslogins’ table of master DB. If any user connected to sql server externally his login will be authenticated in master DB.
  • It stores other server objects linked servers, endpoint set till SQL 2000 we store all metadata (data about data) in tables. If any binary file effect enter data will be lost

SQL Server Model Database

Model database will act as a template in creating new user databases. How many files we want to create and how many MDF and LDF, what its initial size, the path of binary files all this information we call as a template.

Every new database acquires properties from the model database. New DB goes to model database and gets those properties. If we create any table all the new databases which we create from now on contain the same table.

MSDB

Msdb stores all scheduling and automation information. In SQL server we are doing automation in the form of job, sql server agent service will be responsible for this.

All sql jobs information will be stored in sysjobs, sysschedules, and sys steps tables inside msdb.

SQL Server agent service will read msdb schedule tables, based on this it will start and stop the tasks automatically.

History-related information like backup, restoration log shipping history will be stored.

SQL Server Temp DB

Temporary database where all temporary objects will be created for performing sql server internal operations.

Temporary objects that are created by sql server internal operations such as temporary tables, temporary stored procedures etc.

Rows versions that are generated by data modifications transaction in database that uses read-committed (or) snapshot isolation stores here.

All the transformation, internal calculations, conditions sorting operations will be performed in Tem db.

Whenever sql server started Tempdb will be deleted and new copy will be created.

SQL Server Resource DB

Resource db is hidden database we can see only mdf and ldf physically. It stores where we keep sql binaries files.

It provides high security to metadata, it contains all system objects such as system objects physically stores.

Resource db makes upgradation to new sql server veron easier and faster. In earlier versions upgrading will delete and create system objects. Now resource database will contain all system objects we take copy of resource database mdf and ldf and completes upgrading easily.

Creating Databases in SQL Server

Open sql server 2012 →  connect to management studio →Select sql server instance → go to database right click → new database

General:

Database Name : SQL test 2012
Owner : SA
Logical Name: Sql test 2012 – data, Sql test – Log
Initial Size : 1 GB
Autogrowth : 500 MB
Path : E:\Database\sql test.mdf .Ldf

Database name: it provide name to database.

Owner shows default, if we want we can change.

Logical Name is used to refer the physical file in all T-sql statements.

Initial Size will acquire from model db properties we can change as per our requirement. What is the size of database while creating.

Autogrowth means once it reaches the initial how much my database size can increase it will add size.

Path, actually storage location of database, where my database is going to store(location).

SQL Server Database Properties

We require minimum 2 files to create a database 1 mdf and 1 Ldf.

There are 5 properties required to create a database

  1. Name
  2. Filename(path)
  3. Initial Size
  4. Maximum Size
  5. File Growth

Name, what is the database name that we are going to provide for new user database. Logical Mdf and logical Ldf will use for Administrative purpose.

File name, complete path where database is going to store.

Initial Size, we have estimate how much the database size initially we need. We have to specify the max limit and autogrowth means once it reaches the initial size automatically how much size it has to increase we can provide it either in MB (OR) Percentage.

To create database using T-Sql

Create database sql db 2012

It will create new database with default model properties.

We can specify the properties through T-Sql

Create database sql db 2012

On

(
Name = sqldb2012_data
Filename = ‘E:\data\sqldb2012_data.mdf’
Size = 10 GB
Max Size = 500 GB
File Growth = 5GB
)
Log on
(
Name = ‘sqldb2012_log’
Filename = ‘E:\data\sqldb2012_data.ldf’
Size = 1 GB
Max Size = 500 GB
File Growth = 500 MB
)

Rename a file name:

Alter database sql db 2012 modify file name = sql db 2012 – filename =’D:\sqldb_data.mdf’

When database is online we cannot more mdf and ldf. It may lead to database crash.

After creating database, verify properties

Files tab → shows how many Mdf and Ldf are there in database. We have add option in GUI we can add more files.

File group → By default we have only 1 file group as primary. We can group multiple files under one file group

Options

Collation setting →  Latin 1 – General

Compatibility Level → On which version sql server developed show here

For sql server 2012 it is 110

#Ref. Ram Mohan & Rama Krishna notes

Below topics further included in this series