Adam Jorgensen - Microsoft SQL Server 2012 Bible

Part I

Laying the Foundations

In This Part

Chapter 1

The World of SQL Server

Chapter 2

Data Architecture

Chapter 3

Installing SQL Server

Chapter 4

Client Connectivity

Chapter 5

SQL Server Management and Development Tools

Chapter 1

The World of SQL Server

In This Chapter

Understanding SQL Server History and Overview

Understanding SQL Server Components and Tools

Understanding Notable Features in SQL 2012

What's New with SQL Server 2012?

SQL Server 2012 represents another tremendous accomplishment for the Microsoft data platform organization. A number of new features in this release drive performance and scalability to new heights. A large focus is on speed of data access, ease and flexibility of integration, and capability of visualization. These are all strategic areas in which Microsoft has focused on to add value since SQL Server 2005.

SQL Server has grown considerably over the past two decades from its early roots with Sybase.

In 1989, Microsoft, Sybase, and Ashton-Tate jointly released SQL Server 1.0. The product was based on Sybase SQL Server 3.0 for UNIX and VMS.

SQL Server 4.2.1 for Windows NT released in 1993. Microsoft began making changes to the code.

SQL Server 6.0 (code named SQL 95) released in 1995. In 1996, the 6.5 upgrade (Hydra) was released in 1996. It included the first version of Enterprise Manager (StarFighter I) and SQL Server Agent (StarFighter II.)

SQL Server 7.0 (Sphinx), released in 1999 and was a full rewrite of the database engine by Microsoft. From a code sense, this was the first Microsoft SQL Server. SQL Server 7 also included English Query (Argo), OLAP Services (Plato), Replication, Database Design and Query tools (DaVinci), and Full-Text Search (aptly code named Babylon). Data Transformation Services (DTS) was introduced.

SQL Server 2000 (Shiloh) 32-bit, version 8, introduced SQL Server to the enterprise with clustering, better performance, and OLAP. It supported XML through three different XML add-on packs. It added user-defined functions, indexed views, clustering support, OLAP, Distributed Partition Views, and improved Replication. SQL Server 2000 64-bit version for Intel Itanium (Liberty) released in 2003, along with the first version of Reporting Services (Rosetta) and Data Mining tools (Aurum). DTS becomes powerful and gained in popularity. Northwind joined Pubs as the sample database.

SQL Server 2005 (Yukon), version 9, was another rewrite of the database engine and pushed SQL Server further into the enterprise space. In 2005, a ton of new features and technologies were added including Service Broker, Notification Services, CLR, XQuery and XML data types, and SQLOS. T-SQL gained try-catch, and the system tables were replaced with Dynamic Management Views. Management Studio replaced Enterprise Manager and Query Analyzer. DTS was replaced by Integration Services. English Query was removed, and stored procedure debugging was moved from the DBA interface to Visual Studio. AdventureWorks and AdventureWorksDW replaced Northwind and Pubs as the sample database. SQL Server 2005 supported 32-bit, 64x, and Itanium CPUs. Steve Ballmer publically vowed to never again make customers wait 5 years between releases and to return to a 2-to-3-year release cycle.

SQL Server 2008 (Katmai), version 10, is a natural evolution of SQL Server adding Policy-Based Management, Data Compression, Resource Governor, and new beyond relational data types. Notification Services went the way of English Query. T-SQL finally has date and time data types, table-valued parameters, the debugger returns, and Management Studio gets IntelliSense.

SQL Server 2008R2, version 10.5, is a release mostly focused on new business intelligence features and SharePoint 2010 supportability. The list of major new work and code in the SQL Server 2005 and 2008/R2 releases have been fully covered in previous editions, but the high points would be SQLCLR (this was the integration of another long-term strategy project); XML support; Service Broker; and Integration Services, which is all ground up code. Microsoft formed a new team built on the original members of the DTS team, adding in some C++, hardware, AS and COM+ folks, and Report Builder. Additional features to support SharePoint 2010 functionality and other major releases are also critically important. Now you have SQL 2012; so look at where this new release can carry you forward.

SQL Server's position in the database market has consistently grown over time. This section discusses some of the primary competition to SQL Server, and what makes SQL a strong choice for data management, business intelligence, and cloud computing along with the strength of the SQL Server community.

SQL Server competes primarily with two other major database platforms, Oracle and IBM's DB2. Both of these products have existed for longer than SQL Server, but the last four releases of SQL Server have brought them closer together. They are adding features that SQL has had for years and vice versa. Many of the scalability improvements added since SQL 2005 have been directly focused on overtaking the performance and other qualities of these products. Microsoft has succeeded in these releases in besting benchmarks set by many other products both in the relational database platforms as well as in data integration, analytics, and reporting. These improvements, along with the strongest integrated ecosystem, including cloud (Windows Azure SQL Database), portal (SharePoint 2010), and business intelligence make SQL Server the market leader.

SQL Server has one of the strongest communities of any technology platform. There are many websites, blogs, and community contributors that make up a great ecosystem of support. Some great avenues to get involved with include the following:

• PASS (Professional Association of SQL Server) SQLPASS.org
• SQL Saturday events SQLSaturday.com
• SQLServerCentral.com
• BIDN.com
• MSSQLTips.com
• SQLServerPedia.com
• Twitter.com #SQLHelp

Many of these are started and operated by Microsoft SQL Server MVPs and companies focused on SQL Server, education, and mentoring.

SQL Server is composed of the database engine, services, business intelligence tools, and other items including cloud functionality. This section outlines the major components and tools you need to become familiar with as you begin to explore this platform.

The SQL Server Database Engine, sometimes called the relational engine, is the core of SQL Server. It is the component that handles all the relational database work. SQL is a descriptive language, meaning that SQL describes only the question to the engine; the engine takes over from there.

Within the relational engine are several key processes and components, including the following:

• The Algebrizer checks the syntax and transforms a query to an internal representation used by the following components.
  • SQL Server's Query Optimizer determines how to best process the query based on the costs of different types of query-execution operations. The estimated and actual query-execution plans may be viewed graphically, or in XML, using Management Studio or SQL Profiler.