Introducing Microsoft SQL Server 2012 ebook

Introducing Microsoft SQL Server Denali explores the exciting enhancements and new capabilities engineered into SQL Server, ranging from improvements in operation to those in reporting and management. Cover 1 Copyright page 2 Contents at a Glance 4 Table of Contents 6 Introduction 14 Who Should Read This Book? 14 Assumptions 14 Who Should Not Read This Book 14 How Is This Book Organized? 15 Conventions and Features in This Book 16 Pre-Release Software 16 Acknowledgments 16 Errata & Book Support 17 We Want to Hear from You 17 Stay in Touch 17 Part 1: Database Administration 18 Chapter 1: SQL Server 2012 Editions and Engine Enhancements 20 SQL Server 2012 Enhancements for Database Administrators 21 Availability Enhancements 21 Scalability and Performance Enhancements 23 Manageability Enhancements 24 Security Enhancements 27 Programmability Enhancements 28 SQL Server 2012 Editions 29 Enterprise Edition 29 Standard Edition 30 Business Intelligence Edition 31 Specialized Editions 32 SQL Server 2012 Licensing Overview 32 Hardware and Software Requirements 33 Installation, Upgrade, and Migration Strategies 34 The In-Place Upgrade 34 Side-by-Side Migration 36 Chapter 2: High-Availability and Disaster-Recovery Enhancements 38 SQL Server AlwaysOn: A Flexible and Integrated Solution 38 AlwaysOn Availability Groups 40 Understanding Concepts and Terminology 41 Configuring Availability Groups 46 Monitoring Availability Groups with the Dashboard 48 Active Secondaries 49 Read-Only Access to Secondary Replicas 50 Backups on Secondary 50 AlwaysOn Failover Cluster Instances 51 Support for Deploying SQL Server 2012 on Windows Server Core 53 SQL Server 2012 Prerequisites for Server Core 54 SQL Server Features Supported on Server Core 55 SQL Server on Server Core Installation Alternatives 55 Additional High-Availability and Disaster-Recovery Enhancements 56 Support for Server Message Block 56 Database Recovery Advisor 56 Online Operations 57 Rolling Upgrade and Patch Management 57 Chapter 3: Performance and Scalability 58 Columnstore Index Overview 58 Columnstore Index Fundamentals and Architecture 59 How Is Data Stored When Using a Columnstore Index? 59 How Do Columnstore Indexes Significantly Improve the Speed of Queries? 61 Columnstore Index Storage Organization 62 Columnstore Index Support and SQL Server 2012 63 Columnstore Index Restrictions 63 Columnstore Index Design Considerations and Loading Data 64 When to Build a Columnstore Index 64 When Not to Build a Columnstore Index 65 Loading New Data 65 Creating a Columnstore Index 66 Creating a Columnstore Index by Using SQL Server Management Studio 67 Creating a Columnstore Index Using Transact-SQL 68 Using Columnstore Indexes 69 Using Hints with a Columnstore Index 70 Columnstore Index Observations and Best Practices 71 Summary 72 Chapter 4: Security Enhancements 74 Security Enhancements in SQL Server 2012 74 Security Manageability Improvements 75 Default Schema for Groups 75 User-Defined Server Roles 76 Audit Enhancements 79 Audit Supported on All SKUs 79 Improved Resilience 79 User-Defined Audit Event 82 Record Filtering 83 Database Authentication Enhancements 84 Enabling Contained Databases 85 Creating Users 86 Contained Database Authentication Security Concerns 87 Additional Security Enhancements 88 Cryptography Changes 88 Tight Integration with SharePoint and Active Directory 88 Provisioning Enhancements 89 New Permissions 89 Chapter 5: Programmability and Beyond-Relational Enhancements 90 Pain Points of Using the Beyond Relational Paradigm 90 SQL Server 2012 Beyond-Relational Goals 91 Rich Unstructured Data and Services Ecosystem 91 Beyond-Relational Example 93 FILESTREAM Enhancements 93 FileTable 94 FileTable Prerequisites 95 Creating a FileTable 97 Managing FileTable 98 Full-Text Search 98 Statistical Semantic Search 99 Configuring Semantic Search 100 Semantic Search Examples 102 Spatial Enhancements 103 Spatial Data Scenarios 103 Spatial Data Features Supported in SQL Server 103 Spatial Type Improvements 104 Additional Spatial Improvements 106 Extended Events 107 Part 2: Business Intelligence Development 108 Chapter 6: Integration Services 110 Developer Experience 110 Add New Project Dialog Box 110 General Interface Changes 112 Getting Started Window 113 SSIS Toolbox 114 Shared Connection Managers 115 Scripting Engine 116 Expression Indicators 117 Undo and Redo 117 Package Sort By Name 117 Status Indicators 118 Control Flow 118 Expression Task 118 Execute Package Task 119 Data Flow 120 Sources and Destinations 120 Transformations 123 Column References 125 Collapsible Grouping 126 Data Viewer 127 Change Data Capture Support 128 CDC Control Flow 129 CDC Data Flow 130 Flexible Package Design 131 Variables 132 Expressions 132 Deployment Models 133 Supported Deployment Models 133 Project Deployment Model Features 135 Project Deployment Workflow 136 Parameters 139 Project Parameters 140 Package Parameters 141 Parameter Usage 141 Post-Deployment Parameter Values 142 Integration Services Catalog 145 Catalog Creation 145 Catalog Properties 146 Environment Objects 149 Administration 152 Validation 152 Package Execution 152 Logging and Troubleshooting Tools 154 Security 156 Package File Format 156 Chapter 7: Data Quality Services 158 Data Quality Services Architecture 158 Data Quality Server 158 Data Quality Client 159 Knowledge Base Management 160 Domain Management 160 Knowledge Discovery 171 Matching Policy 174 Data Quality Projects 178 Cleansing Projects 178 Matching Projects 181 Administration 183 Activity Monitoring 183 Configuration 184 Integration 187 Integration Services 188 Master Data Services 190 Chapter 8: Master Data Services 192 Getting Started 192 Configuration 193 Master Data Manager 194 Explorer 194 Integration Management 198 User and Group Permissions 200 Model Deployment 202 MDS Add-in for Excel 204 Installation of the MDS Add-in 204 Master Data Management 204 Model-Building Tasks 209 Shortcut Query Files 211 Data Quality Matching 211 Miscellaneous Changes 214 SharePoint Integration 214 Metadata 214 Bulk Updates and Export 214 Transactions 215 Windows PowerShell 215 Chapter 9: Analysis Services and PowerPivot 216 Analysis Services 216 Server Modes 216 Analysis Services Projects 218 Tabular Modeling 220 Multidimensional Model Storage 232 Server Management 232 Programmability 234 PowerPivot for Excel 235 Installation and Upgrade 235 Usability 235 Model Enhancements 238 DAX 239 PowerPivot for SharePoint 241 Installation and Configuration 241 Management 241 Chapter 10: Reporting Services 246 New Renderers 246 Excel 2010 Renderer 246 Word 2010 Renderer 247 SharePoint Shared Service Architecture 247 Feature Support by SharePoint Edition 247 Shared Service Architecture Benefits 248 Service Application Configuration 248 Power View 249 Data Sources 250 Power View Design Environment 251 Data Visualization 254 Sort Order 258 Multiple Views 258 Highlighted Values 259 Filters 260 Display Modes 262 PowerPoint Export 263 Data Alerts 263 Data Alert Designer 263 Alerting Service 265 Data Alert Manager 266 Alerting Configuration 267 About the Authors 286 Survey 287 Annotation Introducing Microsoft® SQL Server® 2012 explores the exciting enhancements and new capabilities engineered into SQL Server, ranging from improvements in operation to those in reporting and management. This book is for anyone who has an interest in SQL Server 2012 and wants to understand its capabilities, including database administrators, application developers, and technical decision makers

Introducing Microsoft® SQL Server® 2012 explores the exciting enhancements and new capabilities engineered into SQL Server, ranging from improvements in operation to those in reporting and management. This book is for anyone who has an interest in SQL Server 2012 and wants to understand its capabilities, including database administrators, application developers, and technical decision makers.