MS SQL Server- An Extremely Easy Way to Store and Retrieve Data

Microsoft SQL Server is a software product of Microsoft Corporation. It is a relational database management system for which the primary functionality is to store or retrieve data as and when requested by associated software applications. The associated applications could run on the same computer or another computer across the network.

Microsoft offers at least 12 different versions of MS SQL Server. It offer services to different workloads and different set of audiences be it a single-machine application or internet-facing application.

Architecture

All the operations that are invoked on SQL server are communicated through Tabular Data Stream (TDS). TDS is an application layer protocol, which transfers the data to/from a database server and client machines.

Data Storage

Data storage is a database which consists of collection of data tables. A database may also contain other objects apart from tables like stored procedures, views, transaction logs, indexes and constraints.  MS SQL supports many datatypes such as Integer, Float, Char, Decimal, Varchar, binary, text and others. It allows supports user-defined composite types.

Buffer Management

MS SQL server buffers the pages in RAM which helps to minimize the disc input/output. It has the capacity to buffer in-memory any page with 8KB size and the set of pages that are buffered at any given point of time is called the buffer cache. The number of pages that can be cached will be decided by the amount of memory available to SQL server. Buffer Manager Function manages the Buffer cache.  

Concurrency and Locking

SQL database server has the capability to serve multiple clients concurrently. To ensure the data integrity, it provides two types of concurrency control: Pessimistic concurrency and Optimistic concurrency. When the pessimistic concurrency is in use, the server controls the concurrent access. This control mechanism is carried out by locks which are either shared locks or exclusive locks. As the name indicates, exclusive lock grants exclusive user access to the data. When the exclusive lock is on, no one else can access the same data until the lock is held. Shared locks are used when multiple users are reading the same data. These locks can be applied at different levels like entire table, pages, or even per- row basis. SQL Server provides lightweight mutual exclusion solutions- latches and spinlocks.

SQL Server also accommodates optimistic concurrency control mechanism which facilitates creation of a new row whenever a row is updated without overwriting the existing row.

Data Retrieval and Programmability

Querying is the main mode to retrieve data from SQL Server database. The query specifies what needs to be retrieved declaratively from the database. Query processor carries this operation by identifying the sequence of steps needed to fulfil the required query request. These sequence of steps are names as query plan. SQL Server logic calculates the quickest possible method to fulfil the request and chooses the plan which takes less amount of time to finish the task. This logical functionality is called ‘Query Optimization’ which is done by the query processor.

Services

SQL Server provides numerous add-on services which is a value add along with the core relational database management system. They have their own API to control and interact. Services run as SQL Server Components or as Windows services.

• Service Broker


• Replication Services


• Transaction Replication


• Merge Replication


• Snapshot Replication

SQL Server also provides the following services: Analysis Services, Reporting Services, Notification Services, Integration Services and Full-text Search Services.

Microsoft Visual Studio

This provides support for data programming which comes along with MS SQL Server. Visual Studio is used to write and debug the code and the code will be executed by SQL CLR. Data Designer helps to graphically depict, create, view or even edit the database schemas. Visual Studio also allows to create queries either visually or through code.

SQL Server Management Studio

This is a Graphic User Interface (GUI) tool that comes with versions above SQL Server 2005. It is used to configure, manage and administer all the components within SQL Server.