You are previewing Introducing Microsoft® SQL Server® 2012.
O'Reilly logo
Introducing Microsoft® SQL Server® 2012

Book Description

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.

Table of Contents

  1. Cover Page
  2. Copyright Page
  3. Dedication Page
  4. Contents at a Glance
  5. Contents
  6. Introduction
    1. Who Should Read This Book?
      1. Assumptions
    2. Who Should Not Read This Book
    3. How Is This Book Organized?
    4. Conventions and Features in This Book
    5. Pre-Release Software
    6. Acknowledgments
    7. Errata & Book Support
    8. We Want to Hear from You
    9. Stay in Touch
  7. PART 1 Database Administration
    1. CHAPTER 1 SQL Server 2012 Editions and Engine Enhancements
      1. SQL Server 2012 Enhancements for Database Administrators
        1. Availability Enhancements
        2. Scalability and Performance Enhancements
        3. Manageability Enhancements
        4. Security Enhancements
        5. Programmability Enhancements
      2. SQL Server 2012 Editions
        1. Enterprise Edition
        2. Standard Edition
        3. Business Intelligence Edition
        4. Specialized Editions
      3. SQL Server 2012 Licensing Overview
      4. Hardware and Software Requirements
      5. Installation, Upgrade, and Migration Strategies
        1. The In-Place Upgrade
          1. In-Place Upgrade Pros and Cons
          2. SQL Server 2012 High-Level In-Place Strategy
        2. Side-by-Side Migration
          1. Side-by-Side Migration Pros and Cons
          2. SQL Server 2012 High-Level, Side-by-Side Strategy
    2. CHAPTER 2 High-Availability and Disaster-Recovery Enhancements
      1. SQL Server AlwaysOn: A Flexible and Integrated Solution
      2. AlwaysOn Availability Groups
        1. Understanding Concepts and Terminology
          1. Availability Replica Roles
          2. Data Synchronization Modes
          3. Availability Groups Failover Modes
          4. Connection Mode in Secondaries
          5. Availability Group Listeners
        2. Configuring Availability Groups
          1. Prerequisites
          2. Deployment Examples
        3. Monitoring Availability Groups with the Dashboard
      3. Active Secondaries
        1. Read-Only Access to Secondary Replicas
        2. Backups on Secondary
      4. AlwaysOn Failover Cluster Instances
      5. Support for Deploying SQL Server 2012 on Windows Server Core
        1. SQL Server 2012 Prerequisites for Server Core
        2. SQL Server Features Supported on Server Core
        3. SQL Server on Server Core Installation Alternatives
      6. Additional High-Availability and Disaster-Recovery Enhancements
        1. Support for Server Message Block
        2. Database Recovery Advisor
        3. Online Operations
        4. Rolling Upgrade and Patch Management
    3. CHAPTER 3 Performance and Scalability
      1. Columnstore Index Overview
      2. Columnstore Index Fundamentals and Architecture
        1. How Is Data Stored When Using a Columnstore Index?
        2. How Do Columnstore Indexes Significantly Improve the Speed of Queries?
          1. Batch-Mode Processing
        3. Columnstore Index Storage Organization
        4. Columnstore Index Support and SQL Server 2012
        5. Columnstore Index Restrictions
      3. Columnstore Index Design Considerations and Loading Data
        1. When to Build a Columnstore Index
        2. When Not to Build a Columnstore Index
        3. Loading New Data
      4. Creating a Columnstore Index
        1. Creating a Columnstore Index by Using SQL Server Management Studio
        2. Creating a Columnstore Index Using Transact-SQL
        3. Using Columnstore Indexes
        4. Using Hints with a Columnstore Index
      5. Columnstore Index Observations and Best Practices
    4. CHAPTER 4 Security Enhancements
      1. Security Enhancements in SQL Server 2012
      2. Security Manageability Improvements
        1. Default Schema for Groups
        2. User-Defined Server Roles
          1. Creating and Managing Server Roles
      3. Audit Enhancements
        1. Audit Supported on All SKUs
        2. Improved Resilience
          1. Create a New Audit with SSMS
        3. User-Defined Audit Event
        4. Record Filtering
      4. Database Authentication Enhancements
        1. Enabling Contained Databases
        2. Creating Users
        3. Contained Database Authentication Security Concerns
      5. Additional Security Enhancements
        1. Cryptography Changes
        2. Tight Integration with SharePoint and Active Directory
        3. Provisioning Enhancements
        4. New Permissions
    5. CHAPTER 5 Programmability and Beyond-Relational Enhancements
      1. Pain Points of Using the Beyond Relational Paradigm
      2. SQL Server 2012 Beyond-Relational Goals
      3. Rich Unstructured Data and Services Ecosystem
      4. Beyond-Relational Example
      5. FILESTREAM Enhancements
      6. FileTable
        1. FileTable Prerequisites
          1. Enabling FILESTREAM on a SQL Server Instance
          2. Enabling Directory Name and Nontransactional Access at the Database Level
          3. Configuring FILESTREAM File Groups and Database Files, and Specifying a Directory for FileTables
        2. Creating a FileTable
          1. Copying Documents and Files to the FileTable
          2. Viewing Documents via FileTable in SSMS
        3. Managing FileTable
      7. Full-Text Search
      8. Statistical Semantic Search
        1. Configuring Semantic Search
        2. Semantic Search Examples
          1. Find the Key Phrases in a Document
          2. Find Similar or Related Documents
          3. Find the Key Phrases That Make Documents Similar or Related
      9. Spatial Enhancements
        1. Spatial Data Scenarios
        2. Spatial Data Features Supported in SQL Server
        3. Spatial Type Improvements
        4. Additional Spatial Improvements
      10. Extended Events
  8. PART 2 Business Intelligence Development
    1. CHAPTER 6 Integration Services
      1. Developer Experience
        1. Add New Project Dialog Box
        2. General Interface Changes
        3. Getting Started Window
        4. SSIS Toolbox
          1. Interface Improvement
          2. Item Arrangement
        5. Shared Connection Managers
        6. Scripting Engine
        7. Expression Indicators
        8. Undo and Redo
        9. Package Sort By Name
        10. Status Indicators
      2. Control Flow
        1. Expression Task
        2. Execute Package Task
      3. Data Flow
        1. Sources and Destinations
          1. Source and Destination Assistants
          2. ODBC Source and Destination
          3. Flat File Source
        2. Transformations
          1. Pivot Transformation
          2. Row Count Transformation
          3. Merge and Merge Join Transformations
          4. DQS Cleansing Transformation
        3. Column References
          1. Components Without Column References
          2. Resolve References Editor
        4. Collapsible Grouping
        5. Data Viewer
      4. Change Data Capture Support
        1. CDC Control Flow
        2. CDC Data Flow
          1. CDC Source
          2. CDC Splitter
      5. Flexible Package Design
        1. Variables
        2. Expressions
          1. Expression Result Length
          2. New Functions
      6. Deployment Models
        1. Supported Deployment Models
        2. Project Deployment Model Features
        3. Project Deployment Workflow
          1. Build
          2. Deploy
          3. Import
          4. Convert
      7. Parameters
        1. Project Parameters
        2. Package Parameters
        3. Parameter Usage
        4. Post-Deployment Parameter Values
          1. Server Default Values
          2. Execution Parameter Values
      8. Integration Services Catalog
        1. Catalog Creation
        2. Catalog Properties
          1. Encryption
          2. Operations
          3. Project Versioning
        3. Environment Objects
          1. Environments
          2. Environment Variables
          3. Environment References
      9. Administration
        1. Validation
        2. Package Execution
        3. Logging and Troubleshooting Tools
          1. Package Execution Logs
          2. Data Taps
          3. Reports
        4. Security
      10. Package File Format
    2. CHAPTER 7 Data Quality Services
      1. Data Quality Services Architecture
        1. Data Quality Server
        2. Data Quality Client
      2. Knowledge Base Management
        1. Domain Management
          1. DQS Data Knowledge Base
          2. New Knowledge Base
          3. End of Domain Management Activity
        2. Knowledge Discovery
          1. Map
          2. Discover
          3. Manage Domain Values
        3. Matching Policy
          1. Map
          2. Matching Policy
          3. Matching Results
      3. Data Quality Projects
        1. Cleansing Projects
          1. Map
          2. Cleanse
          3. Manage and View Results
          4. Export
        2. Matching Projects
          1. Map
          2. Matching
          3. Export
      4. Administration
        1. Activity Monitoring
        2. Configuration
          1. Reference Data
          2. General Settings
          3. Log Settings
      5. Integration
        1. Integration Services
          1. DQS Connection Manager
          2. DQS Cleansing Transformation
        2. Master Data Services
    3. CHAPTER 8 Master Data Services
      1. Getting Started
        1. Upgrade Considerations
        2. Configuration
      2. Master Data Manager
        1. Explorer
          1. Entity Management
          2. Many-to-Many Mapping
          3. Hierarchy Management
          4. Collection Management
        2. Integration Management
        3. User and Group Permissions
      3. Model Deployment
      4. MDS Add-in for Excel
        1. Installation of the MDS Add-in
        2. Master Data Management
          1. Connections
          2. Data Retrieval
          3. Data Publication
        3. Model-Building Tasks
          1. Entities and Attributes
          2. Domain-Based Attributes
        4. Shortcut Query Files
        5. Data Quality Matching
      5. Miscellaneous Changes
        1. SharePoint Integration
        2. Metadata
        3. Bulk Updates and Export
        4. Transactions
        5. Windows PowerShell
    4. CHAPTER 9 Analysis Services and PowerPivot
      1. Analysis Services
        1. Server Modes
        2. Analysis Services Projects
        3. Tabular Modeling
          1. Workspace Database
          2. Table Import Wizard
          3. Tabular Model Designer
          4. Relationships
          5. Calculated Columns
          6. Measures
          7. Key Performance Indicators
          8. Hierarchies
          9. Perspectives
          10. Partitions
          11. Roles
          12. Analyze in Excel
          13. Reporting Properties
          14. DirectQuery Mode
          15. Deployment
        4. Multidimensional Model Storage
        5. Server Management
          1. Event Tracing
          2. XML for Analysis Schema Rowsets
          3. Architecture Improvements
        6. Programmability
      2. PowerPivot for Excel
        1. Installation and Upgrade
        2. Usability
        3. Model Enhancements
      3. DAX
      4. PowerPivot for SharePoint
        1. Installation and Configuration
        2. Management
          1. Disk Space Usage
          2. Server Health Rules
          3. Data Refresh Configuration
    5. CHAPTER 10 Reporting Services
      1. New Renderers
        1. Excel 2010 Renderer
        2. Word 2010 Renderer
      2. SharePoint Shared Service Architecture
        1. Feature Support by SharePoint Edition
        2. Shared Service Architecture Benefits
        3. Service Application Configuration
      3. Power View
        1. Data Sources
        2. Power View Design Environment
        3. Data Visualization
          1. Charts
          2. Arrangement
          3. Cards and Tiles
          4. Play Axis
          5. Multiples
        4. Sort Order
        5. Multiple Views
        6. Highlighted Values
        7. Filters
          1. Slicer
          2. Tile Container
          3. View Filter
          4. Visualization Filter
        8. Display Modes
        9. PowerPoint Export
      4. Data Alerts
        1. Data Alert Designer
        2. Alerting Service
        3. Data Alert Manager
        4. Alerting Configuration
  9. Index
  10. About the Authors