第 2 页
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
第 3 页
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
第 5 页
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
第 7 页
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
第 8 页
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
第 9 页
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
第 10 页
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
第 11 页
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
第 12 页
Chapter 7 Data Quality Services 141 Data Quality Services Architecture 141 Data Quality Server 141 Data Quality Client 142 Knowledge Base Management 143 Domain Management 143 Knowledge Discovery 154 Matching Policy 157 Data Quality Projects 161 Cleansing Projects 161 Matching Projects 164 Administration 166 Activity Monitoring 166 Configuration 167 Integration 170 Integration Services 171 Master Data Services 173 Chapter 8 Master Data Services 175 Getting Started 175 Upgrade Considerations 175 Configuration 176 Master Data Manager 177 Explorer 177 Integration Management 181 User and Group Permissions 183 Model Deployment 185 MDS Add-in for Excel 187 Installation of the MDS Add-in 187 Master Data Management 187 Model-Building Tasks 192 Shortcut Query Files 194 Data Quality Matching 194 xii Contents
第 13 页
Miscellaneous Changes 197 SharePoint Integration 197 Metadata 197 Bulk Updates and Export 197 Transactions 198 Windows PowerShell 198 Chapter 9 Analysis Services and PowerPivot 199 Analysis Services 199 Server Modes 199 Analysis Services Projects 201 Tabular Modeling 203 Multidimensional Model Storage 215 Server Management 215 Programmability 217 PowerPivot for Excel 218 Installation and Upgrade 218 Usability 218 Model Enhancements 221 DAX 222 PowerPivot for SharePoint 224 Installation and Configuration 224 Management 224 Chapter 10 Reporting Services 229 New Renderers 229 Excel 2010 Renderer 229 Word 2010 Renderer 230 SharePoint Shared Service Architecture 230 Feature Support by SharePoint Edition 230 Shared Service Architecture Benefits 231 Service Application Configuration 231 Contents xiii
第 14 页
Power View 232 Data Sources 233 Power View Design Environment 234 Data Visualization 237 Sort Order 241 Multiple Views 241 Highlighted Values 242 Filters 243 Display Modes 245 PowerPoint Export 246 Data Alerts 246 Data Alert Designer 246 Alerting Service 248 Data Alert Manager 249 Alerting Configuration 250 Index 251 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 xiv Contents
第 15 页
Introduction Microsoft SQL Server 2012 is Microsoft’s first cloud-ready information platform. It gives organizations effective tools to protect, unlock, and scale the power of their data, and it works across a variety of devices and data sources, from desktops, phones, and tablets, to datacenters and both private and public clouds. Our purpose in Introducing Microsoft SQL Server 2012 is to point out both the new and the improved capabilities as they apply to achieving mission-critical confidence, breakthrough insight, and using a cloud on your terms. As you read this book, we think you will find that there are a lot of exciting enhancements and new capabilities engineered into SQL Server 2012 that allow you to greatly enhance performance and availability at a low total cost of ownership, unlock new insights with pervasive data discovery across the organization and create business solutions fast—on your terms. Who Should Read This Book? This book is for anyone who has an interest in SQL Server 2012 and wants to understand its capabilities. In a book of this size, we cannot cover every feature that distinguishes SQL Server from other databases or previous versions, and consequently we assume you have some familiarity with SQL Server already. You might be a database administrator (DBA), an application developer, a business intelligence solution architect, a power user, or a technical decision maker. Regardless of your role, we hope you can use this book to discover the features in SQL Server 2012 that are most beneficial to you. Assumptions This book expects that you have at least a minimal understanding of SQL Server from both a database administrator’s perspective and business-intelligence perspective. This also includes an understanding of the components associated with the product, such as the Database Engine, Analysis Services, Reporting Services, and Integration Services. Who Should Not Read This Book As mentioned earlier, the purpose of this book is to provide the reader with a high-level preview of the capabilities and features associated with SQL Server 2012. This book is not intended to be a step-by-step comprehensive guide. Moreover, there have been over 250 new improvements associated with the product; therefore, the book may not cover every improvement in its entirety. xv
第 16 页
How Is This Book Organized? SQL Server 2012, like its predecessors, is more than a database engine. It is a collection of components you can implement either separately or as a group to form a scalable, cloud-ready information platform. In broad terms, this cloud-ready information platform consists of two categories: those that help you manage data and those that help you deliver business intelligence (BI). Accordingly, we divided this book into two parts to focus on the new capabilities for each of these areas. Part 1, “Database Administration,” is written with the database administrator (DBA) in mind and introduces readers to the numerous innovations in SQL Server 2012. Chapter 1, “SQL Server 2012 Editions and Engine Enhancements,” discusses the key enhancements affiliated with availability, scal- ability, performance, manageability, security, and programmability. It then outlines the different SQL Server 2012 editions, hardware and software requirements and installation, upgrade, and migration strategies available. In Chapter 2, “High-Availability and Disaster-Recovery Enhancements” readers learn about the new AlwaysOn features in SQL Server 2012—specifically, AlwaysOn Availability Groups and how they can be used to achieve a high level of confidence in your data and related capabilities. Chapter 3, “Performance and Scalability,” introduces a new index type called columnstore and explains how it can be leveraged to significantly accelerate data-warehousing workloads and other queries that are similar in nature. Chapter 4, “Security Enhancements,” covers the new security enhancements associated with the product, such as security manageability improvements and audit and authenti- cation enhancements. Finally, Chapter 5, “Programmability and Beyond-Relational Enhancements,” discusses the new beyond-relational enhancements positively impacting unstructured data, including refinements to existing technology features such as full-text search, spatial data, and FILESTREAM, as well as brand new capabilities like FileTables and statistical semantic search. Part 2, “Business Intelligence Development,” is written for readers who need to understand how SQL Server 2012 can help them more easily perform data integration, data quality improvements, master data management, data analysis, and reporting tasks. Chapter 6, “Integration Services,” explores the comprehensive changes in this release affecting development, deployment, and admin- istration of Integration Services packages. In Chapter 7, “Data Quality Services,” readers learn about the newest BI component available in SQL Server 2012 for centralizing data-quality activities, includ- ing how to store data-quality rules in a knowledge base and how to automate the discovery of rules. Chapter 8, “Master Data Services,” reviews the improved interface of this feature that simplifies the implementation, workflows, and administration of master data management. Chapter 9, “Analysis Services and PowerPivot,” introduces the new tabular server mode, shows how to develop tabular models, and describes enhancements to the Analysis Services platform and PowerPivot for Excel capabilities. Last, Chapter 10, “Reporting Services,” covers the improvements in SharePoint integration and details the self-service capabilities available with the new ad hoc reporting tool, Power View. xvi Introduction
第 17 页
Conventions and Features in This Book This book presents information using the following conventions, which are designed to make the information more readable and easy to follow: ■ Each exercise consists of a series of tasks, presented as numbered steps (1, 2, and so on) listing each action you must take to complete the exercise. ■ Boxed elements with labels such as “Note” provide additional information or alternative methods for completing a step successfully. ■ Text that you type (apart from code blocks) appears in bold. ■ Transact-SQL code is used to help you further understand a specific example. Pre-Release Software To help you get familiar with SQL Server 2012 as early as possible after its release, we wrote this book using examples that work with the Release Candidate 0 (RC0) version of the product. Consequently, the final version might include new features, and features we discuss might change or disappear. Refer to the “What’s New in SQL Server 2012” topic in Books Online for SQL Server at http://msdn.microsoft.com/en-us/library/ms130214%28v=sql.110%29.aspx for the most up-to-date list of changes to the product. Be aware that you might also notice some minor differences between the RTM version of the product and the descriptions and screen shots that we provide. Acknowledgments First, I would like to thank my colleagues at Microsoft Press and O’Reilly Media for providing me with another great authorship opportunity and putting together a stellar product in such a short period of time. Special thanks goes out to Devon Musgrave, Colin Lyth, Karen Szall, Carol Dillingham, Steve Sagman, Mitch Tulloch, Roger LeBlanc, Christina Yeager, Anne Hamilton, Steve Weiss, and Ken Jones. The publishing team’s support throughout this engagement is much appreciated. Second, I would like to thank my immediate family for being very patient and understanding considering I was absent from their lives on many evenings and weekends while I worked on this book. I couldn’t have done this title without their love and support. I would also like to acknowledge Shirmattie Seenarine for assisting me on this title. Shirmattie’s hard work, contributions, edits, and rewrites are much appreciated. And to my author partner, Stacia Misner, I want to thank you for once again doing an excellent job on the business intelligence part of this book. Finally, this book would not have been possible without support from my colleagues on the SQL Server team who provided introductions, strategic technology guidance, technical reviews, and edits. I would like to thank the following people: Tiffany Wissner, Quentin Clark, Joanne Hodgins, Justin Introduction xvii
第 18 页
Erickson, Santosh Balasubramanian, Gopal Ashok, Goden Yao, Jack Richins, Susan Price, Michael Rys, Srini Acharya, Darmadi Komo, and Luis Daniel Soto Maldonado. –Ross Mistry I, too, want to thank the entire team that has supported Ross and me through yet another publication. It is a pleasure to collaborate again with all of you and with Ross. I look forward to future opportuni- ties should they arise! Each of the product teams has been very helpful, and I am grateful for their assistance and appreciative of the products they have developed. In particular, I wish to thank Matt Masson, Akshai Mirchandani, Marius Dumitru, and Thierry D’hers for their amazing responsiveness to my questions because I know they are very busy people. This is the first book for which my husband did not have the opportunity to demonstrate his seemingly unending supply of patience with me because he was busy in another state preparing a new home for us. Therefore, I can’t really thank him for his support of this book in the typical sense, but I can thank him for ensuring that I will have a comfortable place in which to work and write later this year. He gives me great peace of mind and fuels my anticipation of things to come! –Stacia Misner Errata & Book Support We’ve made every effort to ensure the accuracy of this book and its companion content. Any errors that have been reported since this book was published are listed on our Microsoft Press site at oreilly.com: http://go.microsoft.com/FWLink/?Linkid=245673 If you find an error that is not already listed, you can report it to us through the same page. If you need additional support, email Microsoft Press Book Support at mspinput@microsoft.com. Please note that product support for Microsoft software is not offered through the addresses above. We Want to Hear from You At Microsoft Press, your satisfaction is our top priority, and your feedback our most valuable asset. Please tell us what you think of this book at: http://www.microsoft.com/learning/booksurvey The survey is short, and we read every one of your comments and ideas. Thanks in advance for your input! Stay in Touch Let’s keep the conversation going! We’re on Twitter: http://twitter.com/MicrosoftPress. xviii Introduction
第 19 页
PAR T 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 1
评论 (0)
暂无评论。快来抢沙发吧！