Introduction to SQL Server

Microsoft SQL Server is a Relational Database Management System(RDBMS) developed by Microsoft. It is designed to run on a central server, so that multiple users can access the same data simultaneously. Generally users access the database through an application.

Sql Server Components

1. Protocol Layer

   Protocol layer implements the external interface to SQL Server. TDS is an application layer protocol, that is used to transfer data between a database server and a client.

2. Data Storage

   The main unit of data storage is a database, which is a collection of data. The data in the SQL Server database is stored in primary data files with an extension .mdf and Secondary data files, with an extension .ndf extension are used to store optional metadata. Log files in SQL Server are recognized with the .ldf extension.

3. Buffer Management

   SQL Server buffers pages in RAM to minimize disc Input/Output. A 8 KB page could be buffered in-memory and the set of all pages currently buffered is called the buffer cache. On the basis of available memory, SQL Server decides how many pages will be cached in memory. The buffer cache is managed by the Buffer Manager.

4. Logging and Transaction

   SQL Server uses transaction to make sure that any operation either totally completes or is undone if fails, but never leaves the database in an intermediate state. Any changes made to a page will update the in-memory cache of the page and simultaneously all the operations performed will be written to a log, along with the transaction ID. Each log entry is recognized by an increasing Log Sequence Number (LSN) which makes sure that no event overwrites another event. SQL Server makes sure that the log will be written onto the disc before the actual page is written back.

5. Concurrency and locking

   when multiple users update the same data, or attempt to read data that is in the process of being changed by another user. In SQL Server we have two modes of concurrency control - pessimistic concurrency and optimistic concurrency. In pessimistic concurrency control, SQL Server controls concurrent access by using locks (shared or exclusive).

   In Optimistic concurrency control, a new version of a row is created whenever the divrow is updated. Both the versions of the row are stored and maintained into a system defined database Tempdb.

6. Data Retrieval

   Data retrieval from SQL Server is done using T-SQL. SQL Server also allows us to write stored procedures to query the data.

7. SQL CLR (Common Language Runtime)

   SQL Server 2005 also has a new component named SQL CLR via which it integrates with .NET Framework. When we write code for SQL CLR, data stored in SQL Server databases can be accessed by using the ADO.NET APIs like any other application that accesses SQL Server data.

Further Reading