3. Creation and Managing SQL Server Databases

Topics In This Module.

Databases.
Types of databases and Its Properties
Database Architecture

Page
Extent
Files
File Groups

Modifying Database
Modifying Files and File Parameters
File Movement
Database Corruption
Log File Architecture
Recovery Models

***============================================================================***

Once after instance is installed and connection to instance is successful, we see some by-default databases in instance which are called system databases, and per requirement the user defined databases t load data to use it as requirement.

Databases

Total Number of databases that can be created in an instance are 32767.

S.no	Databases	Maximum Number in instance
1	System Databases	5
2	User Databases	32762
3	Total Databases (system + User)	32767

Note: When the Services are started or restarted, below is the order of starting databases

Master Database à Resource Database à MSDB Database à User Databases à
Model Database à Temp Database.

--------------------------------------------------------------------------------------------------------------------------------------------------------

The database is a combination of some database objects listed below

Types of Databases

There are two types of databases they are

System Databases
User Databases

System Databases

Once Instance is Installed and connection is established we see some default databases which are used to run the SQL Server known as system databases.
There are five system databases which are default database already seen once instance is installed they are:

Master database --- 1
Model database --- 3
MSDB database --- 4
Temp database --- 2
Resource database(read-only) --- 32,767

User Databases

The Databases which are used to run business or used to run applications are called user databases

--------------------------------------------------------------------------------------------------------------------------------------------------------

Database properties

Below are the properties of a database:

Modes of database (Online, Offline, Suspended, Restoring…etc.).
User mode of database (Single_user, Multi_user, Restricted_user).
Owner of a database.
Compatibility of a database.
Recovery model of a database (Simple, Full, Bulk Logged)
Collation setting of databases.

--------------------------------------------------------------------------------------------------------------------------------------------------------

Database objects:

Each database is a collection of database objects the list of database objects they are:

User/Login: a login is an authentication for an instance, a user is a authorization to a database instance
Role: An object that contains set of permissions for objects within database, these roles/permissions are granted to users by admin to access the database objects
Schema: Schema is something that can hold a collection of objects, kind of container, objects that can be grouped logically and assigned to a schema on creation are tables, indexes, functions .. etc
Table: Objects that can hold relational data.
Constraints: These are rules for the data columns in a table like primary key, foreign key, default, check constraints key.
Index: This is a structure that can be used to speed up accessing data in a table
Statics: Statistics are a set of data that describes the data distribution and cardinality of the columns in a table.
Views: A view is basically a stored query. Sometimes a query can be so complex it is created a view so that other connections can just write "SELECT * FROM view".
Note that the actually result set from the query is not stored/cached (this is done for materialized views), only the query text, so each call to the view results in the entire query being executed.
Stored Procedures/Functions

--------------------------------------------------------------------------------------------------------------------------------------------------------

System Databases and Its properties:

Master database

Master database maintain the information in the tables as mentioned they are

All the databases information in -- sys. databases
All Logins information in -- sys. logins
Linked server details.
All the User processes in -- sys. processes, Sp_who or Sp_who2
All the Engine information.

Below is the information regarding master database:

One - 1 is the ID of master database.
Recovery model is SIMPLE, Though the recovery model is simple we cannot take diff backup of master database.
We can change the recovery model of this database
Database owner cannot be changed for this database
To this database files or file groups cannot be added.
If mdf or ldf files of master db is corrupted we cannot start the SQL server. reason below
This is also called as startup database or root database.

-> What happens when SQL Server is started??
The startup parameter of engine services contains the file location and names of mdf and ldf files of master database. using which the master database starts, master database contains all the other databases details so other databases start in the below order.

Master ->Resource Db -> Model -> MSDB ->User Db -> Temp Db .

------------------------------------------------------

MSDB database

MSDB database maintain the below information they are:SQL Server agent specific Information

Four - 4 is the ID of MSDB database.
Recovery model of MSDB is SIMPLE, can take full and diff backup of MSDB.
We can change the recovery model of this database.
Database owner cannot be changed for this database.
To this database files or file groups cannot be added.
If mdf or ldf files of MSDB database is corrupted we can start SQL server but SQL agent does not work and also related features of agent does not work.

------------------------------------------------------

Model Databases

This is a template database whenever user databases are created the properties of this model db is taken by the user databases, that's the reason for recovery model.

Three - 3 is the ID of model databases
The recovery model of Model is FULL, hence we can take full, diff , Tlog backup of this database.
We can change the recovery model of this database.
Database owner cannot be changed for this database.
To this database files or file groups can be added.
This is also called as template database.

------------------------------------------------------

Temp Databases

Below is the information regarding Temp database:

Two - 2 is the ID of Temp database.
The recovery model of temp db is SIMPLE, But we cannot take backup of temp database.
We cannot change the recovery model of this database
Database owner cannot be changed for this
To this database files can be added but we cannot add file groups to this database.
When ever the SQL Services are restarted this db is recreated.

=> Temporary working space for a SQL server and a user session is temp db
=> When user connect a session is created memory is allocated in this and when disconnected this is unallocated.

------------------------------------------------------

Resource databases

Below is the information regarding resource database

32,767 is the ID of resources database. Max no of database is also same.
This db is invisible and read only database.
the recovery model is SIMPLE, can take full and diff backups of database.
We can change the recovery model of this database
This database is added from 2005, all system and default object are segregated from master database and kept as separate as resource db, as this is read -only database they cannot be changed
Database owner cannot be changed for this

------------------------------------------------------

User databases

Below is the information regarding user database:

5 to 32,766 will be the ID of user databases, 32,762 number of user database can be created.
The recovery model depends on the business, can take full, diff and Tlog backups of all user database.
We can change the recovery model of this database to simple or full or Bulk.
Database owner can be changed to any for this database.
To these user database files or file groups can be added

--------------------------------------------------------------------------------------------------------------------------------------------------------

Database Creation

Usually when database is created the features of databases are taken from model database features, Using the below command

CREATE DATABASE <Database name>;

To Overwrite the features, present in model database using the option in SQL as below

CREATE DATABASE <Database name>

ON PRIMARY (NAME = 'abcd', FILENAME = 'F:\SQL2012\Data\abcd.mdf’, SIZE = 4096KB, FILEGROWTH = 1024KB)

LOG ON (NAME = 'abcd_log', FILENAME = 'L:\SQL2012\Log\abcd_log. Ldf’, SIZE = 2048KB, FILEGROWTH = 10%)

To create a database two files are mandatory they are:

Primary - mdf (Master Data File)
Log - ldf (Low Data File)

These Data files are present in File groups so let’s see what are file groups? -- Check in File groups section below.

-------------------------------------------------------------------------------------------------------------------------------------------------------

Properties of database

The below are the properties of a database they are:

Mode of database.
User mode of database.
Owner of a database.
Compatibility of a database.
Recovery model of a database.
Collation setting of databases.

Altering/Managing Database

File groups or Files can be added to a database
Renaming of a file or database
Modifying the file properties or modifying the database and its properties.
Changing Modes of database
Dropping the database or deleting the files.

-------------------------------------------------------------------------------------------------------------------------------------------------------

Modifying the database Properties

Mode and its types of a database.

There are different modes of a database they are

Offline mode: Not Accessible to anyone.
Restoring state.
Recovering state: When Engine services is started or restarted engine itself performs the recovery with the left of data in log file.
Standby mode: Used in Log shipping.
Online mode: Accessible to everyone.

Read - only state: Accessible but in read only.
Read - write state: Accessible to everyone.

Suspect mode: Not Accessible to anyone; When db or its files are corrupted the db goes in to suspect mode.
Emergency mode: Accessible to all DBA's and in read only state.

ALTER DATABASE <Db Name> SET MODE <WITH OPTIONAL OPTIONS>

Changing User modes in a database.
Changing owner of a database.
Changing compatibility of a database.
Changing recovery model of a database.
Changing collation setting of databases.

==================================================================================

File Groups

File groups are the logical grouping of 1 or More data files is called a file group.

We can create Total 32767 number of filegroups in an instance.

There are two types of file groups they are

Primary File group.
User defined File group.

Primary File Group

This is by default and mandatory file groups.
These are created when a database is created.
These primary file groups cannot be kept as read only.

User Defined File Group

These are optionally created file groups,
These are created whenever there is a requirement comes on.
These file groups can be kept as read only.

-------------------------------------------------------------------------------------------------------------------------------------------------------

File Group Properties

Below are the properties of file groups:

Default: To make the file groups default we use this option. (Only one filegroup can be default at a time), if a File group is kept default all the objects created will go into files present in that file groups. Which is kept in default mode.
Read only: To keep the filegroups in read only mode we use this option.

-------------------------------------------------------------------------------------------------------------------------------------------------------

Uses of File groups

Below are the uses of filegroups:

To Mark bunch of files in read-only at a time we will group them and can the file group in read only
Data Partition requires multiple file groups.
Backup and restore of filegroup is faster than database backup and restore.
I/O Optimizations for splitting files into different disks.

-------------------------------------------------------------------------------------------------------------------------------------------------------

These are the mandatory things which are used to create a database they are

a data file - primary - mdf - max size16Tb
a log file - log - ldf – max size 2Tb

Each file Consists of the below properties they are

Name: Logical name of a file.
Filename: The physical path of a file is the filename.
Size: Initial size of a file
Max size: Maximum size of a file is taken by this option. if not mentioned its unlimited.
File Growth: How much size of a file can be grown it can be in percentage or directly size.

Recommendations:

Data file and log file physical path should be different in different disks.
Maximum size (Max_Size) of logfile should be 25% of max size of datafile.

==================================================================================

Files

There are three types of files in usual they are:

Data file

Primary file – should be single and a mandatory
Secondary file – can be many and optional

Log file

Log file and Virtual logs

Data Files

Below are the details that a datafile maintains

Maintains system tables and its information
Objects (Stored procedures, Indexes, Triggers, Views, Tables)
Points to the other sub sequent ndf or ldf file details.

Max size of a data file can be 16Tb
Number of files can be created per file group is 32767

==> Primary Files

It’s the default file and mandatory file for a database to be created or to present.
Cannot be deleted
Only one primary file can be created for a database.
These are the part of primary file group.
mdf - Main Data File.

==> Secondary Files

These files are optional for a database.
Can be deleted.
There can 0 or 1 or many secondary file depends of file groups.
These can be part of primary or user defined file groups.
ndf - Next Data File.

Log Files

Log files maintains the transactions or transactional records happening to a database
---> WAL – Write Ahead Logging Concept
Max Size of a log file can be 2Tb.

It’s the default file and mandatory file for a database to be created or to present.
Cannot be delete.
We can create n number of log files for a database.
file group is not present here in log files
ldf - low Data File.

-------------------------------------------------------------------------------------------------------------------------------------------------------

Modifying the files and file parameters:

Moving File/renaming physical file details/steps

Get the new path/new name details.
Take database to offline so that its un operational.
Use master database and add the new path/new name in system catalog view.
Move the physical file to new location. Or rename the file using modify file option.
Take the database to online

Note: If it’s a master database update the new path in startup parameters at services, and bring services down and move the physical file to new path, and bring services up.

File Size modifying:

Modifying the Auto growth:

-------------------------------------------------------------------------------------------------------------------------------------------------------

Extents

A logical partitioning of data files is an extent can also said as collection of physical pages is called extent.

The Extents are used to manage a file efficiently.

They are two types of extents they are:

Uniform Extents - Pages are owned by single objects.
Mixed Extents - pages can be owned by Multiple objects or shared space for different objects.

A new page is created/allocated in mixed extents the sub sequent pages are created in uniform extents.

To Make the space allocation efficiently the page is allocated in mixed extent first and later if the data size goes beyond 8KB then a new page will be created and its created in uniform extents.

Example:

If a table has some data which occupies:

2pages in case1, 9pages in case2 and 8pages in case3

Case - 1: All 2 pages will be in Mixed extent.
Case - 2: 8Pages will be in Uniform extent and 1 page will be in mixed extent.
Case - 3: All 8 pages will be in uniform extent. will be moved from mixed extent to uniform.

==================================================================================

Page

This is the smallest partition of a file, page is the fundamental data storage in SQL server.
single page size is 8Kb = 8*1024 Bytes = 8192 Bytes
The Size of data part is 8060 Bytes and the rest is header + row off set

A page contains three parts they are:

Header: Page information - 96 bytes
Data: Normal Information - 8060 Bytes
Row off set: Rows information - 36 bytes

There are 9 different types of pages they are:

Data page.

Data records in heap.
Clustered index leaf-level
Location can be random

Index page

Non-clustered index
Non-leave-level clustered index
Location can be random

BLOB Page

Text or Image type of data is stored here

GAM Page -- Global Allocation Map

Records which extents are allocated and which extents are unallocated or simply said tracks allocation of extents.
Maintains 64000 extent details or 4GB of data
One bit for each extent, if the bit is 1 - means the extent is free/unallocated, otherwise means the extent is allocated (not necessarily full).
The first GAM page in each file is page 2

SGAM Page -- Shared Global Allocation Map

Maintains which extents is currently used as mixed extents, having space for at least 1-page track allocation of shared/mixed extents.
Maintains 64000 extent details or 4GB of data.
One bit for each extent, if the bit is 1 - means the extent is allocated but has free space, otherwise means the extent is full.
The first SGAM page in each file is page 3

PFS Page -- Page Free Space

Maintains data of pages which has free space or keeps track of free space of pages
The first PFS is page 1 in each file.

IAM Page -- Index Allocation Map.

It Maintains the meta data of all pages which is similar to index for a book, Extent allocation in a GAM interval for an index or heap table.
Location can be random.

DCM Page -- Differential Changed Map

All pages and its extents information which are modified post full backup.
Extents in GAM interval have changed since last full or differential backup
The first Differential Changed Page is page 6 in each file

BCM Page -- Bulk Change Map

Extents in GAM interval modified by bulk operations since last backup
Between two log backups any pages or extents are modified with any bulk operations those modified extents are saved here.
The first Bulk Change Map page is page 7 in each file.

Bit 1 - Modified
Bit 0 - Not Modified by any Bulk Operation.

-------------------------------------------------------------------------------------------------------------------------------------------------------

File Movement

“Note: Databases Files can be moved only when the databases are offline.”

Update the new physical path in system catalog which is stored in master database using below command or using GUI.

SELECT name, physical_name FROM sys. database_files;

#Above is Used to get the preset name and File name like logical name and Physical name.

#Using below two commands database file details are updated in system catalog.

ALTER DATABASE <Database name>

MODIFY FILE (NAME = <Logical Name>, FILENAME = 'E:\Filename.mdf');

ALTER DATABASE <Database name>

MODIFY FILE (NAME = <Logical Name>, FILENAME = 'E:\Filename.ldf');

Now take the database Offline, and then move the files to a new path location as updated above,
Bring the database Online, after coping the file is completed.
Perform health checks.

“Note: For All the system database file moment we cannot keep them offline so need to stop the services instead of keeping them offline, and for all the user databases file moment we can keep them directly into Offline.”

-------------------------------------------------------------------------------------------------------------------------------------------------------

Corruption

User Database corruption:

A database gets corrupted only when the mdf or ldf gets corrupted or pages insides it gets corrupted then we can say that the database is corrupted when a user database corrupted it goes into suspended mode or when any pages gets corrupted integrity of a database gets failed, which also returns which page is corrupted.

When a database is corrupted we may either repair it using DBCC commands, by starting the database in single user mode, and emergency mode.

This repair can be done with data loss or without data loss,

If we are not able to repair the database, then restore the database with latest backup, if it’s a user database.

System Database corruption:

If a system database (master or model or temp db) get corrupted then the instance does not start.

If temp db get corrupted then just restart the instance, which will recreate the temp database along with files so the issues get resolved, in this scenario if temp db gets corrupted the instance get start but the instance goes into hung state, where no session can be connected to instance.

If Master or model gets corrupted the instance does not even start.

If Master corrupts then we cannot start instance, instead it throughs an error with error number 3411, to know whether the master is fully corrupted or partially corrupted, then start the instance with single user mode, and master only recovery to do it keep the startup parameters -m = single user mode, -t3608 = enabling trace flag, after doing this the if the instance starts then the master database is partially corrupted, else it is fully corrupted.

For Partially corrupted master database:

If backup is present

If backup is not present rebuild the same SQL server version with only master database and replace the corrupted files and add the logins and all info that need to present in master database

For Fully corrupted master database

If backup is present restore it in other SQL server instance which has same version, and now replace the corrupted files with the newly restored files and then remove -m and trace flags, and start the SQL instance.

If backup is not present rebuild the same SQL server version with only master database and replace the corrupted files and add the logins and all info that need to present in master database

If Model database gets corrupted then we cannot start instance, instead it throughs an error with error number 1814(945),

If MSDB databases gets corrupted then the SQL server starts but the SQL server Agent does not start.

In this scenario if backup of this database is present, restore the database in other same version of instance and replace the corrupted mdf and ldf files with this restored file,

If backups are not present then reinstall the msdb database with the script present in the install location, which will create a new msdb database in the same instance.

==================================================================================

Log File Architecture: (Wrap around files)

All transactions related information is stored in Log file its a temporary file which will be cleared when a log backup is triggered. if it has all committed transactions.
Log files are used to maintain consistency and Data recovery of SQL server logs.
If Transaction is pending (Uncommitted) they are rollbacked, If Transaction is completed(committed) they are roll forwarded and write to data file.
When services are started, SQL engine will automatically read these log files and perform auto recovery by roll forward and roll back.
When the log file is about to growth it each time checks all log files whether they are free to use or occupied by any transactions.
A Log file is logically divided into n number of virtual logs;
The number of virtual logs and size of each virtual log is based on SQL Server Engine.
Each Virtual logs (VL's) has a Sequence number called Log Sequence Number - LSN.

-------------------------------------------------------------------------------------------------------------------------------------------------------

Check point

It is a SQL engine process which will be occurred for every 1min, when this occurs the data in memory or buffer cache is written to disk.

When this check point occurs two things happen they are:

Hardening: Transaction from log buffer are moved to log file.
Committed transactions moves to datafile from buffer.

When Does this check point occurs?

SQL server by default triggers check point for every one minute.
Whenever a backup is triggered this check point occurs.

-------------------------------------------------------------------------------------------------------------------------------------------------------

Dirty Pages

Pages modified by operations, which are in buffer but not written to disk and waiting for the check point to occur are called dirty pages.

NOTE: When a log file is set to auto growth, the file size does not grow automatically instead it checks each and every virtual log whether its free (used and space truncated after log backup) or not, if any vlog is free then its reused instead of extending.

Whenever the log backup is taken the logs/virtual logs are truncated post backup only if it has committed transactions,

Example:

if a log file has 10 virtual logs 1,2,3,4,5,6,7,8,9,0

Before log backup -- 4 and 8 VL’s has some active transactions, rest all are free (committed transactions)

After log backup -- when we see the free space available it shows free space for all 1,2,3,5,6,7,9,0 VL’s but when we try to shirk the log file we will be able to only shrink the space occupied by 1,2,3,9,0 VL’s occupied.

-------------------------------------------------------------------------------------------------------------------------------------------------------

Hardening

-------------------------------------------------------------------------------------------------------------------------------------------------------

Lazy Writer

-------------------------------------------------------------------------------------------------------------------------------------------------------

Eager Writer

==================================================================================

Recovery Model

Recovery Models decides what operations need to be recorded in log file, Which Backups can be taken for a database is also decided by recovery model.

Database restores depends upon Backups -> Database Backup depends upon Recovery models -> Recovery models.

Database recovery models are of three types they are:

Full Recovery Mode.
Simple Recovery Mode.
Bulk Logged Mode.

Recovery Modes of the system databases are:

Databases	Recovery Model	Backups
Master Database	Simple	Only Full backup can be taken.
Resources Database	Simple	Only Full backup can be taken.
MSDB Database	Simple	Only Full backup can be taken.
User Databases	Full	Backup can be taken as per recovery model
Model Database	Full	Backup can be taken as per recovery model
Temp Database	Simple/Full	Backup cannot be taken

--> As temp databases are also created from model databases it may be full recovery mode some times.

--> By Default, User Database will be in FULL recovery model, as one of the system databases MODELS is in FULL recovery mode as it’s a template database.

-->To change the recovery model of a databases we use the below query:

ALTER DATABASE <database name> SET RECOVERY <model name>;

If a database is in simple recovery mode then we can take only full backup and Differential backups.
If a database is in Full/Bulk Logged recovery mode then we can take all Full backup, Differential, Transactional log Backups.

What happens if a database is in simple recovery mode?

Any type of Operations (simple or Bulk Operations) transactions are recorded in log file, but whenever a check point occurs all committed/inactive transactions are truncated from log file, without any backup

Hence No Recovery is possible from log files as they will be empty almost and if it has data they are uncommitted hence they will be roll forwarded.
Operations in this mode is faster and also log file is free almost every time, as committed transaction logs get truncated at each checkpoint

=> It is used for Non-Prod servers, OLAP servers, Reporting servers. as recovery is not much used.

What happens if a database is in Bulk logged mode?

Log files will get truncated whenever the log backup is taken, All the active transactions and inactive transactions are backed up and the all inactive transaction are truncated.

For simple Operations maintains complete transactions logs, For Bulk Operations it only maintains partial information.
No Recovery is possible from log files for Bulk operations, Recovery is Possible from log files for simple operations.
Operations are faster than simple mode

=> It is mostly used in data ware house environment.

What happens if a database is in Full recovery mode?

All type of Operations is logged in log file, when we take log backup All the active transactions and inactive transactions are backed up and the all inactive transaction are truncated.

Operation are not as fast as simple but no data loss.

Microsoft SQL Server Database Administration.