SQL Server Architecture

Any product’s architecture is considered part of its internals. An to be able to better troubleshoot SQL Server and have a proper knowledge of its internals it’s important to go through its architecture.
SQL Server Engine is comprised of four main components illustrated in the below figure

The Protocol Layer
When the SQL Server Database Engine communicates with an application, it formats the communication in a Microsoft communication format called a Tabular Data Stream (TDS) packet. The network SQL Server Network Interface (SNI) protocol layer, which replaces the Net-Libraries for SQL Server 2000 and Microsoft Data Access Components (MDAC), encapsulates the TDS packet inside a standard communication protocol, such as TCP/IP or named pipes. The SNI protocol layer is common to both the Database Engine and SQL Native Client. The SNI protocol layer is not directly configured. Instead, the server and SQL Native Client are configured to use a network protocol. Then, the Database Engine and SQL Native Client automatically use the appropriate protocol settings. The server creates a SQL Server object called a TDS endpoint for each network protocol. On the server, the TDS endpoints are installed by SQL Server during SQL Server installation.
SQL Server can be configured to support multiple protocols simultaneously, coming from
different clients. Each client connects to SQL Server with a single protocol. If the client
program does not know which protocols SQL Server is listening on, you can configure
the client to attempt multiple protocols sequentially. The following protocols are
available:
Shared Memory The simplest protocol to use, with no configurable settings. Clients using the Shared Memory protocol can connect only to a SQL Server instance running
on the same computer, so this protocol is not useful for most database activity.
Use this protocol for troubleshooting when you suspect that the other protocols
are configured incorrectly. Clients using MDAC 2.8 or earlier cannot use the Shared
Memory protocol. If such a connection is attempted, the client is switched to the
Named Pipes protocol.
Named Pipes A protocol developed for local area networks (LANs). A portion 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 The most widely used protocol over the Internet. TCP/IP can communicate across
interconnected networks of computers with diverse hardware architectures and operating
systems. It includes standards for routing network traffic and offers advanced security
features. Enabling SQL Server to use TCP/IP requires the most configuration effort, but
most networked computers are already properly configured.
Virtual Interface Adapter (VIA) A protocol that works with VIA hardware. This is a specialized protocol; configuration details are available from your hardware vendor.
TDS Endpoints
A TDS endpoint is the SQL Server object that represents the communication point between SQL Server and a client. SQL Server automatically creates an endpoint for each of the four protocols supported by SQL Server. By default, all users have access to them when the protocols are enabled. If a network protocol is not enabled, the endpoint still exists, but cannot be used. An additional endpoint is created for the dedicated administrator connection (DAC), which can only be used by members of the sysadmin fixed server role.
The table below summarizes the TDS Endpoints and their attributes

Purpose	Endpoint name	Configurable	Notes
Shared memory	TSQL LocalMachine	No	One endpoint per SQL Server instance. System TDS Endpoint and cannot be deleted
Named pipes	TSQL Named Pipes	No	One endpoint per SQL Server instance. System TDS Endpoint and cannot be deleted
TCP/IP	TSQL Default TCP	Yes	Can be more than one TCP/IP endpoint per instance
VIA	TSQL Default VIA	Yes
DAC	Dedicated Admin Connection	No	One per instance
HTTP	HyperText Transport Protocol	Yes	Created by users

To be continued.
Sami…

SQL Internals, The Series

 

This is my very first blog topic, and I decided to start a series of topics about SQL Server Internals.

The term “Internals” is very familiar to most of us; as it normally describes the hidden mechanisms and processes of a device (or apparatus) that eventually derive the visible results.

SQL Server Internals are no different; as by going through the future posts in this blog we will get a grasp of:

• What happens internally in SQL Server when we create, expand, shrink, and move databases?
• What do DBCC commands internally do? and how do they work?
• How does SQL Server handle memory?
• How does SQL Server implement, maintain and manage transactions and concurrency?

Why SQL Server Internals?

You don’t really need to know too much about SQL Server internals to be a successful DBA. You can be operating and managing a small to medium level DB architecture in size, functionality and workload, and can still get things running in an acceptable performance and functional levels. You can sometimes get stuck in some specific tasks or a small problems that you can easily (or with a little effort) resolve using the BOL, external books, or even by digging around in a lot of pretty useful forums and newsgroups over the Internet.

However, in some specific situations like, unclear problem, immediate degradation in performance, sudden awkward behavior, etc, you will need to make some quick and assertive decisions with which the above methods (forums, books, etc) won’t be of rapid help, especially when the problem is very much attached to your own infrastructure implementation with little similarities with those DBAs talk about on the Internet.

So with a proper amount of knowledge in SQL Internals you save can a lot of time (and probably critical data loss) and have a lot of confidence while managing and operating your SQL Server infrastructure

A few points to note about this series

• This is not a beginner’s series: The blog’s audiences are assumed to be SQL Server professionals who have SQL Server in the top 3 in their skills matrix.
• You will need to have some knowledge about the underlying operating system’s internals as well: It is impossible to talk SQL Server Internals without knowing (at least a little bit about) the underlying OS’s Internals. This will be also covered in this series.
• While discussing the SQL Server Internals, we also have to understand the SQL Server Architecture and configuration as well as the underlying platform’s architecture.

 

Sami…