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.