SQL Server Architecture

Question 1

What are the three major components of SQL Server Architecture?

Answer 1

Protocol Layer, Relational Engine, and Storage Engine.

Question 2

Protocol Layer:

What are the 3 types of client-server architecture?

Answer 2

Shared Memory: Both client and server are on the same machine.

TCP/IP: Client and Server are separated, they communicate via TCP/IP.

Named Pipes: Different machines but on a Local Intranet Connection. (Disabled by default).

Question 3

What is TDS?

Answer 3

Tabular Data Stream. Data transfer protocol within the Protocol Layer. All 3 client-server architectures options of the Protocol Layer use this.

Question 4

Relational Engine (AKA Query Processor):

What are the 3 components of the Relational Engine?

Answer 4

CMD Parser, Optimizer, Query Executor.

Question 5

Relational Engine (AKA Query Processor):

What is the CMD Parser, what does it do?

Answer 5

The CMD Parser checks the submitted query for syntactic integrity, semantical integrity, and builds various query trees options.

Question 6

Relational Engine (AKA Query Processor):

What is the Optimizer, what does it do?

Answer 6

The optimizer creates an execution plan for the query in the most cost-effective method.