SQLServer
SQL Server는 Oracle과 달리 아키텍처에 대한 정의가 자세하게 공식 문서에 나타나있지 않다…
여기저기 검색해보면 아키텍처 정의를 해둔 곳이 있지만 이게 실제로 공식적으로 맞는 구조인가 에 대해서..
확인을 할 수가 없다… 이놈의 Microsoft는 비밀이 많은가보다…
열심히 인터넷 검색을 하다보면 아래와 같은 구성 요소 아키텍처를 확인해볼 수 있는데..
정작 공식 문서가 아니라 다 누군가 분석해서 작성한 구성 요소 Diagram인데..
조금씩 다른게 좀 찜찜…하다
1) SQL Server Overview…
SQL Server 2005에서는 SQL SERVER를 아래와 같이 정의 한다.
Microsoft SQL Server 2005 is a database platform for large-scale online transaction processing (OLTP), data warehousing, and e-commerce applications; it is also a business intelligence platform for data integration, analysis, and reporting solutions.
SQL Server 2005 Components are “Intergration Services, Analysis Servcies, Reporting Services, Database Engine”
2) SQL Server Architecture Ref.
2. SQL Server Architecture.
우선 공식문서에 나온 기본 아키텍처 구성 요소에서 쿼리 처리 아키텍처에서 아래 그림을 확인할 수 있다.
이 그림을 보면 크게 4가지 구성 요소가 파악된다.
Server Net-Libraries, Relational engine, OLE DB, Storage Engine…
화가나는건.. SQL Server 2019에 저 그림을 넣어두곤, 저 용어에 대한 정의는 없다. .
(1) Server Net-Libraries.
The Net-Library API (Application Programming Interface) is located between the network API that is provided by a network vendor or an operating system, and a higher-level data access API, such as OLE DB and ODBC.
The Net-Library API is intended for use with Microsoft SQL Server. It consists of a single interface called the Connection object, which is used for each connection to an instance of the SQL Server database engine.
아마도 Sever Net-Libraries는 위에 대표 아키텍처에서 SNI (SQL Server Network Interface)에 해당하는 것으로 판단됨.
(2) Relational Engine.
- Parsing the SQL statements.
– The parser scans an SQL statement and breaks it down into the logical units, such as keywords, parameters, operators, and identifiers. The parser also breaks down the overall SQL statement into a series of smaller logical operations. - Optimizng the execution plans.
– Typically, there are many ways that the server could use data from the source tables to build the result set. The query optimizer determines what these various series of steps are, estimates the cost of each series (primarily in terms of file I/O), and chooses the series of steps that has the lowest cost. It then combines the specific steps with the query tree to produce an optimized execution plan. - Executing the series of logical opertaions defined in the xecution plan.
– After the query optimizer has defined the logical operations required to complete a statement, the relational engine steps through these operations in the sequence specified in the optimized execution plan. - Processing DDL(Data Definition Language) and other statements.
– These statements are not the typical SELECT, INSERT, UPDATE, or DELETE statements; these statements have special processing needs. Examples are the SET statements to set connection options, and the CREATE statements to create objects in a database. - Formatting results.
– The relational engine formats the results returned to the client. The results are formatted as either a traditional, tabular result set or as an XML document. The results are then encapsulated in one or more TDS packets and returned to the application.
<Ref. SQL Server 2000; http://msdn.microsoft.com/en-us/library/aa933154(v=sql.80).aspx>
(3) OLE DB.
OLE DB is a set of COM-based interfaces that expose data from a variety of sources. OLE DB interfaces provide applications with uniform access to data stored in diverse information sources, or data stores. These interfaces support the amount of DBMS functionality appropriate to the data store, enabling the data store to share its data.
(4) Storage Engine.
- Manages the file that the database is stored on and using space in the file.
- Builds and reads the physical pages that are used to store data.
- Manages the data buffers and all I/O to the physical files.
- Manages transactions and uses locking to control concurrent user access to rows and schemas in the database.
- Ensures the atomicity, consistency, isolation, and durability (ACID) of transactions.
- Creates and maintains the index structure.
- Supports referential integrity.
- Supports encryption and password-protected databases.
* 추가로 위 참조 아키텍처에서 정의된 Protocol Layer, SQLOS(SOS), Query Processor의 추가적인 정의를 확인해보자..
(5) Protocol Layer.
Protocol Layer의 경우 위에 Net-Libraries에 해당하는 SNI와 동일한 층을 설명하는 것으로 판단된다..
이에 대한 정확한 정의 내용은 없지만, Reference Architecture 설명에는 그리 표현되어 있음…
Microsoft 공식 내에서는 문서 찾기 어렵다…
#ref. https://dbtut.com/index.php/2019/08/20/sql-server-architecture
(6) SQLOS(SQL Operating System).
The SQLOS is responsible for managing operating system resources that are specific to SQL Server.
(7) Query Processor.
- Data manipulation language (DML) statements.
- Joins and predicates with ORDER BY.
- GROUP BY and DISTINCT clauses.
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms176083(v=sql.100)
https://docs.microsoft.com/en-us/previous-versions/windows/desktop/ms722784(v=vs.85)
– Relational Database Engine Architecture는 Query Processor, Memory, Thread and Task Architecture, Buffer Management, Distributed Query Architecture 등으로 구성된다.
# ref. SQL Server 2005 ; https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms189559(v=sql.90)
– SQL Server Database Engine 정의.
The Database Engine is the core service for storing, processing, and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise.
Use the Database Engine to create relational databases for online transaction processing or online analytical processing data. This includes creating tables for storing data, and database objects such as indexes, views, and stored procedures for viewing, managing, and securing data. You can use SQL Server Management Studio to manage the database objects, and SQL Server Profiler to capture server events.
#ref SQL Server 2014 ; https://docs.microsoft.com/en-us/previous-versions/sql/2014/database-engine/sql-server-database-engine-overview?view=sql-server-2014>
또한, SQL Server 2000에서는 아래와 같이 써있고..
The relational database server of Microsoft® SQL Server™ 2000 has two main parts: the relational engine and the storage engine. One of the most important architectural changes made in SQL Server version 7.0 was to strictly separate the relational and storage engine components within the server and to have them use the OLE DB API to communicate with each other. (Database Server = Relational Engine + Storage Engine)
추가로, SQL Server Compact 3.5에서는… 아래와 같이 써있다..
The Microsoft SQL Server Compact 3.5 (SQL Server Compact 3.5) Database Engine is the core technology for storing, processing and securing data. By using the SQL Server Compact 3.5 Database Engine, you can create, access, and modify SQL Server Compact 3.5 databases in supported devices. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the data consuming applications within your enterprise. The Database Engine is installed and runs on the supported devices.
The components of the SQL Server Compact 3.5 Database Engine are the storage engine and the query processor.
(SQL Server Database Engine = Query Processor + Storage Engine)
그래서 정리해보면, 결국 Relational Engine = Query Processor로 볼 수 있다는 정리된 글이 있음…
이게 또 공식 문서가 아니라 사실 좀 찜찜…
http://mybasicknowledge.blogspot.com/2012/06/relational-engine.html
– Database Engine Instance 정의.
운영 체제 서비스로 실행되는 sqlserver.exe 실행 파일의 복사본이며, 해당 인스턴스에서 관리하는 데이터베이스에 있는 데이터에 대한 Application의 모든 작업 요청을 처리하는 서비스로 작동된다. 또한, 같은 컴퓨터에서 데이터베이스 엔진의 여러 인스턴스를 구성할 수 있다.
#ref. SQL Server 2014 ; https://docs.microsoft.com/en-us/previous-versions/sql/2014/database-engine/configure-windows/database-engine-instances-sql-server?view=sql-server-2014
3. 정리
이렇게 용어를 정리하다 보니, 알게 된 사실을 바탕으로 다양한 아키텍처 그림에 대해서 이해가 생기기 시작한다.
– Instance에는 여러 Database Engine을 구성할 수 있다. (Database Engine ⊃ Instance)
– Database Engine은 Relational Engine과 Storage Engine으로 구성된다. ( * Relational Engine = Query Processor ? )
– Database Engine의 Relational Engine과 Storage Engine 사이 OLE DB를 통해 상호간 교류가 발생한다.
– SQLOS(SOS)는 Database Engine을 관리한다.
– 즉, Database Engine은 Relational Engine + Storage Engine + SQLOS + OLE DB 등으로 구성됨.