SQL SERVER ARCHITECTURE

           SQL SERVER ARCHITECTURE

                                                                 BY ANAND GUPTA

Sql server is basically start with 1987 with Sybase and first version is 6.0 then 6.5 ->7.0 -> 2000 and then 2005 and many more with new feature.

Now, this is basic architecture which is define by Microsoft and is very easy when you already learn oracle or another database because every database now a days follow RDBMS which is known as relational database management system. The above 1^st half part is known as relational engine and below part is known as storage engine.

FOLLOW THESE 8 POINTS AND LEARN ARCHITECTURE LIKE PRO J

SQL SERVER RELATIONAL ENGINE -

Now let’s start with 1 components which is SQL SERVER NETWORK INTERFACE

1) Now this is server where we get the user request or you can say this is client machine.

The SQL Server Network Interface (SNI) is a protocol layer that establishes the network connection between the client and the server. It consists of a set of APIs that are used by both the database engine and the SQL Server Native Client i.e SNAC

FOR MORE DETAILS J

(SQL Server has support for the following protocols:

➤
Shared memory: Simple and fast, shared memory is the default protocol used to connect from a client running on the same computer as SQL Server. It can only be used locally, has no configurable properties, and is always tried first when connecting from the local machine means The limitation is that the client applications must reside on the same machine where the SQL Server is installed.

➤
TCP/IP: TCP/IP is the most commonly / the most popular and common protocol widely used throughout the industry today. It communicates across interconnected networks and is a standard for routing network traffics and offers advanced security features. It enables you to connect to SQL Server by specifying an IP address and a port number. Typically, this happens automatically when you specify an instance to connect to. Your internal name resolution system resolves the hostname part of the instance name to an IP address, and either you connect to the default TCP port number 1433 for default instances or the SQL Browser service will find the right port for a named instance using UDP port 1434.

➤
Named Pipes: This protocol can be used when your application and SQL Server resides on a local area network. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first) or remote (on a networked computer). TCP/IP and Named Pipes are comparable protocols in the architectures in which they can be used. Named Pipes was developed for local area networks (LANs) but it can be inefficient across slower networks such as wide area networks (WANs). To use Named Pipes you first need to enable it in SQL Server Configuration Manager (if you’ll be connecting remotely) and then create a SQL Server alias, which connects to the server using Named Pipes as the protocol. Named Pipes uses TCP port 445, so ensure that the port is open on any firewalls between the two computers, including the Windows Firewall.)

2)Whereas TDS is the communication protocols which transfer the information to the SNI protocol layer which is developed by Sybase and later taken by Microsoft.(remember default port number for 1433 else you can change it. )

3) Now, when the data come into the TDS form SNI protocol layer will unpack that data and transfer that data by language event (or you can say it’s a adhoc query or language event is set of rules which commonly known as Collation in Sql server) whereas Collation refers to a set of rules that determine how data is sorted and compared then it go to cmd parser.

4) Now CMD parser play a vital in Sql server architecture basically it do 2 tasks-

>firstly it check if there is some grammar mistakes or syntax error it will revert back to protocol layer and then back to client server in TDS format. (NOTE- there are approx. 50k error in Sql server even if you want to add some error with your own you can also able to do that J).

>Now cmd parser will analyze the Sql query which is send by user and it will create Sql server execution plan and this execution plan is known as the query tree. For Query tree we will say that it contain all the steps on which how the Sql query is executed and all possible way to execute the query.

 5) Now the query goes into the optimizer which will choose the best plan to execute that query which the minimum cost or you can say minimum resource utilization and then it goes to Query executor which you can understand by name that it executes the query.

NOTE- (component like cmd parser, optimizer and query executor are known as Sql server OS).

SQL SERVER STORAGE ENGINE –

6) Now another and important component in storage engine is access method. From there Sql server check whether if query is select or any other query.

If it is select query then it goes to BUFFER MANAGER from which the database is extract from datafile or from data cache. But if query is not select query it will go to transaction manager for ex – insert or update query.

7) Now suppose if query is other than select query it will go to transaction manager. Now transaction manager will have 2 assistance you can say

                                                                        transaction manager

                                                        Transaction logs              Transaction locks

Now Transaction logs assistance will write every query change into the Transaction logs with proper sequence in it and Transaction locks will lock that table or column in which process is done for example – some update on salary where employee id is 100 and it will never release lock until data is not written into the datafile.

8) Now if query is select query then it will go directly to buffer manager

                                                                         Buffer Manager

                                                                                                                      

                                                        Plan cache                       Data Cache

Now Plan cache will store the plan which is send by query executer and make connection with Data Cache for processing data. Let us suppose if query is already executed it will match that plan send by query executor to buffer manager and buffer manager will check that plan in plan cache, if plan is found it will fetch output from data cache to buffer manager and then to query executor and then to protocol layer and again change in TDI format to client. Remember Sql server work in session layer J

DONT FORGET TO COMMENT !!