Introducing Microsoft SQL Server 2012

287 Pages·2012·21.217 MB·English

by  Ross Mistry, Stacia Misner

spine = 1.76”

Introducing Microsoft
®
SQL Server
®

2012

Ross Mistry and Stacia Misner
www.it-ebooks.info
PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2012 by Microsoft Corporation
All rights reserved . No part of the contents of this book may be reproduced or transmitted in any form or by any
means without the written permission of the publisher .
Library of Congress Control Number: 2012933508
ISBN: 978-0-7356-6515-6
Microsoft Press books are available through booksellers and distributors worldwide . If you need support related
to this book, email Microsoft Press Book Support at mspinput@microsoft .com . Please tell us what you think of
this book at http://www .microsoft .com/learning/booksurvey .
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 .
The example companies, organizations, products, domain names, email addresses, logos, people, places, and
events depicted herein are fictitious. No association with any real company, organization, product, domain name,
email address, logo, person, place, or event is intended or should be inferred .
This book expresses the author’s views and opinions . The information contained in this book is provided without
any express, statutory, or implied warranties . Neither the authors, Microsoft Corporation, nor its resellers, or
distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by
this book .
Acquisitions Editor: Anne Hamilton
Developmental Editor: Devon Musgrave
Project Editor: Carol Dillingham
Technical Reviewer: Mitch Tulloch; Technical Review services provided by Content Master, a member of CM
Group, Ltd .
Copy Editor: Roger LeBlanc
Indexer: Christina Yeager
Editorial Production: Waypoint Press
Cover: Twist Creative • Seattle
www.it-ebooks.info
I dedicate this book to my wife, Sherry. Thank you for being one of the only
people in my life who has always been there for me regardless of the situation
and has never let me down. I am greatly appreciative.
–Ross MistRy
I dedicate this book to my husband and best friend, Gerry, who excels at
keeping our dreams alive.
–stacia MisneR
www.it-ebooks.info
Contents at a Glance
PART 1 DATABASE ADMINISTRATION
CHAPTER 1 SQL Server 2012 Editions and Engine Enhancements 3
CHAPTER 2 High-Availability and Disaster-Recovery Enhancements 21
CHAPTER 3 Performance and Scalability 41
CHAPTER 4 Security Enhancements 57
CHAPTER 5 Programmability and Beyond-Relational Enhancements 73
PART 2 BUSINESS INTELLIGENCE DEVELOPMENT
CHAPTER 6 Integration Services 93
CHAPTER 7 Data Quality Services 141
CHAPTER 8 Master Data Services 175
CHAPTER 9 Analysis Services and PowerPivot 199
CHAPTER 10 Reporting Services 229
www.it-ebooks.info
www.it-ebooks.info
Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
PART 1 DATABASE ADMINISTRATION
Chapter 1 SQL Server 2012 Editions and Engine Enhancements 3
SQL Server 2012 Enhancements for Database Administrators . . . . . . . . . . . 4
Availability Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Scalability and Performance Enhancements . . . . . . . . . . . . . . . . . . . . . 6
Manageability Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Security Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Programmability Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
SQL Server 2012 Editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Enterprise Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Standard Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Business Intelligence Edition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Specialized Editions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
SQL Server 2012 Licensing Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Hardware and Software Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Installation, Upgrade, and Migration Strategies . . . . . . . . . . . . . . . . . . . . . . 17
The In-Place Upgrade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Side-by-Side Migration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books and learning
resources for you. To participate in a brief online survey, please visit:
microsoft.com/learning/booksurvey
vii
www.it-ebooks.info
Chapter 2 High-Availability and Disaster-Recovery
Enhancements 21
SQL Server AlwaysOn: A Flexible and Integrated Solution . . . . . . . . . . . . . 21
AlwaysOn Availability Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Understanding Concepts and Terminology . . . . . . . . . . . . . . . . . . . . 24
Configuring Availability Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Monitoring Availability Groups with the Dashboard . . . . . . . . . . . . . 31
Active Secondaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Read-Only Access to Secondary Replicas . . . . . . . . . . . . . . . . . . . . . . 33
Backups on Secondary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
AlwaysOn Failover Cluster Instances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Support for Deploying SQL Server 2012 on Windows Server Core . . . . . 36
SQL Server 2012 Prerequisites for Server Core . . . . . . . . . . . . . . . . . . 37
SQL Server Features Supported on Server Core . . . . . . . . . . . . . . . . . 38
SQL Server on Server Core Installation Alternatives . . . . . . . . . . . . . 38
Additional High-Availability and Disaster-Recovery Enhancements . . . . . 39
Support for Server Message Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Database Recovery Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Online Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Rolling Upgrade and Patch Management . . . . . . . . . . . . . . . . . . . . . . 40
Chapter 3 Performance and Scalability 41
Columnstore Index Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Columnstore Index Fundamentals and Architecture . . . . . . . . . . . . . . . . . . 42
How Is Data Stored When Using a Columnstore Index? . . . . . . . . . . 42
How Do Columnstore Indexes Significantly Improve the
Speed of Queries? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Columnstore Index Storage Organization . . . . . . . . . . . . . . . . . . . . . . 45
Columnstore Index Support and SQL Server 2012 . . . . . . . . . . . . . . . 46
Columnstore Index Restrictions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Columnstore Index Design Considerations and Loading Data . . . . . . . . . . 47
When to Build a Columnstore Index . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
When Not to Build a Columnstore Index . . . . . . . . . . . . . . . . . . . . . . . 48
Loading New Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
viii Contents
www.it-ebooks.info
Creating a Columnstore Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .49
Creating a Columnstore Index by Using SQL Server
Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50
Creating a Columnstore Index Using Transact-SQL . . . . . . . . . . . . . .51
Using Columnstore Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52
Using Hints with a Columnstore Index . . . . . . . . . . . . . . . . . . . . . . . . .53
Columnstore Index Observations and Best Practices . . . . . . . . . . . . . . . . .54
Chapter 4 Security Enhancements 57
Security Enhancements in SQL Server 2012 . . . . . . . . . . . . . . . . . . . . . . . . .57
Security Manageability Improvements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .58
Default Schema for Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .58
User-Defined Server Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59
Audit Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62
Audit Supported on All SKUs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62
Improved Resilience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62
User-Defined Audit Event . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
Record Filtering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66
Database Authentication Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . .67
Enabling Contained Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .68
Creating Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69
Contained Database Authentication Security Concerns . . . . . . . . . .70
Additional Security Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71
Cryptography Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71
Tight Integration with SharePoint and Active Directory . . . . . . . . .71
Provisioning Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72
New Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72
Chapter 5 Programmability and Beyond-Relational
Enhancements 73
Pain Points of Using the Beyond Relational Paradigm . . . . . . . . . . . . . . . . .73
SQL Server 2012 Beyond-Relational Goals . . . . . . . . . . . . . . . . . . . . . . . . . . .74
Rich Unstructured Data and Services Ecosystem . . . . . . . . . . . . . . . . . . . . .74
Contents ix
www.it-ebooks.info
Beyond-Relational Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
FILESTREAM Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
FileTable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
FileTable Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Creating a FileTable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Managing FileTable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Full-Text Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Statistical Semantic Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Configuring Semantic Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Semantic Search Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Spatial Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Spatial Data Scenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Spatial Data Features Supported in SQL Server . . . . . . . . . . . . . . . . . 86
Spatial Type Improvements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Additional Spatial Improvements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Extended Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
PART 2 BUSINESS INTELLIGENCE DEVELOPMENT
Chapter 6 Integration Services 93
Developer Experience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Add New Project Dialog Box . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
General Interface Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Getting Started Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
SSIS Toolbox . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Shared Connection Managers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Scripting Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Expression Indicators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Undo and Redo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Package Sort By Name . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
Status Indicators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Control Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Expression Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Execute Package Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
x Contents
www.it-ebooks.info
Data Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Sources and Destinations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Column References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Collapsible Grouping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Data Viewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Change Data Capture Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
CDC Control Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
CDC Data Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Flexible Package Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114
Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115
Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Deployment Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Supported Deployment Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Project Deployment Model Features . . . . . . . . . . . . . . . . . . . . . . . . . 118
Project Deployment Workflow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Project Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Package Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .124
Parameter Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124
Post-Deployment Parameter Values . . . . . . . . . . . . . . . . . . . . . . . . . . 125
Integration Services Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Catalog Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Catalog Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Environment Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .135
Package Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .135
Logging and Troubleshooting Tools . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Package File Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Contents xi
www.it-ebooks.info