SQL Server Architecture

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, TDS packets can be encased in other physical transport dependent protocols, including TCP/IP,Named pipes, and Shared memory. Consequently, access to SQL Server is available over these protocols. In addition, the SQL Server API is also exposed over web services.

Microsoft SQL Server Editions

Microsoft makes SQL Server available in multiple editions, with different feature sets and targeting different users. These editions are:

       SQL Server Compact Edition (SQL CE)

The compact edition is an embedded database engine. Unlike the other editions of SQL Server, the SQL CE engine is based on SQL Mobile (initially designed for use with hand-held devices) and does not share the same binaries. Due to its small size (1 MB DLL footprint), it has a markedly reduced feature set compared to the other editions. For example, it supports a subset of the standard data types, does not support stored procedures or Views or multiple-statement batches (among other limitations). It is limited to 4 GB maximum database size and cannot be run as a Windows service, Compact Edition must be hosted by the application using it. The 3.5 version includes considerable work that supports ADO.NET Synchronization Services.

      SQL Server Datacenter Edition

      SQL Server Developer Edition

SQL Server Developer Edition includes the same features as SQL Server Enterprise Edition, but is limited by the license to be only used as a development and test system, and not as production server. This edition is available to download by students free of charge as a part of Microsoft's DreamSpark program.

      SQL Server 2005 Embedded Edition (SSEE)

SQL Server 2005 Embedded Edition is a specially configured named instance of the SQL Server Express database engine which can be accessed only by certain Windows Services.

      SQL Server Enterprise Edition

SQL Server Enterprise Edition is the full-featured edition of SQL Server, including both the core database engine and add-on services, while including a range of tools for creating and managing a SQL Server cluster.

      SQL Server Evaluation Edition

SQL Server Evaluation Edition, also known as the Trial Edition, has all the features of the Enterprise Edition, but is limited to 180 days, after which the tools will continue to run, but the server services will stop.

      SQL Server Express Edition

SQL Server Express Edition is a scaled down, free edition of SQL Server, which includes the core database engine. While there are no limitations on the number of databases or users supported, it is limited to using one processor, 1 GB memory and 4 GB database files (10 GB database files from SQL Server Express 2008 R2). The entire database is stored in a single .mdf file, and thus making it suitable for XCOPY deployment. It is intended as a replacement for MSDE. Two additional editions provide a superset of features not in the original Express Edition. The first is SQL Server Express with Tools, which includes SQL Server Management Studio Basic. SQL Server Express with Advanced Services adds full-text search capability and reporting services.

      SQL Server Fast Track

SQL Server Fast Track is specifically for enterprise-scale data warehousing storage and business intelligence processing, and runs on reference-architecture hardware that is optimized for Fast Track.

      SQL Server Standard Edition

SQL Server Standard edition includes the core database engine, along with the stand-alone services. It differs from Enterprise edition in that it supports fewer active instances (number of nodes in a cluster) and does not include some high-availability functions such as hot-add memory (allowing memory to be added while the server is still running), and parallel indexes.

      SQL Server Web Edition

SQL Server Web Edition is a low-TCO option for Web hosting.

       SQL Server Workgroup Edition

SQL Server Workgroup Edition includes the core database functionality but does not include the additional services.

History of the SQL Server

Genesis

The code base for MS SQL Server (prior to version 7.0) originated in Sybase SQL Server, and was Microsoft's entry to the enterprise-level database market, competing against Oracle, IBM, and, later, Sybase. Microsoft, Sybase and Ashton-Tate originally teamed up to create and market the first version named SQL Server 1.0 for OS/2 (about 1989) which was essentially the same as Sybase SQL Server 3.0 on Unix, VMS, etc. Microsoft SQL Server 4.2 was shipped around 1992 (available bundled with IBM OS/2 version 1.3). Later Microsoft SQL Server 4.21 for Windows NT was released at the same time as Windows NT 3.1. Microsoft SQL Server v6.0 was the first version designed for NT, and did not include any direction from Sybase.

About the time Windows NT was released, Sybase and Microsoft parted ways and each pursued their own design and marketing schemes. Microsoft negotiated exclusive rights to all versions of SQL Server written for Microsoft operating systems. Later, Sybase changed the name of its product to Adaptive Server Enterprise to avoid confusion with Microsoft SQL Server. Until 1994, Microsoft's SQL Server carried three Sybase copyright notices as an indication of its origin.

Since parting ways, several revisions have been done independently. SQL Server 7.0 was a rewrite from the legacy Sybase code. It was succeeded by SQL Server 2000, which was the first edition to be launched in a variant for the IA-64 architecture.

In the ten years since release of Microsoft's previous SQL Server product (SQL Server 2000), advancements have been made in performance, the client IDE tools, and several complementary systems that are packaged with SQL Server 2005. These include: an ETL tool (SQL Server Integration Services or SSIS), a Reporting Server, an OLAP and data mining server (Analysis Services), and several messaging technologies, specifically Service Broker and Notification Services.

SQL Server 2005

SQL Server 2005 (codenamed Yukon), released in October 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data. For this purpose, it defined an xml data type that could be used either as a data type in database columns or as literals in queries. XML columns can be associated with XSD schemas; XML data being stored is verified against the schema. XML is converted to an internal binary data type before being stored in the database. Specialized indexing methods were made available for XML data. XML data is queried using XQuery; Common Language Runtime (CLR) integration was a main features with this edition, enabling one to write SQL code as Managed Code by the CLR. SQL Server 2005 added some extensions to the T-SQL language to allow embedding XQuery queries in T-SQL. In addition, it also defines a new extension to XQuery, called XML DML, that allows query-based modifications to XML data. SQL Server 2005 also allows a database server to be exposed over web services using TDS packets encapsulated within SOAP (protocol) requests. When the data is accessed over web services, results are returned as XML.

For relational data, T-SQL has been augmented with error handling features (try/catch) and support for recursive queries with CTEs (Common Table Expressions). SQL Server 2005 has also been enhanced with new indexing algorithms, syntax and better error recovery systems. Data pages are checksummed for better error resiliency, and optimistic concurrency support has been added for better performance. Permissions and access control have been made more granular and the query processor handles concurrent execution of queries in a more efficient way. Partitions on tables and indexes are supported natively, so scaling out a database onto a cluster is easier. SQL CLR was introduced with SQL Server 2005 to let it integrate with the .NET Framework.

SQL Server 2005 introduced "MARS" (Multiple Active Results Sets), a method of allowing usage of database connections for multiple purposes.

SQL Server 2005 introduced DMVs (Dynamic Management Views), which are specialized views and functions that return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

SQL Server 2005 introduced Database Mirroring, but it was not fully supported until the first Service Pack release (SP1). In the initial release (RTM) of SQL Server 2005, database mirroring was available, but unsupported. In order to implement database mirroring in the RTM version, you had to apply trace flag 1400 at startup. Database mirroring is a high availability option that provides redundancy and failover capabilities at the database level. Failover can be performed manually or can be configured for automatic failover. Automatic failover requires a witness partner and an operating mode of synchronous (also known as high-safety or full safety).

SQL Server 2008

The latest version of SQL Server, SQL Server 2008, was released (RTM) on August 6, 2008 and aims to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime. SQL Server 2008 also includes support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects), but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them. According to Paul Flessner, senior Vice President, Server Applications, Microsoft Corp., SQL Server 2008 can be a data storage backend for different varieties of data: XML, email, time/calendar, file, document, spatial, etc as well as perform search, query, analysis, sharing, and synchronization across all data types.

Other new data types include specialized date and time types and a Spatial data type for location-dependent data. Better support for unstructured and semi-structured data is provided using the new FILESTREAM data type, which can be used to reference any file stored on the file system. Structured data and metadata about the file is stored in SQL Server database, whereas the unstructured component is stored in the file system. Such files can be accessed both via Win32 file handling APIs as well as via SQL Server using T-SQL; doing the latter accesses the file data as a BLOB. Backing up and restoring the database backs up or restores the referenced files as well. SQL Server 2008 also natively supports hierarchical data, and includes T-SQL constructs to directly deal with them, without using recursive queries.

The Full-Text Search functionality has been integrated with the database engine. According to a Microsoft technical article, this simplifies management and improves performance.

Spatial data will be stored in two types. A "Flat Earth" (GEOMETRY or planar) data type represents geospatial data which has been projected from its native, spherical, coordinate system into a plane. A "Round Earth" data type (GEOGRAPHY) uses an ellipsoidal model in which the Earth is defined as a single continuous entity which does not suffer from the singularities such as the international dateline, poles, or map projection zone "edges". Approximately 70 methods are available to represent spatial operations for the Open Geospatial Consortium Simple Features for SQL, Version 1.1.

SQL Server includes better compression features, which also helps in improving scalability. It enhanced the indexing algorithms and introduced the notion of filtered indexes. It also includes Resource Governor that allows reserving resources for certain users or workflows. It also includes capabilities for transparent encryption of data (TDE) as well as compression of backups. SQL Server 2008 supports the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be built around the Entity Data Model. SQL Server Reporting Services will gain charting capabilities from the integration of the data visualization products from Dundas Data Visualization, Inc., which was acquired by Microsoft. On the management side, SQL Server 2008 includes the Declarative Management Framework which allows configuring policies and constraints, on the entire database or certain tables, declaratively. The version of SQL Server Management Studio included with SQL Server 2008 supports IntelliSense for SQL queries against a SQL Server 2008 Database Engine. SQL Server 2008 also makes the databases available via Windows PowerShell providers and management functionality available as Cmdlets, so that the server and all the running instances can be managed from Windows PowerShell.

SQL Server 2008 R2

SQL Server 2008 R2 (formerly codenamed SQL Server "Kilimanjaro") was announced at TechEd 2009, and was released to manufacturing on April 21, 2010. SQL Server 2008 R2 adds certain features to SQL Server 2008 including a master data management system branded as Master Data Services, a central management of master data entities and hierarchies. Also Multi Server Management, a centralized console to manage multiple SQL Server 2008 instances and services including relational databases, Reporting Services, Analysis Services & Integration Services.

SQL Server 2008 R2 includes a number of new services, including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, ReportBuilder 3.0, Reporting Services Add-in for SharePoint, a Data-tier function in Visual Studio that enables packaging of tiered databases as part of an application, and a SQL Server Utility named UC (Utility Control Point), part of AMSM (Application and Multi-Server Management) that is used to manage multiple SQL Servers.

Microsoft SQL Server

Microsoft SQL Server is a relational model database server produced by Microsoft that's designed for the enterprise environment. Its primary query languages are T-SQL (Transact -SQL) and ANSI SQL.


SQL Server runs on T-SQL (Transact -SQL), a set of programming extensions from Sybase and Microsoft that add several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.

Windows SQL Azure

Microsoft® SQL Azure™ (formerly SQL Server Data Services and later SQL Services) Database is a cloud-based relational databaseservice built on Microsoft SQL Server® technologies. It provides a highly available, scalable, multi-tenant database service hosted by Microsoft in the cloud. SQL Azure Database helps to ease provisioning and deployment of multiple databases. Developers do not have to install, setup, patch or manage any software. High availability and fault tolerance is built-in and no physical administration is required. SQL Azure Database supports Transact-SQL (T-SQL). Customers can use existing knowledge in T-SQL development and a familiar relational data model for symmetry with existing on-premises databases. The maximum size of SQL Azure database currently is 50 GB.

Top features:
Relational Database Management Services (RDBMS)
• Create, access, and manipulate tables, views, indexes, roles, stored procedures, triggers, and functions
• Execute complex queries and joins across multiple tables
• Insert, Update, and Delete
• Constraints
• Transactions
• Temp tables
• Basic functions (aggregates, math, string, date/time)
• A subset of the existing SQL Server built-in stored procedures and system views
• Support for tracking billable metrics in real time and for historical analysis

Programmability
• Managed ADO.NET data access
• Native ODBC
• Support for PHP

Tools
• Provision logical servers and databases through the SQL Azure Database account portal
• SQL Server Management Studio: an integrated environment with graphics tools for accessing and configuring SQL Server and SQL
Azure databases: 32-bit | 64 bit • Project Houston web-based database management.
• Deployment Support for running SQL configuration scripts via SQLCMD

AppFabric Access Control Basic Components

Management Portal

The AppFabric portal provides a user interface that you can use to create a service namespace and manage AppFabric Access Control resources for that service namespace. You can perform management operations through the portal such as retrieving your management keys and viewing the URIs associated with your service namespace. While you can accomplish these management tasks programmatically, the portal is useful for your initial service service namespace set-up and configuration activities.

Management Service

The AppFabric Access Control management service is a set of REST API that enables service consumers to programmatically interact with AppFabric Access Control resources. The management service uses the well-known REST protocol (HTTPS), methods (GET, PUT, POST, and DELETE), and their behaviors.

Token-Exchange Endpoint

AppFabric Access Control exposes a single endpoint that issues tokens. It accepts plain text token requests, signed token requests, and SAML token requests and always returns a signed SWT.

SDK

AppFabric Access Control code samples and a management sample are included in the AppFabric SDK.

Key Features of AppFabric Access Control

For this release, AppFabric Access Control focuses on authorization for REST Web services and the AppFabric Service Bus. The following is a summary of AppFabric Access Control features:

• Cross-platform support. AppFabric Access Control can be accessed from applications that run on on almost any operating system or platform that can perform HTTPS operations.
  
• Active Directory Federation Services (ADFS) version 2.0 integration. This includes the ability to parse and publish WS-Federation metadata.
  
• Lightweight authentication and authorization using symmetric keys and HMACSHA256 signatures.
  
• Configurable rules that enable mapping input claims to output claims.
  
• Web Resource Authorization Protocol (WRAP) and Simple Web Token (SWT) support.

Overview of AppFabric Access Control

The Windows Azure AppFabric Access Control service simplifies access control for Web service providers by reducing the cost and complexity of integrating with various customer identity technologies. Instead of having to address different customer identity technologies, Web services can easily integrate with AppFabric Access Control. Web services can also integrate with all identity models and technologies that AppFabric Access Control supports through a simple provisioning process and through a REST-based management API. Subsequently, Web services can allow AppFabric Access Control to serve as the point of integration for service consumers.

All application scenarios that involve AppFabric Access Control consist of three service components:

• Service provider: The REST Web service.
  
• Service consumer: The client application that accesses the Web service.
  
• Token issuer: The AppFabric Access Control service itself.

AppFabric Access Control

The Windows Azure AppFabric Access Control (AC) service is a hosted service that provides federated authentication and rules-driven, claims-based authorization for REST Web services. REST Web services can rely on AC for simple username/password scenarios, in addition to enterprise integration scenarios that use Active Directory Federation Services (ADFS) v2.

AppFabric Service Bus

The Windows Azure AppFabric Service Bus provides a hosted, secure, and widely available infrastructure for widespread communication, large-scale event distribution, naming, and service publishing. AppFabric Service Bus provides connectivity options for Windows Communication Foundation (WCF) and other service endpoints – including REST endpoints -- that would otherwise be difficult or impossible to reach. Endpoints can be located behind network address translation (NAT) boundaries, or bound to frequently-changing, dynamically-assigned IP addresses, or both.

The Windows Azure AppFabric Service Bus and Access Control Overview

The AppFabric Service Bus connects local, firewalled applications and data with applications in the cloud, rich desktop applications, and smart, Web-enabled devices anywhere in the world.

AppFabric Access Control is a claims-based access control service that can be used on most Web-enabled devices to build interoperable, federated authentication and authorization into any connected application.

Service Bus Features

• Securely exposes to external callers Windows Communication Foundation (WCF)-based Web services that are running behind firewalls and NAT routers -- without requiring you to open any inbound ports or otherwise change firewall and router configurations.
  
• Enables secure inbound communication from devices outside the firewall.
  
• Provides a global namespace system that is location-independent: the name of a service in the AppFabric Service Bus provides no information about the final destination of the communication.
  
• Provides a service registry for publishing and discovering service endpoint references in a service namespace.
  
• Builds and hosts service endpoints that support:
  
  
  • Exposing a Web service to remote users. Expose and secure a local Web service in the cloud without managing any firewall or NAT settings.
    
  • Eventing behavior. Listen for notifications on any device, anywhere in the world.
    
  • Tunneling between any two endpoints to enable bidirectional streams.
    

The following diagram illustrates the capabilities of the Service Bus.

Access Control Features

The AppFabric Access Control service provides claims-based authentication and authorization for REST Web services.

• Usable from any platform.
  
• Low friction way to onboard new clients.
  
• Integrates with AD FS v2.
  
• Implements OAuth Web Resource Authorization Protocol (WRAP)and Simple Web Tokens (SWT).
  
• Enables simple delegation.
  
• Trusted as an identity provider by the AppFabric Service Bus.
  
• Extensible to provide integration with any identity provider.

Windows Azure AppFabric

          

         Windows Azure AppFabric provides a comprehensive cloud middleware platform for developing, deploying and managing applications on the Windows Azure Platform.


          The Windows Azure AppFabric Service Bus and Windows Azure AppFabric Access Control together make hybrid, connected applications—applications that communicate from behind firewalls, across the Internet, from hosted cloud servers, between rich desktops and smart devices—easier to build, secure, and manage. Although you can build hybrid, connected applications today, doing this often means you have to build important infrastructure components before you can build the applications themselves. AppFabric Service Bus and AppFabric Access Control provide several important infrastructure elements so that you can more easily begin making your connected applications work now.

• The AppFabric Service Bus securely relays messages to and from any Web service regardless of the device or computer on which they are hosted, or whether that device is behind a firewall or NAT router.
  
• The AppFabric Access Control is an interoperable, claims-based service that provides federated authentication and authorization solutions for any resource, whether in the cloud, behind a firewall, or on a smart device. 

Competitors of The Windows Azure Platform

• Amazon Web Services
• Engine Yard
• Google App Engine
• Heroku
• Force.com

Implementation of Windows Azure Platform

The Windows Azure platform uses a specialized operating system, called Windows Azure, to run its "fabric layer" — a cluster hosted at Microsoft's datacenters that manages computing and storage resources of the computers and provisions the resources (or a subset of them) to applications running on top of Windows Azure. Windows Azure has been described as a "cloud layer" on top of a number of Windows Server systems, which use Windows Server 2008 and a customized version of Hyper-V, known as the Windows Azure Hypervisor to provide virtualization of services.

The platform includes five services — Live Services, SQL Azure (formerly SQL Services), AppFabric (formerly .NET Services), SharePoint Services and Dynamics CRM Services — which the developers can use to build the applications that will run in the cloud. A client library, in managed code, and associated tools are also provided for developing cloud applications in Visual Studio. Scaling and reliability are controlled by the Windows Azure Fabric Controller so the services and environment don't crash if one of the servers crashes within the Microsoft datacenter and provides the management of the user's web application like memory resources and load balancing.

The Azure Services Platform can currently run .NET Framework applications compiled for the CLR, while supporting the ASP.NET application framework and associated deployment methods to deploy the applications onto the cloud platform. It can also support PHP websites. Two SDKs have been made available for interoperability with the Azure Services Platform: The Java SDK for AppFabric and the Ruby SDK for AppFabric. These enable Java and Ruby developers to integrate with AppFabric Internet services.