A database administrator is a person responsible for the database design, implementation, maintenance and repair of the database. The main goal of DBA is to keep the database server always up and make it available to users. In case of any failures, DBA should minimize the Database by implementing powerful backup and restoring technique.
As a DBA we have to perform these tasks,
SQL Server follows client-server architecture. Whenever the user performs any action on client machine, it converts in the form of a query. This query moves from client to server in the form of network packets using protocols for connection and communication between the source and destination servers.
Relational Engine (Query Processor) prepares the execution plan and hand over to the storage Engine. Storage Engine It is a central repository, responsible in the execution of query using execution plan, the response sent to the user.
Buffer pool is another important component contains plan cache and data cache which is used for query execution.
SQL OS is a core to SQL Server architecture, used for scheduling, I/O completion, Memory Management and resource management. It is a thin layer between windows OS and SQL server.
SNI is a Protocol layer that establishes the network connection between the client and the server. It uses TCP/IP protocol to send queries in the form of TOS packets.
Command Parser first checks for syntax errors, then it generates query plan (or) find an existing plan query plan contains detail steps how query is going to execute. Command parser checks whether a plan already exists in plan cache of buffer pool. If finds plan passes to query executor for execution. If it does not find then query passes to optimizer.
Optimizer prepares query plans for one query in that SQL server select best plan based on response time, the query plan passes to query executor for execution.
Query executor requires data to read the query plan it passes to access methods of storage engine.
Access methods require data to complete the query it asks buffer managers to provide data page. Once it receives required data, the query results passes back to the relational engine and there to the user.
Buffer manager checks in the data cache of the buffer pool to see if it has the page already in cache memory. If page exists, it passes results to Access methods. If not exists it pulls required pages from mdf data file put in data cache and passes it back to Access methods.
Part of SQL servers buffer pool used to store previously executed execution plans in case they are needed later.
Data cache is largest part of buffer pool. Every data page that is read from disk is written a copy here before using.Under memory pressure these pages are flushed from cache using LRU (Least recently used) policy.
SQL Server Network Interface (SNI) is a protocol layer that establishes the network connection between the client and the server. SQL Server supports 4 protocols.
Shared Memory: It is default protocol used to connect client and SQL Server on the same machine
Named Pipes: Client and server will connect within a LAN. It has certain limitations.
TCP/IP: TCP/IP is the most used protocol for SQL Server client establishes a connection with SQL server using an IP Address and a port number 1433 we can access the databases using the internet hence there are no boundaries for this protocol.
VIA (Virtual Interface Adapter): VIA is a wireless internet protocol for connecting clients and servers within a certain range.
To establish a secure SQL connection we need a port number along with the protocol. The default port number for TCP/IP protocol is 1433 we can change the port number from configuration manager — SQL server network configure — protocols we can change.
#Ref. Ram Mohan & Rama Krishna notes