Introducing Microsoft SQL Server 2012

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

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

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

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

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

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

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

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