SQL Server DBA & its Architecture [Part 1]

Database Administration

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.

DBA Responsibilities

As a DBA we have to perform these tasks,

  1. Maintaining the availability of database by minimizing the down time.
  2. Data recovery, we have to minimize the data loss in case of failures by implementing high availability.
  3. Provide high security in accessing the databases externally.
  4. Need to monitor the performance of server, implement various techniques to increases the performance
  5. Regularly monitor database growth, disk space sql server logs, Event viewer logs to avoid issues and to identify any bottlenecks.

DBA Roles / Daily Activities

  1. As part of the DBA team need to provide 24/7 production support to client and users.
  2. Work on user requirements and problems that comes in the form of tickets.
  3. Responding to alerts that we receive in the form of an email from third-party monitoring tools.
  4. Make sure all the maintenance jobs are running successfully.
  5. Make sure all backup jobs executed successfully without any issues an all servers.
  6. Checking SQL server logs to identify bottlenecks.
  7. Checking drive spaces on critical servers to ensure that there is ample amount of space
  8. Regularly monitor data file growth, log file growth, database growth as part of capacity planning.
  9. Maintain documentation of all the tasks and issues that you encounter for future reference.
  10. Check whether all SQL services are running (or) not.

SQL Server Architecture

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.

  1. Relational Engine
  2. Storage Engine

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.

Components of SQL Server

SQL Server Network Interface (SNI)

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

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

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

Query executor requires data to read the query plan it passes to access methods of storage engine.

Access Methods

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

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.

Plan Cache

Part of SQL servers buffer pool used to store previously executed execution plans in case they are needed later.

Data Cache

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 Architecture

Steps in executing a query

  1. Server Network Interface (SNI) of the user establishes the connection between client and server using TCP/IP protocol, sends a query in TDS packets.
  2. Query at command parser checks syntax errors then checks plan in plan cache of the buffer pool. If the plan not exists, pass the query to the optimizer.
  3. The optimizer generates the best plan and passes to the query executor, it reads the plan and passes to access method of storage engine through OLEDB.
  4. The access method requests the buffer manager to provide the data.
  5. Buffer manager checks in the data cache of the buffer pool for an existing page. If the page not exists it pulls the required pages from the data (MDF) file,  put in the data cache and passes to access method.
  6. Finally, the Access method passes the results back to relational engine, from there it sent back to the user who executed the query.

Protocols available in SQL Server

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.

  1. Shared memory
  2. Named pipes
  3. TCP/IP
  4. VIA

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


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *