Table Of Contentt
of s
s
o s
r
c e
i
M
r
P
e
r
p
Published with the authorization of Microsoft Corporation by:
O’Reilly Media, Inc.
1005 Gravenstein Highway North
Sebastopol, California 95472
With Microsoft prePress, you can access just-written content from upcoming
books. The chapters come straight from our respected authors, before they’re
fully polished and debugged—for critical insights now, when you need them.
This document contains one or more portions of a preliminary version of a Microsoft Press title and is provided
“as is.” The content may be changed substantially upon final publication. In addition, this document may make
reference to pre-released versions of software products that may be changed substantially prior to final
commercial release. Microsoft reserves the right to not publish this title or any versions thereof (including
future prePress ebooks). This document is provided for informational purposes only. MICROSOFT MAKES NO
WARRANTIES, EITHER EXPRESS OR IMPLIED, IN THIS DOCUMENT. Information and views expressed in this
document, including URL and other Internet website references may be subject to change without notice. You
bear the risk of using it.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under
copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or
transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or
for any purpose, without the express written permission of Microsoft Corporation.
Some examples are for illustration only and are fictitious. No real association is intended or inferred. This
document does not provide you with any legal rights to any intellectual property in any Microsoft product,
service, or other offering.
© 2013 Patrick LeBlanc. All rights reserved.
Microsoft and the trademarks listed at
http://www.microsoft.com/about/legal/en/us/IntellectualProperty/Trademarks/EN-US.aspx are trademarks of
the Microsoft group of companies. All other marks are property of their respective owners.
Contents at a glance
Part I Getting started with Microsoft SQL Server 2012
Chapter 1 Overview of Microsoft SQL Server 2012
Chapter 2 Installing, configuring, and upgrading
Microsoft SQL Server 2012
Chapter 3 Using SQL Server 2012 administration and
development tools
Part II Designing databases
Chapter 4 Designing SQL Server databases
Chapter 5 Creating your first table
Chapter 6 Building and maintaining indexes
Part III Advanced database design topics
Chapter 7 Table compression
Chapter 8 Table partitioning
Chapter 9 Database snapshots
Chapter 10 The SELECT statement
Part IV Using Transact-SQL (TSQL)
Chapter 11 Advanced data retrieval topics
Chapter 12 Modifying data
Chapter 13 Built-in scalar functions
Part V Creating other database objects
Chapter 14 Advanced TSQL topics
Chapter 15 Views
Chapter 16 User-defined functions
Chapter 17 Stored procedures
Chapter 18 Data manipulation triggers
Part VI SQL Server replication
Chapter 19 Replication
Part VII Database maintenance
Chapter 20 Backups
Chapter 21 Managing and maintaining indexes and statistics
Chapter 22 Maintenance plans
Part VIII Database management
Chapter 23 SQL Server Profiler
Chapter 24 Extended events
Chapter 25 SQL Server security
Chapter 26 Resource Governor
Chapter 27 SQL Server Agent
Chapter 28 Database mail
Chapter 29 Data definition triggers
Chapter 30 Dynamic management objects
Part IX High-availability solutions
Chapter 31 AlwaysOn
Chapter 32 Log shipping
Note: Chapters included in this file are indicated in black.
PART I
Getting started
with Microsoft
SQL Server 2012
CHAPTER 1 Sections, body text, and emphasis tags . . . . . . . . . . . 3
CHAPTER 2 Code elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
CHAPTER 3 Using SQL Server 2012 administration and
development tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
1
CHAPTER 1
Overview of
Microsoft SQL Server 2012
After completing this chapter, you will be able to
■■ Explain SQL Server components and features and their uses .
■■ Identify SQL Server features vital to your environment .
■■ Define and scope your SQL Server installation topology from a high level.
The process of learning a new technology can be daunting and sometimes involves a tremendous
amount of time and effort. Each step of the process, from installing and configuring the software to
deploying the first project, introduces new challenges. These challenges often grow when the tech-
nology includes several components and features, so the first step, especially with a multicomponent
technology, is to identify the components your environment requires and gain a good understanding
of the functionality of each component . To that end, in this chapter, you will examine the components
and features of Microsoft SQL Server 2012 and determine how they fit into your installation.
Like most relational database management systems (RDBMSs), SQL Server 2012 includes several
components . The product itself, however, is often divided into two distinct categories: business intel-
ligence (BI) and the Database Engine .
Business Intelligence
Business intelligence (BI) refers to data transformed into knowledge that can then be used to make
more informed business decisions . For example, a company whose primary purpose is to sell bikes
could use its data to identify sales trends and the purchasing patterns of its customers . From that
analysis, the company could decide to focus sales efforts on a particular area or region, which in turn
could lead to better opportunities and offer the company competitive advantages in its industry .
While the BI features of SQL Server 2012 can add highly visible and effective value to business
users and data consumers, in this book you'll focus primarily on the features specific to the Database
Engine .
3
Database Engine
The Database Engine sits at the core of the SQL Server components . The engine operates as a service
on a machine, which is often referred to as an instance of SQL Server . You can run multiple instances
of SQL Server on a given server . When you connect to SQL Server, the instance is the target of the
connection . Once an application is connected, it sends Transact-SQL (T-SQL) statements to the in-
stance . The instance in return sends data back to the client . Within the connection is a security layer
that validates access to the data as specified by the database administrators (DBAs). The Database
Engine enables you to leverage the full capabilities of all of the other components, such as accessing,
storing, and securing the data .
The storage component of the Database Engine determines how the data is stored on disk . When
designing your databases, you will specify various aspects that will dictate how your tables, indexes,
and, in some cases, views are physically organized on your disk subsystem . You will examine the con-
cepts of tables, indexes, and views in detail in later chapters . In SQL Server 2012, you can physically
distribute data across disks by partitioning it, or dividing the data into distinct, independent parts .
Partitioning not only improves query performance, but it also simplifies the process of managing and
maintaining your data . With the release of SQL Server 2012, Microsoft increased the number of sup-
ported partitions to 15,000 per table .
Within the Database Engine itself, the storage engine is the primary component . Surrounding it are
several additional components that depend on the engine . These components include the following:
■■ T-SQL programming interface (Microsoft's implementations of the SQL ANSI standard
language)
■■ Security subsystem
■■ Replication
■■ SQL Server Agent
■■ High availability and disaster recovery tools
■■ SQL Server Integration Services
■■ SQL Server Management tools
The following sections provide a brief explanation of each component .
T-SQL Programming Interface
What is the value in storing data if you cannot access it? SQL Server provides a rich programming lan-
guage that allows you to write simple and complex queries against the underlying storage structures .
Using T-SQL, you can write data manipulation queries that enable you to modify and access the data
on demand. You can create objects such as views, stored procedures, triggers, and user-defined func-
tions that act as a means of surfacing that data. Applications written in programming languages such
4 Part 1 Getting Started with Microsoft SQL Server 2012
as Visual Basic and C# .NET can send T-SQL queries from applications to the Database Engine. The
Database Engine will then resolve the queries and send the results back to the client .
In addition, you can write data definition queries to create and modify objects that act as mecha-
nisms for surfacing the data. T-SQL also allows you to manage server configurations and security
seamlessly . T-SQL is a set-based language, meaning that it performs optimally when interacting with
data in sets as opposed to manipulating strings or iterating over rows of data . While T-SQL is capable
of these cursor-based operations, these types of operations are less efficient than a properly designed
set-based approach. If you find you are using T-SQL to perform cursor-based operations, consider
leveraging a common language runtime (CLR) language . Using your favorite compiler (Visual Studio,
for example), you can extend the functionality of T-SQL .
SQL Server 2012 introduces several new T-SQL programming enhancements, including a simpler
form of paging, windowing functions, and error handling. A THROW statement is introduced that
provides a way to elegantly handle errors by raising exceptions . You can now create a FileTable that
builds on the FileStream technology introduced in SQL Server 2008 . Coupling the FileTable with Full-
TextSearch allows you to run complicated queries against massive amounts of text data (such as the
complete text of this book) . SQL Server 2012 also introduces several new conversion, string, logical,
data, and time functions .
Security Subsystem
In most organizations, data is the most valuable asset, and keeping that data secure is a major con-
cern. Any vulnerability in an organization's security might end up triggering a series of events that
could prove catastrophic to the business . This is why SQL Server 2012 consists of a robust security
subsystem that allows you to control access via two modes of authentication, SQL and Windows. As
an administrator, you are able to configure SQL Server security at multiple levels. Using T-SQL or SQL
Server Management Studio, you can control access to a particular instance of SQL Server, to specific
databases, to objects within those databases, and even to columns within a particular table.
SQL Server also includes native encryption . For example, if you want to secure employees' So-
cial Security numbers, using column level encryption, you could encrypt a single column in a table .
SQL Server also includes Transparent Data Encryption (TDE), which allows you to encrypt an entire
database without affecting how clients and applications access the data . However, if someone were to
breach your network security and obtain a copy of a data file or backup file, the only way that person
could access the data is with an encryption key that you set and store .
Even with all of these security capabilities, SQL Server provides you with the ability to audit your
server and databases proactively. In SQL Server 2012, you can filter audit events before they are writ-
ten to the audit log . Chapter 26, “Security,” describes how to plan and deploy your SQL Server security
strategy. You will learn specific concepts around creating logins and users, and you will examine how
to create a security approach and maintain security accounts .
Also in SQL Server 2012, you can create user-defined server roles, which can assist in providing a
more secure method of allocating server-level access to server administrators . Microsoft has included
Chapter 1 Overview of Microsoft SQL Server 2012 5
the ability to create users within a database without requiring you to create a server login, known as
contained databases . In past versions of SQL Server, prior to granting access at the database level, an
administrator was required to create a server login . With the advent of SQL Server 2012, a user can be
self-contained within a database .
Replication
SQL Server replication has been available in most releases of the product . Over time, replication types
were introduced to ensure that users could configure replication architectures that satisfied a wide
range of scenarios . Using SQL Server replication technology, you can distribute data locally, to differ-
ent locations, using File Transfer Protocol (FTP), over the Internet, and to mobile users . Replication can
be configured to push data, pull data, and merge data across local area networks (LANs) and wide
area networks (WANs).
The simplest form of replication, snapshot replication, periodically takes a snapshot of the data and
distributes it to servers that are subscribed to the publication . Snapshot replication is typically used to
move data at longer intervals, such as daily or nightly. While this method is effective, it is often insuffi-
cient in satisfying the high demands of users for near real-time data . If higher throughput is required,
users often leverage transactional replication . Instead of distributing snapshots of data, transactional
replication continuously sends data changes as they happen to the subscribers . Transactional replica-
tion is typically used in a server-to-server topology where one server is the source of the data and the
other server is used as a backup copy or for reporting .
Both replication types are one-way data movements . But what if you need bidirectional move-
ment? For example, assume you have mobile users who work offline. While they are offline, they enter
information into a database residing on an instance of SQL Server running on their laptops . What
happens when they return to the office and connect to the network? In this scenario, the local in-
stance will synchronize with the company’s primary SQL Server database . Merge replication will move
transactions between the publisher and subscriber since the last time synchronization occurred .
SQL Server professionals debate the use of replication as a high availability (HA) or disaster re-
covery (DR) technology . Could it be used for either? There is a possibility; however, replication only
moves schema changes and data. To provide an effective HA or DR topology, every aspect of the
instance should be included such as security, maintenance, jobs, and so on. Therefore, using replica-
tion in either case could pose potential problems in the event of hardware failure or a disaster .
See Also Chapter 19, "Replication," discusses the replication in depth.
SQL Server Agent
SQL Server Agent runs as a separate service on an instance of SQL Server. Each instance of SQL Server
has an accompanying SQL Agent service. The primary use of SQL Server Agent is to execute sched-
uled tasks, such as rebuilding indexes, backing up databases, loading the data warehouse, and so on .
It allows you to schedule the jobs to run at various intervals throughout the day or night.
6 Part 1 Getting Started with Microsoft SQL Server 2012
