A Beginners Guide to SQL Server Architecture

SQL Server architecture is the part where all components work together and independently to process or execute services in a proper or defined manner so that SQL Server could work in a smooth way. The external interface to the SQL Server is created by the protocol layer and all operations on SQL Server are communicated via a defined format called TDS (Tabular Data Stream). It is an application layer protocol, which is used to transfer data between client and database server. There are some more basic points which make SQL as a strong database for users like Data Storage, Buffer management, Data retrieval & Concurrency and locking as defined.

Data Storage:

Data storage is a collection of tables with all types like primary types (integer, decimal, float, char, etc.), varchar, text and binary etc. SQL Server makes availability of the server statistics as virtual tables and views (DMVs-Dynamic Virtual Views). SQL Server database contains data objects like stored procedures, indexes, views, constraints etc. and transaction log file. Its database can contain a maximum 231 no of objects. The primary data in the database kept in the MDF file (.mdf) and secondary data are kept in the NDF file (.ndf) and log files are denoted by .ldf extension.

Buffer Management:

SQL Server buffer manager plays a vital role to minimize disc I/O as it buffers pages in RAM. It is possible to have 8 KB page in buffered memory and set of all buffered pages is called buffer cache. The pages in cached memory will depend on the memory, allocated to SQL Server. The number of cached pages is decided by the available buffer memory. The Buffer Manager manages the buffer cache like read or write, etc. All the operations are first copies to buffer cache. Read and Write operations are redirected to the in-memory copy rather the on-disc version. The page is updated by the buffer manager on disc only if the in-memory hasn’t been referenced for a while. Asynchronous I/O is used while writing pages to disc so that others do not have to wait for I/O operations. Each page is written with its checksum and computed further to ensure if it’s damaged or ruined while meantime running operations.

Concurrency and locking:

SQL Server allows its database to be used concurrently by multiple clients. So it needs to control concurrent database access to shared data. It provides two modes of concurrency control one is pessimistic and another one is optimistic concurrency.

SQL Server uses locks in case of pessimistic concurrency control mechanism. Locks can be either Exclusive or shared. Exclusive locks grant exclusive access to the data means no one can access the data as well as lock it. A shared lock is used when data is only about to read but cannot acquire an exclusive lock. The user has to wait for shared locks to be released. Locks can be applied to pages, tables and even in per row, etc. It can be applied at different levels of granularity and it depends on the per-database basis defined by the DBA.

The optimistic concurrency control mechanism is similar to the multiversion mechanism used in other databases. It allows a row to be created a new version whenever the row is updated and row is identified by ID of the transaction which is created by the version of the row. Both new and older versions stored and maintained, old versions are carried out to the system database and identified as Tempdb.

Data retrieval:

In SQL Server data is retrieved by querying for it and it is carried out by the variant of the SQL Server i.e. T-SQL. Query processor decides the sequence of steps that are essentials to retrieve requested data. The sequence of operations to execute a query is called query plan. SQL Server uses shortest possible plans to execute a query and called query optimization.

Conclusion:

I have explained above a concise description of SQL Server architecture like how data is transferred between Server and client, how the data storage is carried in the server, how much memory is required to bring the page to buffer, page read-write operations, concurrency management in which database locks are clarified briefly and last how SQL Server data is retrieved by querying. The further more details will be given to you very soon.