MS SQL Server 2008 consists of a core database engine plus additional services that provide business intelligence functionality

Database Engine

The Database Engine is the core component of SQL Server 2008 and provides the following features

Storage Engine

Manages:

• the storage of data based on table and column definitions
• indexes for improved query performance
• partitioning of large tables across multiple storage structures
• database snapshots
• backup and restore functionality

Security Subsystem

Manages authentication which can be integrated (windows login) or mixed (both windows login and SQL login). Integrated authentication is recommended for increased security. Permissions can be set at all levels of the object hierarchy. Provides support for use of encryption, auditing and use of policy files.

Programming Interfaces

Transact-SQL is the native programming language, but support is included for any CLR programming language via .NET extensions. XML, stored procedures, functions, triggers and full-text search are also supported

Service Broker

A message-queueing system to provide asynchronous data processing via messages and processing actions

SQL Server Agent

Provides scheduling and alerting functionality

Replication Engine

Used to synchronise changes across multiple copies of a database. Allows creation of replicas via snapshots, which are then kept in sync with transactional replication from publisher to subscriber. Also allows replication of changes on subscriber to publisher or peer-to-peer replication. Merge replication available to synchronise changes from off-line copies of database on re-connection.

High Availability

SQL Clustering implemented on Windows Clustering to provide protection from hardware failures. Database mirroring can be used to keep a second copy of a database. 'Log Shipping' uses SQL Server Agent scheduler to apply transaction log backups to a secondary server. See also Replication.

Integration Services

SQL Server 2008 Integration Services (SSIS) provides capabilities to Extract, Transform and Load data to and from databases. Data can be accessed via FTP, directories and files. SSIS can also execute applications and interact with Web Services, Microsoft Message Queue and WMI events. Containers allow SSIS to execute workflows and precedence constraints can be used to define conditional execution paths. Event handling can be used to trigger workflows

Reporting Services

SQL Server 2008 Reporting Services (SSRS) provides a platform for designing reports and distributing data. Standard reports are available, but custom reports or report models can be built and distributed via portals. Users can subscribe to reports and report models, to schedule and deliver reports according to their parameters/customistations. SSRS has two main components: the Report Server and the Report Designer

Analysis Services

SQL Server 2008 Analysis Services (SSAS) provides OLAP and data mining functionality. OLAP allows the you to deploy, query and manage cubes designed with Business Intelligence Development Studio, a .NET component.

Installation Notes

Prior to installation create service accounts for the following services:

• Database Engine
• SQL Server Agent
• Full Text Search
• Integration Services
• Reporting Services
• Analysis Services