Internal Structure of Data File

Data files contain data and objects such as tables, indexes, stored procedures, and views.
Storage space allocated to a database is divided into pages that are contiguously numbered from 0 to N. That means data file is a large array of pages.
When Microsoft SQL Server starts expanding the database file from its default size, the newly created pages are numbered starting from the (last highest page number in the file)+1.
Similarly, when SQL server shrinks the file it removes pages descendingly (starting with the highest page number) from the database file.
Disk I/O operations (read/write) are performed at the page level.

Basically, a SQL Server data file has the following basic structure.
SQL server refer the first page in the data file as page number 0.In all the data file first 9  pages (till page number 8) are in same order as shown below.In the primary data file, the 10th page (Page number 9) will be the boot page which store the metadata about the database.

Extents Architecture

An extent is a group of eight physically consecutive pages in a database data file. One page is 8 KB, therefore one extent is 64 KB. This means SQL Server databases have 16 extents per megabyte.

There are two types of extents in SQL Sever:

Uniform Extents:
All eight physically contiguous pages belong and can be used only by a single object.(table, index, ...) This is a uniform extent.

Page Architecture

SQL server organizes and stores records in smaller units of data, known as pages.
The size of the page in SQL server is  8 KB. This means SQL Server databases have 128 pages per megabyte.
Page mainly divided as  Page Header, Data Row, and Row Offset.
A page can have maximum storage of 8060 bytes (ie., (Total Page Capacity 8192)-Page Header-Row Offset)=8192-96-36).

Database Architecture

Database is a systematic collection of data. Databases support storage and  manipulation of data. Databases make data management easy.
Microsoft SQL Server databases contain two primary file types required for proper functioning of a OS. They are: Data file and a Log file.

Prerequisite to Config Database Mail

Step 1: Verify SQL Server Agent Service is Running:

Messages in Database Mail are sent by the SQL Server Agent. If the Agent is not running, the messages will be accumulated in the msdb database and sent when the Agent service gets started again.
Use the master..xp_servicecontrol system stored procedure to check the status of the SQL Server Agent process. If the Agent is stopped, you will want to start it in order to send messages through Database Mail.
USE master
Go
EXEC xp_servicecontrol N'QueryState', N'SQLServerAGENT';
Go

Database Mail Architecture:

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version.
The main purpose of Database Mail is sending e-mail messages from the SQL Server Database Engine to users. We can send a text message, query result, failure alerts, file as attachment.
The database mail can be used to notify users or administrators regarding events raised in SQL Server.
To send Database Mail, you must be a member of the Sys admin <or >DatabaseMailUserRole database role in the msdb database.

How to configure the Linked Server

Step1.In SQL Server Management Studio, open Object Explorer, expand Server Objects, right-click Linked Servers, and then click New Linked Server.

Step2.In the new window on [General] page, we need to set several parameters as below:

How Linked Server Works

Linked Servers allows us to submit a TSQL statement on one SQL Server instance, which retrieves data[Table\View] from a different SQL Server instances.
In fact, linked server can be used to join data from multiple SQL Server instances using a single TSQL statement.
When you have databases on multiple SQL Server instances, you might find it useful to use linked servers in your application to retrieve data from more than one instance.
By using a linked server your application will only need to connect to one SQL Server instance to retrieve data from multiple SQL Server instances.
On that single SQL Server instance, we would define linked servers so your application could retrieve data from the databases that reside on a different SQL Server instance.

Cross Database Ownership Chaining

Cross Database Ownership Chaining is a mechanism of Microsoft SQL Server to grant a database 'A' user access to objects in databases B where he doesn’t explicitly got access-permissions to.
Note: By default Cross-database ownership chaining is turned ON for the master, temp db, and msdb system databases. You cannot change cross-database ownership chaining for the master, msdb, model, and tempdb system databases.
 By default you’re already using Cross Database Ownership Chaining on all of your SQL Servers. You surely already used a function or table of the master\temp-database. If you take a look at the security-configuration of the master-\temp database you will see that you’re mostly not explicitly granted access. That’s because Cross Database Ownership Chaining is applied in this operation.

SQL Server Architecture

How does the transaction log record a transaction\ How does the data file records a transaction.\How SQL query goes through.

Following is the process which a SQL Query goes through.

Transaction Log Management

ADD New Log File:
If you need to enlarge your transaction log, one method you can use is to add a file to the log.
You can do this by using the ADD LOG FILE clause of the ALTER DATABASE statement.

Shrinking Transaction Log

In order to truncate the transaction log, you must first back up the log. The database engine then automatically truncates the inactive records. However, truncating the log doesn’t reduce its size. Instead, you must shrink the log file, which removes one or more inactive virtual log files.

Transaction Log Truncation:

The process in SQL Server  "Log truncation" deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log.
If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files.
The way this truncation happens is dependent on the recovery model that was selected for your database.

Transaction Log Roles in SQL Server

What is logged in the transaction log?


SQL Server logs every event in a database to a more or lesser extent.
•When a transaction begins or ends
•Every update , insert or delete
•Drop and creation of tables and indexes.

Transaction Log Architecture

A transaction log is a physical file in which SQL server stores the details of all transactions and data modifications performed on the database.
A database can have one or more log file. In general there will be only one log file as there is no performance improvement by having multiple log file.
Writing can occur only into one file at the time, so parallel I/O operations are not possible.
Having multiple transaction log files is recommended only if the first one is full or the disk drive is running out of space.