Download Wiley Beginning SQL Server 2005 Administration

Chapter 1
SQL Ser ver 2005 Architecture
It is the job of SQL Server to efficiently store and manage related data in a transaction-intensive environment. The actual theories and principles of a relational database are beyond the scope of this book, and
hopefully you already have some of that knowledge. What is pertinent to this book is the way SQL
Server manages the data, and how it communicates with clients to expose the data. The following discussion describes the communication architecture utilized by SQL Server 2005, the services SQL Server
2005 utilizes, and the types of databases SQL Server uses. This section also discusses how those
databases are stored and accessed, but you can find a detailed description of SQL Server 2005 storage
architecture in Chapter 4.
SQL Server 2005 Communication
To adequately plan for a SQL Server database application, it is important to understand how SQL Server
2005 communicates with clients. As mentioned previously, SQL Server 2005 is more of a data platform
than just a relational database server. Because the SQL Server 2005 platform offers several different data
services, it also must provide different ways of accessing that data.
SQL Server 2005 ships with the ability to communicate over different protocols. By default, SQL Server
will accept network connections via TCP/IP. The local Shared Memory protocol is also enabled by
default to enable local connections without having to incur the overhead of a network protocol.
In addition to the TCP/IP, Named Pipes, and Shared Memory protocols, the Virtual Interface Adapter
(VIA) protocol is available for VIA Storage Area Network (SAN) implementations.
With the exception of HTTP endpoints (described in Chapter 7), SQL Server utilizes a communication
format called Tabular Data Stream (TDS). The TDS packets utilized by SQL Server are encapsulated in the
appropriate protocol packets for network communication.
The task of wrapping the TDS packets is the responsibility of the SQL Server Network Interface (SNI)
protocol layer. The SNI replaces the Server Net-Libraries and the Microsoft Data Access Components
(MDAC) that were utilized in SQL Server 2000. SQL Server creates separate TDS endpoints for each network protocol.
Although TDS is the primary method for connecting to and manipulating data on a SQL Server, it is not
the only method available. In addition to TDS communication, SQL Server 2005 supports native Data
Tier Web services (see Chapter 7). By utilizing SQL Server Web services, connections can be made to SQL
Server via any client application that supports HTTP and Simple Object Access Protocol (SOAP).
Supported Languages
SQL Server 2005 supports the following five different languages to enable data manipulation, data
retrieval, administrative functions and database configuration operations:
❑
8
Transact-Structured Query Language (T-SQL) — This is Microsoft’s procedural language extension
to the Structured Query Language (SQL) standard established by the American National
Standards Institute (ANSI). T-SQL is entry-level compliant with the ANSI-99 standard. T-SQL is
the primary and most common method for manipulating data. For more information about TSQL, consult Beginning Transact-SQL with SQL Server 2000 and 2005 (Indianapolis: Wiley, 2005).