SQL Server Architecture
*******************************************************************************
Storage Engine
Storage Engine is the underlying software component that a database management system (DBMS) uses to create, retrieve, update and delete data from a database.
METADATA Interface
Each relational database system has its own mechanisms for storing metadata. Example: Tables of all tables in a database, their names, sizes and number of rows in each table.
In database terminology, this set of metadata is referred to as the catalog.
Development Layer
SQL OS Layer
SQLOS is the foundation of SQL Server architecture. It employs functions generally associated with operating system- memory management, thread scheduling, I/O management, resource management, buffer pool management and deadlock detection.
SQLOS also handle the memory requirements of MS SQL Server. On the main goals of SQL Server memory management is to reduce disk I/O. SQLOS is basically an abstraction layer among the hardware, operating system and SQL Server. It also manages the read/write processes of MS SQL Server.
Protocol Layer
Protocol layer implements the external interface to SQL Server. All operations that can be invoked on SQL Server are communicated to it via a Microsoft-defined format, called Tabular Data Stream (TDS). TDS is an application layer protocol, used to transfer data between a database server and a client. Initially designed and developed by Sybase Inc. for their Sybase SQL Server relational database engine in 1984, and later by Microsoft in Microsoft SQL Server
Development Layer Components
PARSER
Syntax checks are performed by parser.
T-SQL Compiler
Semantic checks are performed and also creates execution plans for the queries.
Algebraizer
Evaluates mathematical or algebraic expressions in queries.
Follows BODMOS rules.
Query Optimizer
Simplifies and chooses low cost path for query execution.
SQL Manager
Supervises components of Development layer
CLR: Common Langauage Runtime
CLR allows programmers to create stored procedures, triggers, user-defined functions, aggregates and types directly inside SQL Server. T-SQL (Transact-SQL) is great at returning data sets, but it's not good for much else beyond that. CLR makes it possible to solve problems and produce data manipulations inside SQL Server that would normally require a completely separate program to pull off.
T-SQL Execution
Executes T-SQL commands
Query Execution
Executes Single line commands
Expression Service
Execution Environment
Components of SQL OS
SQL CLR Hosting Layer
SQL Server embedded Common Language Runtime (SQLCLR)
.NET runtime embedded in SQL Server 2005
Provides capability to develop rich functionality hosted within database server
Hosting layer provides coordination for assembly loading, threads, memory management, security model, execution context, etc.
Safely extend SQL Server functionality, unlike extended stored procedures (XPs)
Deadlock Monitor
A companion tool, constantly monitors your system for lock and deadlock events, with zero impact on your database’s performance.
Lock Manager
Locks control concurrent access to data items by multiple transactions. Your DBMS is simple and uses only Shared (S) and Exclusive (X) mode locks.
Memory Manager
Decision maker for memory components.
eg: Managing buffer pool
Scheduler
The main goal of a scheduler is to schedule tasks on a given CPU. A scheduler runs one task at the time – only one task is actively running on a CPU that scheduler is affinitized to. By limiting one active task per scheduler SQLOS minimizes CPU demand on the system, which leads to smaller amount of context switches, and, therefore, improves throughput and scalability of the server application
Synchronization Services
Buffer Pool
The primary purpose of a SQL Server database is to store and retrieve data, so intensive disk I/O is a core characteristic of the Database Engine. And because disk I/O operations can consume many resources and take a relatively long time to finish, SQL Server focuses on making I/O highly efficient. Buffer management is a key component in achieving this efficiency.
The buffer management component consists of two mechanisms: the buffer manager to access and update database pages, and the buffer cache (also called the buffer pool), to reduce database file I/O.
Buffer Pool is part of the overall SQL Server Process space
Buffer cache is the pool of buffered pages and it is managed by Buffer Manager.
*******************************************************************************
Licensing
*******************************************************************************
SQL Server 2005 is available under three licensing models:
Server plus device client access license (CAL). Requires a license for the computer running the Microsoft server product, as well as CALs for each client device.
Server plus user client access license (CAL). Requires a license for the computer running the Microsoft server product, as well as CALs for each user.
Processor license. Requires a single license for each CPU in the operating system environment running SQL Server. This license includes unlimited client device access.
SQL SERVER 2005 SERVER PLUS CAL LICENSING
SQL Server 2005 offers separate Server licenses (in Workgroup, Standard and Enterprise Editions) in conjunction with per device or per user client access licenses (CALs). Server plus CAL licenses are useful to customers using SQL Server in non-Web-based scenarios.
Server License. A Server license (for Workgroup, Standard, or Enterprise editions) is required for every operating system environment on which that edition of SQL Server software or any of its components (for example, Analysis Services) is running.
Device CAL. A SQL Server Device CAL is required in order
for a device (for example, a PC, workstation, terminal, PDA, mobile phone, and so on) to access or use the services or functionality of Microsoft SQL Server. The Server plus device CAL model will likely be the more cost-effective choice if there are multiple users per device (for example, in a call center).
User CAL. A SQL Server User CAL is required in order for a user (employee, customer, partner, and so on) to access or use the services or functionality of Microsoft SQL Server. The Server plus user CAL model will likely be more cost effective if there are multiple devices per user (for example, a user who has a desktop PC, laptop, PDA, and so forth).
A CAL is not software; it is a legal document granting a device or user access to server software. A single device CAL grants access to multiple servers for one device (CAL must be same version as latest version of any of the servers). A single user CAL grants access to multiple servers for one user.
SQL SERVER 2005 PROCESSOR LICENSING
Microsoft offers a processor-based licensing model to help alleviate complexity. Processor licenses can be used for any type of application (not limited to Web-based scenarios).
Processor License. A Processor License is required for each processor installed on each operating system environment running SQL Server or any of its components (for example, Analysis Services). It includes access for an unlimited number of users or devices to connect from either inside or outside the firewall. Customers do not need to purchase additional Server Licenses or client access licenses (CALs) when licensed under the per processor model.
Processor licenses are available in Enterprise, Standard, and Workgroup Editions and offer more simplicity for certain scenarios.
*******************************************************************************
Pricing
*******************************************************************************
Microsoft SQL Server 2005 licensing provides the option to purchase SQL Server 2005 under a server operating system license with incremental Client Access Licenses (CALs), or a per-processor license model.
******************************************************************************
System Requirements for SQL Server 2005
******************************************************************************
Software Requirements
SQL Server Setup requires Microsoft Windows Installer 3.1 or later and Microsoft Data Access Components (MDAC) 2.8 SP1 or later.
SQL Server Setup installs the following software components required by the product:
* Microsoft .NET Framework 2.0
* Microsoft SQL Server Native Client
* Microsoft SQL Server Setup support files
Hardware Requirements
Computer with 600 MHz; 1 GHz recommended
214 MB of Hard disk space required
512 MB RAM; 1 GB recommended
Internet Software - Microsoft Internet Explorer 6.0 SP1 or later is required
Microsoft Windows XP with Service Pack 2 or later
Microsoft Windows 2000 with Service Pack 4 or later
Microsoft Windows 2000 Server with Service Pack 4 or later
Microsoft Windows 2003 Server with Service Pack 1 or later
******************************************************************************
SQL Server 2005 Tools
******************************************************************************
SQLCMD a command line utility, to run ad hoc Transact-SQL statements and scripts.
SQL Server Management Studio, is a new tool for SQL Server database administrators and developers. Hosted inside Microsoft Visual Studio, it brings graphical tools for database management together with a rich development environment.
