Dude! What is Microsoft SQL Server ?
Microsoft SQL Server is a relational
database server, developed by Microsoft:
It is a software product whose primary function is to store and retrieve data as
requested by other software applications, be it those on the same computer or
those running on another computer across a network (including the Internet).
There are at least a dozen different editions of Microsoft SQL Server aimed at
different audiences and for different workloads (ranging from small applications
that store and retrieve data on the same computer, to millions of users and
computers that access huge amounts of data from the Internet at the same
time).
True to its
namesake, Microsoft SQL Server's primary query languages are T-SQL and ANSI
SQL.
History
Genesis
|
Prior to
version 7.0 the code
base for MS SQL Server was sold by Sybase SQL Server to Microsoft, 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 (codename 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 feature 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 Tabular Data Stream (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 next
version of SQL Server, SQL Server 2008, was released (RTM) on August 6, 2008and 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, Report Builder 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.
The first SQL
Server 2008 R2 service pack (Service Pack 1) was released on July 11,
2011.
SQL Server
2012
At the 2011 PASS summit on October 11, Microsoft announced
that the next major version of SQL Server, codenamed Denali, would be SQL Server
2012. The release date is set to the first half of 2012
Editions
Microsoft
makes SQL Server available in multiple editions, with different feature sets and
targeting different users. These editions are:
Mainstream
editions
Datacenter
SQL Server
2008 R2 Datacenter is the full-featured edition of SQL Server and is designed
for datacenters that need the high levels of application support and
scalability. It supports 256 logical processors and virtually unlimited memory.
Comes with StreamInsight Premium edition.
Enterprise
SQL Server
Enterprise Edition includes both the core database engine and add-on services,
with a range of tools for creating and managing a SQL Server cluster. It can
manage databases as large as 524 petabytes and address 2 terabytes of memory and supports 8 physical
processors
Standard
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.
Web
SQL Server Web
Edition is a low-TCO option for Web hosting.
Workgroup
SQL Server
Workgroup Edition includes the core database functionality but does not include
the additional services.
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.
Specialized
editions
Microsoft SQL
Azure Database is the cloud-based version of Microsoft SQL Server,
presented as software as a service on Azure Services Platform.
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.
Developer
SQL Server
Developer Edition includes the same features as SQL Server Datacenter 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 is a specially configured named instance of the SQL Server
Express database engine which can be accessed only by certain Windows
Services.
Evaluation
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
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.
Parallel Data Warehouse (PDW)
A massively parallel processing (MPP) SQL Server
appliance optimized for large-scale data warehousing such as hundreds of terabytes.
To know more about Architecture and others components, follow the below link-
https://en.wikipedia.org/wiki/Microsoft_SQL_Server