Microsoft® SQL Server 2012 Unleashed

Microsoft® SQL Server 2012 Unleashed

Released December 2013
Publisher(s): Sams
ISBN: 9780133408539

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Buy the print version of¿Microsoft SQL Server 2012 Unleashed and get the eBook version for free! eBook version includes chapters 44-60 not included in the print. See inside the book for access code and details.

¿

With up-to-the-minute content, this is the industry’s most complete, useful guide to SQL Server 2012.

¿

You’ll find start-to-finish coverage of SQL Server’s core database server and management capabilities: all the real-world information, tips, guidelines, and samples you’ll need to create and manage complex database solutions. The additional online chapters add extensive coverage of SQL Server Integration Services, Reporting Services, Analysis Services, T-SQL programming, .NET Framework integration, and much more.

¿

Authored by four expert SQL Server administrators, designers, developers, architects, and consultants, this book reflects immense experience with SQL Server in production environments. Intended for intermediate-to-advanced-level SQL Server professionals, it focuses on the product’s most complex and powerful capabilities, and its newest tools and features.

  • Understand SQL Server 2012’s newest features, licensing changes, and capabilities of each edition

  • Manage SQL Server 2012 more effectively with SQL Server Management Studio, the SQLCMD command-line query tool, and Powershell

  • Use Policy-Based Management to centrally configure and operate SQL Server

  • Utilize the new Extended Events trace capabilities within SSMS

  • Maximize performance by optimizing design, queries, analysis, and workload management

  • Implement new best practices for SQL Server high availability

  • Deploy AlwaysOn Availability Groups and Failover Cluster Instances to achieve enterprise-class availability and disaster recovery

  • Leverage new business intelligence improvements, including Master Data Services, Data Quality Services and Parallel Data Warehouse

  • Deliver better full-text search with SQL Server 2012’s new Semantic Search

  • Improve reporting with new SQL Server 2012 Reporting Services features

    • Download the following from informit.com/title/9780672336928: Sample databases and code examples

    ¿

    ¿

    Table of contents

    1. About This eBook
    2. Title Page
    3. Copyright Page
    4. Contents at a Glance
    5. Table of Contents
    6. About the Authors
    7. About the Contributing Author
    8. Dedication
    9. Acknowledgments
    10. We Want to Hear from You!
    11. Reader Services
    12. Introduction
      1. Who This Book Is For
      2. What This Book Covers
      3. Conventions Used in This Book
      4. Good Luck!
    13. Part I: Welcome to Microsoft SQL Server
      1. Chapter 1. SQL Server 2012 Overview
        1. SQL Server Components and Features
          1. The SQL Server Database Engine
          2. SQL Server 2012 Administration and Management Tools
          3. Replication
          4. Merge Replication
          5. Database Mirroring
          6. SQL Server AlwaysOn Features
          7. SQL Server Service Broker
          8. Full-Text and Semantic Search
          9. SQL Server Integration Services (SSIS)
          10. SQL Server Analysis Services (SSAS)
          11. SQL Server Reporting Services (SSRS)
          12. Master Data Services
          13. Data Quality Services
        2. SQL Server 2012 Editions
          1. SQL Server 2012 Standard Edition
          2. SQL Server 2012 Enterprise Edition
          3. Differences Between the Enterprise and Standard Editions of SQL Server
          4. Other SQL Server 2012 Editions
        3. SQL Server Licensing Models
          1. Web Edition Licensing
          2. Developer Edition Licensing
          3. Express Edition Licensing
          4. Choosing a Licensing Model
          5. Mixing Licensing Models
          6. Passive Server/Failover Licensing
          7. Virtual Server Licensing
        4. Summary
      2. Chapter 2. What’s New in SQL Server 2012
        1. New SQL Server 2012 Features
          1. New and Improved Storage Features
          2. New Transact-SQL Constructs
          3. New Scalability and Performance Features
          4. New Security Features
          5. New Availability Features
          6. Statistical Semantic Search
          7. Data Quality Services
        2. SQL Server 2012 Enhancements
          1. SQL Server Management Studio Enhancements
          2. Resource Governor Enhancements
          3. Spatial Data Enhancements
          4. Integration Services Enhancements
          5. Service Broker Enhancements
          6. Full-Text Search Enhancements
          7. Analysis Services Enhancements
          8. Reporting Services Enhancements
          9. Master Data Services Enhancements
        3. Deprecated Features
        4. Summary
      3. Chapter 3. Examples of SQL Server Implementations
        1. Application Terms
        2. OLTP Application Examples
          1. OLTP ERP Example
          2. OLTP Shopping Cart Example
        3. DSS Application Examples
          1. DSS Example One
          2. DSS Example Two
          3. DSS Example Three
        4. Summary
    14. Part II: SQL Server Tools and Utilities
      1. Chapter 4. SQL Server Management Studio
        1. What’s New in SSMS
        2. The Integrated Environment
          1. Window Management
          2. Integrated Help
        3. Administration Tools
          1. Registered Servers
          2. Object Explorer
          3. Activity Monitor
          4. Log File Viewer
          5. SQL Server Utility
        4. Development Tools
          1. The Query Editor
          2. Managing Projects in SSMS
          3. Integrating SSMS with Source Control
          4. Using SSMS Templates
          5. Using SSMS Snippets
          6. T-SQL Debugging
          7. Multiserver Queries
        5. Summary
      2. Chapter 5. SQL Server Command-Line Utilities
        1. What’s New in SQL Server Command-Line Utilities
        2. The sqlcmd Command-Line Utility
          1. Executing the sqlcmd Utility
          2. Using Scripting Variables with sqlcmd
        3. The dta Command-Line Utility
        4. The tablediff Command-Line Utility
        5. The bcp Command-Line Utility
        6. The sqldiag Command-Line Utility
        7. The sqlservr Command-Line Utility
        8. The SqlLocalDB Command-Line Utility
        9. Summary
      3. Chapter 6. SQL Server Profiler
        1. What’s New with SQL Server Profiler
        2. SQL Server Profiler Architecture
        3. Creating Traces
          1. Events
          2. Data Columns
          3. Filters
        4. Executing Traces and Working with Trace Output
        5. Saving and Exporting Traces
          1. Saving Trace Output to a File
          2. Saving Trace Output to a Table
          3. Saving the Profiler GUI Output
          4. Importing Trace Files
          5. Importing a Trace File into a Trace Table
          6. Analyzing Trace Output with the Database Engine Tuning Advisor
        6. Replaying Trace Data
        7. Defining Server-Side Traces
          1. Monitoring Running Traces
          2. Stopping Server-Side Traces
        8. Profiler Usage Scenarios
          1. Analyzing Slow Stored Procedures or Queries
          2. Deadlocks
          3. Identifying Ad Hoc Queries
          4. Identifying Performance Bottlenecks
          5. Monitoring Auto-Update Statistics
          6. Monitoring Application Progress
        9. Summary
    15. Part III: SQL Server Administration
      1. Chapter 7. SQL Server System and Database Administration
        1. What’s New in SQL Server System and Database Administration
        2. System Administrator Responsibilities
        3. System Databases
          1. The master Database
          2. The resource Database
          3. The model Database
          4. The msdb Database
          5. The distribution Database
          6. The tempdb Database
          7. Maintaining System Databases
        4. System Tables
        5. System Views
          1. Compatibility Views
          2. Catalog Views
          3. Information Schema Views
          4. Dynamic Management Views
        6. System Stored Procedures
          1. Useful System Stored Procedures
        7. Summary
      2. Chapter 8. Installing SQL Server 2012
        1. What’s New in Installing SQL Server 2012
          1. Installation Requirements
          2. Hardware Requirements
          3. Software Requirements
        2. Installation Walkthrough
          1. Install Screens, Step-by-Step
          2. Installing SQL Server Documentation
        3. Installing SQL Server Using a Configuration File
          1. Running an Automated or Manual Install
        4. Installing Service Packs and Cumulative Updates
          1. Applying a Service Pack or Cumulative Update During a New Installation
          2. Applying a Service Pack or Cumulative Update to an Existing Installation
          3. Installing a Service Pack from the Command Line
        5. Summary
      3. Chapter 9. Upgrading to SQL Server 2012
        1. What’s New in Upgrading SQL Server
          1. SQL Server 2012
          2. The SQL Server 2012 Upgrade Matrix
        2. Identifying Products and Features to be Upgraded
        3. Using the SQL Server Upgrade Advisor (UA)
          1. Getting Started with the UA
          2. The Analysis Wizard
          3. The Report Viewer
        4. Destination: SQL Server 2012
          1. Side-by-Side Upgrade
          2. Upgrading In-Place
          3. Upgrading the Database Engine
        5. Installing Product Updates (Slipstreaming) During Upgrades
        6. Upgrading Using a Configuration File
          1. Upgrading from Pre-SQL Server 2005 Versions
        7. Upgrading Other SQL Server Components
          1. Upgrading Analysis Services
          2. Upgrading SQL Server Analysis Services
          3. Upgrading Reporting Services
          4. Performing an In-Place Upgrade of Reporting Services
          5. Migrating to Reporting Services 2012
          6. Upgrading SSIS Packages
          7. Migrating DTS Packages
        8. Summary
      4. Chapter 10. Client Installation and Configuration
        1. What’s New in Client Installation and Configuration
          1. Client/Server Networking Considerations
          2. Server Network Protocols
          3. The Server Endpoint Layer
          4. The Role of SQL Browser
        2. Client Installation
          1. Installing the Client Tools
          2. Installing SNAC
        3. Client Configuration
          1. Client Configuration Using SSCM
          2. Connection Encryption
        4. Client Data Access Technologies
          1. Provider Choices
          2. Connecting Using the Various Providers and Drivers
          3. General Networking Considerations and Troubleshooting
        5. Summary
      5. Chapter 11. Database Backup and Restore
        1. What’s New in Database Backup and Restore
        2. Developing a Backup and Restore Plan
        3. Types of Backups
          1. Full Database Backups
          2. Differential Database Backups
          3. Partial Backups
          4. Differential Partial Backups
          5. File and Filegroup Backups
          6. Copy-Only Backups
          7. Transaction Log Backups
        4. Recovery Models
          1. Full Recovery
          2. Bulk-Logged Recovery
          3. Simple Recovery
        5. Backup Devices
          1. Disk Devices
          2. Tape Devices
          3. Network Shares
          4. Media Sets and Families
          5. Creating Backup Devices
        6. Backing Up a Database
          1. Creating Database Backups with SSMS
          2. Creating Database Backups with T-SQL
        7. Backing Up the Transaction Log
          1. Creating Transaction Log Backups with SSMS
          2. Creating Transaction Log Backups with T-SQL
        8. Backup Scenarios
          1. Full Database Backups Only
          2. Full Database Backups with Transaction Log Backups
          3. Differential Backups
          4. Partial Backups
          5. File/Filegroup Backups
          6. Mirrored Backups
          7. Copy-Only Backups
          8. Compressed Backups
          9. System Database Backups
        9. Restoring Databases and Transaction Logs
          1. Restores with T-SQL
          2. Restoring by Using SSMS
          3. Restore Information
        10. Restore Scenarios
          1. Restoring to a Different Database
          2. Restoring a Snapshot
          3. Restoring a Transaction Log
          4. Restoring to the Point of Failure
          5. Restoring to a Point in Time
          6. Online Restores
          7. Restoring the System Databases
        11. Additional Backup Considerations
          1. Frequency of Backups
          2. Using a Standby Server
          3. Snapshot Backups
          4. Considerations for Very Large Databases
          5. Maintenance Plans
        12. Summary
      6. Chapter 12. Database Mail
        1. What’s New in Database Mail
        2. Setting Up Database Mail
          1. Creating Mail Profiles and Accounts
          2. Using T-SQL to Update and Delete Mail Objects
          3. Setting System-Wide Mail Settings
          4. Testing Your Setup
        3. Sending and Receiving with Database Mail
          1. The Service Broker Architecture
          2. Sending Email
          3. Receiving Email
        4. Using SQL Server Agent Mail
          1. Job Mail Notifications
          2. Creating an Operator
          3. Enabling SQL Agent Mail
          4. Creating the Job
          5. Testing the Job-Completion Notification
          6. Alert Mail Notifications
          7. Creating an Alert
          8. Testing the Alert Notification
        5. Related Views and Procedures
          1. Viewing the Mail Configuration Objects
          2. Viewing Mail Message Data
        6. Summary
      7. Chapter 13. SQL Server Agent
        1. What’s New in Scheduling and Notification
        2. Configuring the SQL Server Agent
          1. Configuring SQL Server Agent Properties
          2. Configuring the SQL Server Agent Startup Account
          3. Configuring Email Notification
          4. SQL Server Agent Proxy Account
        3. Viewing the SQL Server Agent Error Log
        4. SQL Server Agent Security
        5. Managing Operators
        6. Managing Jobs
          1. Defining Job Properties
          2. Defining Job Steps
          3. Defining Multiple Jobs Steps
          4. Defining Job Schedules
          5. Defining Job Notifications
          6. Viewing Job History
        7. Managing Alerts
          1. Defining Alert Properties
          2. Defining Alert Responses
        8. Scripting Jobs and Alerts
        9. Multiserver Job Management
          1. Creating a Master Server
          2. Enlisting Target Servers
          3. Creating Multiserver Jobs
        10. Event Forwarding
        11. Summary
      8. Chapter 14. Administering SQL Server 2012 with PowerShell
        1. What’s New with PowerShell
        2. Overview of PowerShell
          1. Start Using PowerShell Now
          2. Common Terminology
          3. Object-Based Functionality
          4. SQL Server Management Objects
          5. WMI
          6. Installing PowerShell
          7. PowerShell Console
          8. Scriptable and Interactive
          9. Default Security
          10. Execution Policy
          11. Profiles
          12. Built-In Help Features
        3. PowerShell Scripting Basics
          1. A Few Basic Cmdlets
          2. Creating a PowerShell Script
          3. Adding Comments
          4. Variables
          5. Escaping Characters
          6. Special Variable $_
          7. Joining Variables and Strings
          8. Passing Arguments
          9. Using Param
          10. Arrays
          11. Operators
          12. Conditional Statements
          13. Functions
          14. Looping Statements
          15. Filtering Cmdlets
          16. Formatting Cmdlets
          17. Dealing with CSV Files
          18. Dealing with Dates and Times
          19. -WhatIf/-Confirm Parameters
        4. PowerShell in SQL Server 2012
          1. Adding PowerShell Support
          2. Accessing SQL Server PowerShell
          3. SQL Server PowerShell
          4. SQL Provider
          5. SQL Cmdlets
          6. SQL Server Agent Support
        5. Step-by-Step Examples
          1. General Tasks
          2. Scheduling Scripts
          3. Common OS-Related Tasks
          4. SQL Server-Specific Tasks
          5. Using the Provider
          6. Creating a Database Table
          7. Performing a Database Backup
          8. Checking Server Settings
          9. Checking the Database Usage
          10. Getting Table Properties
          11. Cmdlet Example: Invoke-SqlCmd
          12. Cmdlet Example: Invoke-PolicyEvaluation
          13. Joining Columns
          14. Retrieving an Entry
        6. Summary
      9. Chapter 15. SQL Server Policy-Based Management
        1. What’s New in Policy-Based Management
        2. Introduction to Policy-Based Management
        3. Policy-Based Management Concepts
          1. Facets
          2. Conditions
          3. Policies
          4. Categories
          5. Targets
          6. Execution Modes
          7. Central Management Servers
        4. Implementing Policy-Based Management
          1. Creating a Condition Based on a Facet
          2. Creating a Policy
          3. Creating a Category
          4. Evaluating Policies
          5. Importing and Exporting Policies
        5. Sample Templates and Real-World Examples
          1. Sample Policy Templates
          2. Evaluating Recovery Models
          3. Implementing Surface Area Configuration Checks
          4. Ensuring Object Naming Conventions
          5. Checking Best Practices Compliance
        6. Policy-Based Management Best Practices
        7. Summary
    16. Part IV: SQL Server Security Administration
      1. Chapter 16. Security and Compliance
        1. Exposure and Risk
        2. Across the Life Cycle
        3. The Security Big Picture
        4. Identity Access Management Components
        5. Data Security Compliance and SQL Server
        6. SQL Server Auditing
        7. Setting Up Auditing via T-SQL
        8. SQL Injection Is Easy to Do
        9. Summary
      2. Chapter 17. Security and User Administration
        1. What’s New in Security and User Administration
        2. An Overview of SQL Server Security
        3. Authentication Methods
          1. Windows Authentication Mode
          2. Mixed Authentication Mode
          3. Setting the Authentication Mode
        4. Managing Principals
          1. Logins
          2. SQL Server Security: Users
          3. The dbo User
          4. The guest User
          5. The INFORMATION_SCHEMA User
          6. The sys User
          7. User/Schema Separation
          8. Roles
          9. Fixed Server Roles
          10. Fixed Database Roles
          11. The public Role
          12. User-Defined Database Roles
          13. User-Defined Server Roles
          14. Application Roles
        5. Managing Securables
        6. Managing Permissions
        7. Managing SQL Server Logins
          1. Using SSMS to Manage Logins
          2. Using T-SQL to Manage Logins
        8. Managing SQL Server Users
          1. Using SSMS to Manage Users
          2. Using T-SQL to Manage Users
        9. Managing Database Roles
          1. Using SSMS to Manage Database Roles
          2. Using T-SQL to Manage Database Roles
        10. Managing Server Roles
          1. Using SSMS to Manage Server Roles
          2. Using T-SQL to Manage Server Roles
        11. Managing SQL Server Permissions
          1. Using SSMS to Manage Permissions
          2. Using SSMS to Manage Permissions at the Server Level
          3. Using SSMS to Manage Permissions at the Database Level
          4. Using SSMS to Manage Permissions at the Object Level
          5. Using T-SQL to Manage Permissions
        12. The Execution Context
          1. Explicit Context Switching
          2. Implicit Context Switching
        13. Summary
      3. Chapter 18. Data Encryption
        1. What’s New in Data Encryption
        2. An Overview of Data Security
        3. An Overview of Data Encryption
        4. SQL Server Key Management
          1. Extensible Key Management
        5. Column-Level Encryption
          1. Encrypting Columns Using a Passphrase
          2. Encrypting Columns Using a Certificate
        6. Transparent Data Encryption
          1. Implementing Transparent Data Encryption
          2. Managing TDE in SSMS
          3. Backing Up TDE Certificates and Keys
          4. The Limitations of TDE
        7. Column-Level Encryption Versus Transparent Data Encryption
        8. Summary
    17. Part V: Database Administration
      1. Chapter 19. Creating and Managing Databases
        1. What’s New in Creating and Managing Databases
        2. Data Storage in SQL Server
        3. Database Files
          1. Primary Files
          2. Secondary Files
          3. Using Filegroups
          4. Using Partitions
          5. Transaction Log Files
        4. Creating Databases
          1. Using SSMS to Create a Database
          2. Using T-SQL to Create Databases
        5. Setting Database Options
          1. The Database Options
          2. Using T-SQL to Set Database Options
          3. Retrieving Option Information
        6. Managing Databases
          1. Managing File Growth
          2. Expanding Databases
          3. Shrinking Databases
          4. Moving Databases
          5. Restoring a Database to a New Location
          6. Using ALTER DATABASE
          7. Detaching and Attaching Databases
        7. Contained Databases
          1. Creating a Contained Database
          2. Connecting to a Contained Database
        8. Summary
      2. Chapter 20. Creating and Managing Tables
        1. What’s New in SQL Server 2012
        2. Creating Tables
          1. Using Object Explorer to Create Tables
          2. Using Database Diagrams to Create Tables
          3. Using T-SQL to Create Tables
        3. Defining Columns
          1. Data Types
          2. Column Properties
          3. Column Sets
          4. Working with Sparse Columns
          5. Sparse Columns: Good or Bad?
          6. Defining Sparse Columns in SSMS
        4. Defining Table Location
        5. Defining Table Constraints
        6. Modifying Tables
          1. Using T-SQL to Modify Tables
          2. Using Object Explorer and the Table Designer to Modify Tables
          3. Using Database Diagrams to Modify Tables
        7. Dropping Tables
        8. Using Partitioned Tables
          1. Creating a Partition Function
          2. Creating a Partition Scheme
          3. Creating a Partitioned Table
          4. Adding and Dropping Table Partitions
          5. Switching Table Partitions
        9. Using FILESTREAM Storage
          1. Enabling FILESTREAM Storage
          2. Setting Up a Database for FILESTREAM Storage
          3. Using FILESTREAM Storage for Data Columns
        10. Using FileTables
          1. FileTable Prerequisites
          2. Creating FileTables
          3. Copying Files to the FileTable
        11. Creating Temporary Tables
        12. Summary
      3. Chapter 21. Creating and Managing Indexes
        1. What’s New in Creating and Managing Indexes
        2. Types of Indexes
          1. Clustered Indexes
          2. Nonclustered Indexes
        3. Creating Indexes
          1. Creating Indexes with T-SQL
          2. Creating Indexes with SSMS
        4. Managing Indexes
          1. Managing Indexes with T-SQL
          2. Managing Indexes with SSMS
        5. Dropping Indexes
        6. Online Indexing Operations
        7. Indexes on Views
        8. Summary
      4. Chapter 22. Implementing Data Integrity
        1. What’s New in Data Integrity
        2. Types of Data Integrity
          1. Domain Integrity
          2. Entity Integrity
          3. Referential Integrity
        3. Enforcing Data Integrity
          1. Implementing Declarative Data Integrity
          2. Implementing Procedural Data Integrity
        4. Using Constraints
          1. The PRIMARY KEY Constraint
          2. The UNIQUE Constraint
          3. The FOREIGN KEY Referential Integrity Constraint
          4. The CHECK Constraint
          5. Creating Constraints
          6. Managing Constraints
        5. Rules
        6. Defaults
          1. Declarative Defaults
          2. Bound Defaults
        7. When a Default Is Applied
          1. Restrictions on Defaults
        8. Summary
      5. Chapter 23. Creating and Managing Views
        1. What’s New in Creating and Managing Views
        2. Definition of Views
        3. Using Views
          1. Simplifying Data Manipulation
          2. Focusing on Specific Data
          3. Abstracting Data
          4. Controlling Access to Data
        4. Creating Views
          1. Creating Views Using T-SQL
          2. ENCRYPTION
          3. Creating Views Using the View Designer
        5. Managing Views
          1. Altering Views with T-SQL
          2. Dropping Views with T-SQL
          3. Managing Views with SSMS
        6. Data Modifications and Views
        7. Partitioned Views
          1. Modifying Data Through a Partitioned View
          2. Distributed Partitioned Views
        8. Indexed Views
          1. Creating Indexed Views
          2. Indexed Views and Performance
          3. To Expand or Not to Expand
        9. Summary
      6. Chapter 24. Creating and Managing Stored Procedures
        1. What’s New in Creating and Managing Stored Procedures
        2. Advantages of Stored Procedures
        3. Creating Stored Procedures
          1. Creating Procedures in SSMS
        4. Executing Stored Procedures
          1. Executing Procedures in SSMS
          2. Execution Context and the EXECUTE AS Clause
          3. Using the WITH RESULT SETS Clause
        5. Deferred Name Resolution
          1. Identifying Objects Referenced in Stored Procedures
        6. Viewing Stored Procedures
        7. Modifying Stored Procedures
          1. Viewing and Modifying Stored Procedures with SSMS
        8. Using Input Parameters
          1. Setting Default Values for Parameters
          2. Passing Object Names as Parameters
          3. Using Wildcards in Parameters
          4. Using Table-Valued Parameters
        9. Using Output Parameters
        10. Returning Procedure Status
        11. Debugging Stored Procedures Using SQL Server Management Studio
        12. Using System Stored Procedures
        13. Startup Procedures
        14. Summary
      7. Chapter 25. Creating and Managing User-Defined Functions
        1. Why Use User-Defined Functions?
        2. Types of User-Defined Functions
          1. Scalar Functions
          2. Table-Valued Functions
        3. Creating and Managing User-Defined Functions
          1. Creating User-Defined Functions
          2. Viewing and Modifying User-Defined Functions
          3. Managing User-Defined Function Permissions
        4. Rewriting Stored Procedures as Functions
        5. Creating and Using CLR Functions
          1. Adding CLR Functions to a Database
          2. Deciding Between Using T-SQL or CLR Functions
        6. Summary
      8. Chapter 26. Creating and Managing Triggers
        1. What’s New in Creating and Managing Triggers
        2. Using DML Triggers
          1. Creating DML Triggers
          2. Using AFTER Triggers
          3. Using inserted and deleted Tables
          4. INSTEAD OF Triggers
        3. Using DDL Triggers
          1. Creating DDL Triggers
          2. Managing DDL Triggers
        4. Using CLR Triggers
        5. Using Nested Triggers
        6. Using Recursive Triggers
        7. Summary
      9. Chapter 27. Transaction Management and the Transaction Log
        1. What’s New in Transaction Management
        2. What Is a Transaction?
        3. How SQL Server Manages Transactions
        4. Defining Transactions
          1. AutoCommit Transactions
          2. Explicit User-Defined Transactions
          3. Implicit Transactions
          4. Implicit Transactions Versus Explicit Transactions
        5. Transactions and Batches
        6. Transactions and Stored Procedures
        7. Transactions and Triggers
          1. Triggers and Transaction Nesting
          2. Triggers and Multistatement Transactions
          3. Using Savepoints in Triggers
        8. Transactions and Locking
          1. READ_COMMITTED_SNAPSHOT Isolation
        9. Coding Effective Transactions
        10. Transaction Logging and the Recovery Process
          1. The Checkpoint Process
          2. Automatic Checkpoints
          3. Indirect Checkpoints
          4. Manual Checkpoints
          5. The Recovery Process
          6. Managing the Transaction Log
        11. Long-Running Transactions
        12. Distributed Transactions
        13. Summary
      10. Chapter 28. Database Snapshots
        1. What’s New with Database Snapshots
        2. What Are Database Snapshots?
        3. Limitations and Restrictions of Database Snapshots
        4. Copy-on-Write Technology
        5. When to Use Database Snapshots
          1. Reverting to a Snapshot for Recovery Purposes
          2. Safeguarding a Database Prior to Making Mass Changes
          3. Providing a Testing (or Quality Assurance) Starting Point (Baseline)
          4. Providing a Point-in-Time Reporting Database
          5. Providing a Highly Available and Offloaded Reporting Database from a Database Mirror
        6. Setup and Breakdown of a Database Snapshot
          1. Creating a Database Snapshot
          2. Removing a Database Snapshot
        7. Reverting to a Database Snapshot for Recovery
          1. Reverting a Source Database from a Database Snapshot
          2. Using Database Snapshots with Testing and QA
        8. Database Snapshots Maintenance and Security Considerations
          1. Security for Database Snapshots
          2. Snapshot Sparse File Size Management
          3. Number of Database Snapshots per Source Database
        9. Summary
      11. Chapter 29. Database Maintenance
        1. What’s New in Database Maintenance
        2. The Maintenance Plan Wizard
          1. Backing Up Databases
          2. Checking Database Integrity
          3. Shrinking Databases
          4. Maintaining Indexes and Statistics
          5. Scheduling a Maintenance Plan
        3. Managing Maintenance Plans Without the Wizard
        4. Executing a Maintenance Plan
        5. Maintenance Without a Maintenance Plan
        6. Database Maintenance Policies
        7. Summary
    18. Part VI: SQL Server Performance and Optimization
      1. Chapter 30. Data Structures, Indexes, and Performance
        1. What’s New for Data Structures, Indexes, and Performance
        2. Understanding Data Structures
        3. Database Files and Filegroups
          1. Primary Data File
          2. Secondary Data Files
          3. The Log File
          4. File Management
          5. Using Filegroups
          6. FILESTREAM Filegroups
        4. Database Pages
          1. Page Types
          2. Data Pages
          3. Row-Overflow Pages
          4. LOB Data Pages
          5. Index Pages
        5. Space Allocation Structures
          1. Extents
          2. Global and Shared Global Allocation Map Pages
          3. Page Free Space Pages
          4. Index Allocation Map Pages
          5. Differential Changed Map Pages
          6. Bulk Changed Map Pages
        6. Data Compression
          1. Row-Level Compression
          2. Page-Level Compression
          3. The CI Record
          4. Implementing Page Compression
          5. Evaluating Page Compression
          6. Managing Data Compression with SSMS
        7. Understanding Table Structures
          1. Heap Tables
          2. Clustered Tables
        8. Understanding Index Structures
          1. Clustered Indexes
          2. Nonclustered Indexes
          3. Columnstore Indexes
        9. Data Modification and Performance
          1. Inserting Data
          2. Deleting Rows
          3. Updating Rows
        10. Index Utilization
        11. Index Selection
        12. Evaluating Index Usefulness
        13. Index Statistics
          1. The Statistics Histogram
          2. How the Statistics Histogram Is Used
          3. Index Densities
          4. Estimating Rows Using Index Statistics
          5. Generating and Maintaining Index and Column Statistics
        14. SQL Server Index Maintenance
          1. Setting the Fill Factor
          2. Reapplying the Fill Factor
          3. Disabling Indexes
          4. Managing Indexes with SSMS
        15. Index Design Guidelines
          1. Clustered Index Indications
          2. Nonclustered Index Indications
          3. Index Covering
          4. Included Columns
          5. Wide Indexes Versus Multiple Indexes
        16. Indexed Views
        17. Indexes on Computed Columns
        18. Filtered Indexes and Statistics
          1. Creating and Using Filtered Indexes
          2. Creating and Using Filtered Statistics
        19. Choosing Indexes: Query Versus Update Performance
        20. Identifying Missing Indexes
          1. The Database Engine Tuning Advisor
          2. Missing Index Dynamic Management Objects
          3. Missing Index Feature Versus Database Engine Tuning Advisor
        21. Identifying Unused Indexes
        22. Summary
      2. Chapter 31. Understanding Query Optimization
        1. What’s New in Query Optimization
        2. What Is the Query Optimizer?
        3. Query Compilation and Optimization
          1. Compiling DML Statements
          2. Optimization Steps
        4. Query Analysis
          1. Identifying Search Arguments
          2. Identifying OR Clauses
          3. Identifying Join Clauses
        5. Row Estimation and Index Selection
          1. Evaluating SARG and Join Selectivity
          2. Estimating Access Path Cost
          3. Using Multiple Indexes
          4. Optimizing with Indexed Views
          5. Optimizing with Filtered Indexes
        6. Join Selection
          1. Join Processing Strategies
          2. Determining the Optimal Join Order
          3. Subquery Processing
        7. Execution Plan Selection
        8. Query Plan Caching
          1. Query Plan Reuse
          2. Query Plan Aging
          3. Recompiling Query Plans
          4. Monitoring the Plan Cache
        9. Other Query Processing Strategies
          1. Predicate Transitivity
          2. Group by Optimization
          3. Queries with DISTINCT
          4. Queries with UNION
          5. Queries Using Columnstore Indexes
        10. Parallel Query Processing
          1. Parallel Query Configuration Options
          2. Identifying Parallel Queries
        11. Common Query Optimization Problems
          1. Out-of-Date or Insufficient Statistics
          2. Poor Index Design
          3. Search Argument Problems
          4. Large Complex Queries
          5. Triggers
        12. Managing the Optimizer
          1. Optimizer Hints
          2. Forced Parameterization
          3. Using the USE PLAN Query Hint
          4. Using Plan Guides
          5. Limiting Query Plan Execution with the Query Governor
        13. Summary
      3. Chapter 32. Query Analysis
        1. What’s New in Query Analysis
        2. Query Analysis in SSMS
          1. Execution Plan ToolTips
          2. Logical and Physical Operator Icons
          3. Analyzing Stored Procedures
          4. Saving and Viewing Graphical Execution Plans
          5. Displaying Execution Plan XML
          6. Missing Index Hints
        3. SSMS Client Statistics
        4. Using the SET SHOWPLAN Options
          1. SHOWPLAN_TEXT
          2. SHOWPLAN_ALL
          3. SHOWPLAN_XML
        5. Using sys.dm_exec_query_plan
        6. Query Statistics
          1. STATISTICS IO
          2. STATISTICS TIME
          3. Using datediff() to Measure Runtime
          4. STATISTICS PROFILE
          5. STATISTICS XML
        7. Query Analysis with SQL Server Profiler
        8. Summary
      4. Chapter 33. Locking and Performance
        1. What’s New in Locking and Performance
        2. The Need for Locking
        3. Transaction Isolation Levels in SQL Server
          1. Read Uncommitted Isolation
          2. Read Committed Isolation
          3. Read Committed Snapshot Isolation
          4. Repeatable Read Isolation
          5. Serializable Read Isolation
          6. Snapshot Isolation
        4. The Lock Manager
        5. Monitoring Lock Activity in SQL Server
          1. Querying the sys.dm_tran_locks View
          2. Viewing Locking Activity with SQL Server Profiler
          3. Monitoring Locks with Performance Monitor
        6. SQL Server Lock Types
          1. Shared Locks
          2. Update Locks
          3. Exclusive Locks
          4. Intent Locks
          5. Schema Locks
          6. Bulk Update Locks
        7. SQL Server Lock Granularity
          1. Serialization and Key-Range Locking
          2. Using Application Locks
          3. Index Locking
          4. Row-Level Versus Page-Level Locking
          5. Lock Escalation
        8. Lock Compatibility
        9. Locking Contention and Deadlocks
          1. Identifying Locking Contention
          2. Setting the Lock Timeout Interval
          3. Minimizing Locking Contention
          4. Deadlocks
        10. Table Hints for Locking
          1. Transaction Isolation-Level Hints
          2. Lock Granularity Hints
          3. Lock Type Hints
        11. Optimistic Locking
          1. Optimistic Locking Using the rowversion Data Type
          2. Optimistic Locking with Snapshot Isolation
        12. Summary
      5. Chapter 34. Database Design and Performance
        1. What’s New in Database Design and Performance
        2. Basic Tenets of Designing for Performance
        3. Logical Database Design Issues
          1. Normalization Conditions
          2. Normalization Forms
          3. Benefits of Normalization
          4. Drawbacks of Normalization
        4. Denormalizing a Database
          1. Denormalization Guidelines
          2. Essential Denormalization Techniques
        5. Database Filegroups and Performance
        6. RAID Technology
          1. RAID Level 0
          2. RAID Level 1
          3. RAID Level 10
          4. RAID Level 5
        7. SQL Server and SAN Technology
          1. What Is a SAN?
          2. SAN Considerations for SQL Server
        8. Summary
      6. Chapter 35. Monitoring SQL Server Performance
        1. What’s New in Monitoring SQL Server Performance
        2. Performance Monitoring Tools
          1. The Data Collector and the MDW
          2. SQL Server Utility
          3. SQL Server Extended Events
          4. Windows Performance Monitor
        3. A Performance Monitoring Approach
          1. Monitoring the Network Interface
          2. Monitoring the Processors
          3. Monitoring Memory
          4. Monitoring the Disk System
          5. Monitoring SQL Server’s Disk Activity
          6. Monitoring Other SQL Server Performance Items
        4. Summary
      7. Chapter 36. SQL Server Database Engine Tuning Advisor
        1. What’s New in SQL Server Database Engine Tuning Advisor
        2. SQL Server Instance Architecture
        3. Database Engine Tuning Advisor
          1. The Database Engine Tuning Advisor GUI
          2. The Database Engine Tuning Advisor Command Line
        4. Summary
      8. Chapter 37. Managing Workloads with the Resource Governor
        1. What’s New for Resource Governor
        2. Overview of Resource Governor
        3. Resource Governor Components
          1. Classification
          2. Resource Pools
          3. Workload Groups
        4. Configuring Resource Governor
          1. Enabling Resource Governor
          2. Defining Resource Pools
          3. Defining Workload Groups
          4. Creating a Classifier Function
        5. Monitoring Resource Usage
        6. Modifying Your Resource Governor Configuration
          1. Deleting Workload Groups
          2. Deleting Resource Pools
          3. Modifying a Classifier Function
        7. Summary
      9. Chapter 38. A Performance and Tuning Methodology
        1. The Full Architectural Landscape
        2. Primary Performance and Tuning Handles
        3. A Performance and Tuning Methodology
          1. Designing In Performance and Tuning from the Start
          2. Code and Test
          3. Performance and Tuning for an Existing Implementation
        4. Performance and Tuning Design Guidelines
          1. Hardware and Operating System Guidelines
          2. SQL Server Instance Guidelines
          3. Database-Level Guidelines
          4. Table Design Guidelines
          5. Indexing Guidelines
          6. View Design Guidelines
          7. Transact-SQL Guidelines
          8. Application Design Guidelines
          9. Distributed Data Guidelines
          10. High-Availability Guidelines
        5. Tools of the Performance and Tuning Trade
          1. Microsoft Out-of-the-Box
          2. Third-Party Performance and Tuning Tools
        6. Summary
    19. Part VII: SQL Server High Availability
      1. Chapter 39. SQL Server High Availability Fundamentals
        1. What’s New in High Availability
        2. What Is High Availability?
        3. The Fundamentals of HA
          1. Hardware Factors
          2. Backup Considerations
          3. Operating System Upgrades
          4. Vendor Agreements Followed
          5. Training Kept Up-to-Date
          6. Quality Assurance Done Well
          7. Standards/Procedures Followed
          8. Server Instance Isolation
        4. Building Solutions with One or More HA Options
          1. Failover Cluster Services (FCS)
          2. SQL Clustering
          3. AlwaysOn Failover Clustering Instance (FCI)
          4. AlwaysOn Availability Groups
          5. Data Replication
          6. Change Data Capture
          7. Log Shipping
          8. Database Mirroring
          9. Combining Failover with Scale-Out Options
        5. Other HA Techniques That Yield Great Results
        6. High Availability from the Windows Server Family Side
          1. Microsoft Virtual Machines and Hyper-V
        7. Summary
      2. Chapter 40. Data Replication
        1. What’s New in Data Replication
        2. What Is Replication?
        3. The Publisher, Distributor, and Subscriber Magazine Metaphor
          1. Publications and Articles
          2. Filtering Articles
        4. Replication Scenarios
          1. The Central Publisher Replication Model
          2. The Central Publisher with Remote Distributor Replication Model
          3. The Publishing Subscriber Replication Model
          4. The Central Subscriber Replication Model
          5. The Multiple Publishers with Multiple Subscribers Replication Model
          6. The Updating Subscribers Replication Model
          7. The Peer-to-Peer Replication Model
        5. Subscriptions
          1. Anonymous Subscriptions (Pull Subscriptions)
          2. The Distribution Database
        6. Replication Agents
          1. The Snapshot Agent
          2. The Log Reader Agent
          3. The Distribution Agent
          4. The Merge Agent
          5. Other Specialized Agents
        7. Planning for SQL Server Data Replication
          1. Autonomy, Timing, and Latency of Data
          2. Methods of Data Distribution
        8. SQL Server Replication Types
          1. Snapshot Replication
          2. Transactional Replication
          3. Merge Replication
        9. Basing the Replication Design on User Requirements
          1. Data Characteristics
        10. Setting Up Replication
          1. Creating a Distributor and Enabling Publishing
          2. Creating a Publication
          3. Horizontal and Vertical Filtering
          4. Creating Subscriptions
        11. Scripting Replication
        12. Monitoring Replication
          1. Replication Monitoring SQL Statements
          2. Monitoring Replication within SQL Server Management Studio
          3. Troubleshooting Replication Failures
          4. Peer-to-Peer Replication
          5. The Performance Monitor
          6. Replication in Heterogeneous Environments
          7. Backup and Recovery in a Replication Configuration
          8. Some Thoughts on Performance
          9. Log Shipping
          10. Data Replication and Database Mirroring or AlwaysOn Features for Fault Tolerance and High Availability
          11. Change Data Capture
          12. The Change Data Capture Tables
          13. Enabling CDC for a Database
          14. Enabling CDC for a Table
          15. Querying the CDC Tables
          16. CDC and DDL Changes to Source Tables
          17. CDC and AlwaysOn Availability Groups
        13. Change Tracking
          1. Implementing Change Tracking
          2. Identifying Tracked Changes
          3. Identifying Changed Columns
          4. Change Tracking Overhead
        14. Summary
      3. Chapter 41. Database Mirroring
        1. What’s New in Database Mirroring
        2. What Is Database Mirroring?
          1. Copy-on-Write Technology
          2. When to Use Database Mirroring
        3. Roles of the Database Mirroring Configuration
          1. Playing Roles and Switching Roles
          2. Database Mirroring Operating Modes
        4. Setting Up and Configuring Database Mirroring
          1. Getting Ready to Mirror a Database
          2. Creating the Endpoints
          3. Granting Permissions
          4. Creating the Database on the Mirror Server
          5. Identifying the Other Endpoints for Database Mirroring
          6. Configuring Database Mirroring by Using the Wizard
          7. Monitoring a Mirrored Database Environment
          8. Removing Mirroring
        5. Testing Failover from the Principal to the Mirror
        6. Client Setup and Configuration for Database Mirroring
        7. Using Replication and Database Mirroring Together
        8. Using Database Snapshots from a Mirror for Reporting
        9. Summary
      4. Chapter 42. SQL Server Failover Clustering
        1. What’s New in SQL Server Failover Clustering
        2. How Microsoft SQL Server Failover Clustering Works
          1. Understanding WSFC
          2. Extending WSFC with NLB
          3. How WSFC Sets the Stage for SQL Server Clustering
        3. Installing SQL Server Failover Clustering
          1. Configuring SQL Server Database Disks
          2. Installing Network Interfaces
          3. Installing WSFC
          4. Cluster Events
          5. Installing SQL Server within WSFC
          6. Fail Over to Another Node
          7. The Client Connection Impact of a Failover
          8. Potential Problems to Watch Out for with SQL Server Failover Clustering
        4. Summary
      5. Chapter 43. SQL Server AlwaysOn and Availability Groups
        1. SQL Server AlwaysOn and Availability Groups
          1. Windows Failover Cluster Services
          2. AlwaysOn Failover Clustering Instances
          3. AlwaysOn Availability Groups
          4. Combining Failover with Scale-Out Options
          5. Building a Multinode AlwaysOn Configuration
          6. Adding Replicas
          7. Dashboard and Monitoring
        2. Summary
    20. Part VIII: SQL Server Application Development
      1. Chapter 44. What’s New for Transact-SQL in SQL Server 2012
        1. THROW Statement
        2. The SEQUENCE Object
          1. SEQUENCE Objects Versus Identity Columns
        3. New Conversion Functions
          1. The PARSE Function
          2. The TRY_PARSE Function
          3. The TRY_CONVERT Function
        4. New Logical Functions
          1. The CHOOSE Function
          2. The IIF Function
        5. New String Functions
          1. CONCAT
          2. FORMAT
        6. New date and time Functions
          1. Some Recently Added Date and Time Features You May Have Missed
          2. Date and Time Conversions
        7. SQL Server 2012 Enhancements to Windowing Functions
          1. The OVER Clause
          2. Ranking Functions
          3. Calculating Aggregates with the OVER Clause
          4. SQL Server 2012 Enhancements to Windowing Functions
        8. Ad Hoc Query Paging
        9. New Features and Enhancements to Spatial Data
          1. The Spatial Data Types
          2. Spatial Instance Types
          3. Other SQL Server 2012 Enhancements
          4. Representing Spatial Data in SQL Server 2012
          5. Working with Geometry Data
          6. Working with Geography Data
          7. Spatial Data Support in SSMS
          8. Spatial Data Types: Where to Go from Here?
        10. Summary
      2. Chapter 45. Transact-SQL Programming Guidelines, Tips, and Tricks
        1. General T-SQL Coding Recommendations
          1. Provide Explicit Column Lists
          2. Qualify Object Names with a Schema Name
          3. Avoid SQL Injection Attacks When Using Dynamic SQL
          4. Comment Your T-SQL Code
        2. General T-SQL Performance Recommendations
          1. UNION Versus UNION ALL Performance
          2. Use IF EXISTS Instead of SELECT COUNT(*)
          3. Avoid Unnecessary ORDER BY or DISTINCT Clauses
          4. Temp Tables Versus Table Variables Versus Common Table Expressions
          5. Avoid Unnecessary Function Executions
          6. Cursors and Performance
          7. Variable Assignment in UPDATE Statements
        3. In Case You Missed It: Recently Added T-SQL Features
        4. TOP Enhancements
        5. The OUTPUT Clause
        6. MERGE Statement
          1. MERGE Statement Best Practices and Guidelines
        7. Insert over DML
        8. Common Table Expressions
          1. Recursive Queries with CTEs
        9. Using the Hierarchyid Data Type
          1. Creating a Hierarchy
          2. Populating the Hierarchy
          3. Querying the Hierarchy
          4. Modifying the Hierarchy
        10. Using Row Numbers for Paging Results
        11. De-Duping Data with Ranking Functions
        12. PIVOT and UNPIVOT
        13. The APPLY Operator
          1. CROSS APPLY
          2. OUTER APPLY
        14. The TABLESAMPLE Clause
        15. Variable Assignment in DECLARE Statements
        16. Compound Assignment Operators
        17. GROUP BY Clause Enhancements
          1. GROUPING SETS
          2. The grouping_id() Function
        18. General T-SQL Tips and Tricks
          1. Date Calculations
          2. Using CONTEXT_INFO
          3. Working with Outer Joins
          4. Generating T-SQL Statements with T-SQL
        19. Summary
      3. Chapter 46. Advanced Stored Procedure Programming and Optimization
        1. T-SQL Stored Procedure Coding Guidelines
          1. Calling Stored Procedures from Transactions
          2. Handling Errors in Stored Procedures
        2. Using Cursors in Stored Procedures
          1. Using CURSOR Variables in Stored Procedures
        3. Nested Stored Procedures
          1. Recursive Stored Procedures
        4. Using Temporary Tables in Stored Procedures
          1. Temporary Table Performance Tips
          2. Using the table Data Type
        5. Stored Procedure Performance
          1. Query Plan Caching
          2. The SQL Server Plan Cache
          3. Shared Query Plans
          4. Automatic Query Plan Recompilation
          5. Forcing Recompilation of Query Plans
        6. Using Dynamic SQL in Stored Procedures
          1. Using sp_executesql
        7. Installing and Using .NET CLR Stored Procedures
          1. Adding CLR Stored Procedures to a Database
          2. T-SQL or CLR Stored Procedures?
        8. Using xp_cmdshell
        9. Summary
      4. Chapter 47. SQL Server and the .NET Framework
        1. What’s New in SQL Server 2012 and the .NET Framework
        2. Getting Comfortable with ADO.NET 4.5 and SQL Server 2012
          1. Essential ADO.NET Development Techniques
        3. Developing with LINQ to SQL
          1. Getting Started with LINQ to SQL
          2. Going Deeper
          3. Uncovering LINQ to SQL with LINQPad
        4. Introducing the Entity Framework
          1. Getting Started
          2. Data Operations with EF Entities
        5. Using WCF Data Services
          1. Getting Set Up
          2. Essentials
          3. Building Your Data Service
          4. CRUD Operations
        6. Summary
      5. Chapter 48. SQLCLR: Developing SQL Server Objects in .NET
        1. What’s New for SQLCLR in SQL Server 2012
        2. Developing Custom Managed Database Objects
          1. An Introduction to Custom Managed Database Objects
          2. Managed Object Permissions
          3. Developing Managed Objects with Visual Studio 2012
          4. Developing Managed Stored Procedures
          5. Developing Managed User-Defined Functions (UDFs)
          6. Developing Managed User-Defined Types (UDTs)
          7. Developing Managed User-Defined Aggregates (UDAs)
          8. Developing Managed Triggers
          9. Using Transactions
          10. Using the Related System Catalogs
        3. Summary
      6. Chapter 49. Using XML in SQL Server 2012
        1. What’s New in Using XML in SQL Server 2012
        2. Understanding XML
        3. Relational Data as XML: The FOR XML Modes
          1. RAW Mode
          2. AUTO Mode
          3. EXPLICIT Mode
          4. PATH Mode
          5. FOR XML and the xml Data Type
        4. XML as Relational Data: Using OPENXML
        5. Using the xml Data Type
          1. Defining and Using xml Columns
          2. Using XML Schema Collections
          3. The Built-in xml Data Type Methods
        6. Indexing and Full-Text Indexing of xml Columns
          1. Indexing xml Columns
          2. Full-Text Indexing xml Columns
        7. Summary
      7. Chapter 50. SQL Server Service Broker
        1. What’s New in Service Broker
        2. Understanding Distributed Messaging
          1. The Basics of Service Broker
        3. Example System Design
        4. Understanding Service Broker Constructs
          1. Defining Messages and Choosing a Message Type
          2. Setting Up Contracts for Communication
          3. Creating Queues for Message Storage
          4. Defining Services to Send and Receive Messages
          5. Planning Conversations Between Services
        5. Service Broker Routing and Security
          1. Using Certificates for Conversation Encryption
          2. A Final Note on the Sample System
        6. Troubleshooting SSB Applications with ssbdiagnose.exe
        7. AlwaysOn Availability Group Support
        8. Related System Catalogs
        9. Summary
      8. Chapter 51. SQL Server Full-Text Search
        1. What’s New in SQL Server 2012 Full-Text Search
        2. Installing SQL FTS
        3. Upgrade Options in SQL Server 2012
        4. How SQL Server FTS Works
          1. Indexing
          2. Searching
        5. Implementing SQL Server 2012 Full-Text Catalogs
        6. Setting Up a Full-Text Index
          1. Using T-SQL Commands to Build Full-Text Indexes
          2. Using the Full-Text Indexing Wizard to Build Full-Text Indexes and Catalogs
        7. Full-Text Searches
          1. CONTAINS and CONTAINSTABLE
          2. FREETEXT and FREETEXTTABLE
        8. Semantic Search
        9. Full-Text Search Maintenance
        10. Full-Text Search Performance
        11. Full-Text Search Troubleshooting
        12. Summary
      9. Chapter 52. Working with SQL Azure
        1. Setting Up Subscriptions, Servers, and Databases
          1. Setting Up Your Windows Azure Subscription
          2. Creating a Logical Server
        2. Managing Your Server
          1. Configuring Your Firewall
          2. Using SQL Server Management Studio
          3. Using Management Portal
        3. Working with Databases
          1. Understanding SQL Database Editions
          2. Managing Databases Using T-SQL
          3. Migrating Data into SQL Database
          4. Copying Databases
        4. Backing Up and Restoring Databases
          1. Using Database Copies for Backup and Restore
          2. Using BACPAC Files for Backup and Restore
        5. Managing Logins, Users, and Roles
          1. Understanding Roles
          2. Managing Logins and Users
        6. Using SQL Database with ADO.NET
          1. Connecting to SQL Database Using a Custom Windows Application
          2. Connectivity Limitations
        7. Understanding SQL Database Billing
          1. Baseline Billing
          2. Tracking Your Usage
        8. Understanding SQL Database Limitations
          1. Unsupported and Partially Supported Functionality
          2. References
        9. Summary
    21. Part IX: SQL Server Business Intelligence Features
      1. Chapter 53. SQL Server 2012 Analysis Services
        1. What’s New in SSAS
        2. Understanding SSAS and OLAP
        3. Understanding the SSAS Environment Wizards
          1. OLAP Versus OLTP
        4. An Analytics Design Methodology
          1. An Analytics Mini-Methodology
        5. An OLAP Requirements Example: CompSales International
          1. CompSales International Requirements
          2. OLAP Cube Creation
          3. Using SQL Server SSDT
          4. Creating an OLAP Database
          5. Defining Dimensions and Hierarchies
          6. Creating the Other Dimensions
          7. Creating the Cube
          8. Building and Deploying the Cube
          9. Populating the Cube with Data
          10. Aggregating Data Within the Cube
          11. Browsing Data in the Cube
          12. Delivering Data to Users
          13. ADO MD
          14. Query Analysis and Optimization
        6. Generating a Relational Database
          1. Limitations of a Relational Database
          2. Cube Perspectives
          3. Data Mining
          4. Security and Roles
          5. Tabular Models and SSAS
          6. OLAP Performance
        7. Summary
      2. Chapter 54. SQL Server Integration Services
        1. What’s New with SSIS
        2. SSIS Basics
        3. SSIS Architecture and Concepts
        4. SSIS Tools and Utilities
        5. A Data Transformation Requirement
        6. Running the SSIS Import and Export Wizard
        7. The SSIS Designer
        8. The Package Execution Utility
          1. The dtexec Utility
          2. Running Packages
          3. Running Package Examples
          4. The dtutil Utility
          5. dtutil Examples
        9. Change Data Capture and SSIS
        10. Using bcp
          1. Fundamentals of Exporting and Importing Data
          2. File Data Types
          3. Format Files
          4. Using Views
        11. Logged and Nonlogged Operations
          1. Batches
          2. Parallel Loading
          3. Supplying Hints to bcp
        12. Summary
      3. Chapter 55. SQL Server 2012 Reporting Services
        1. What’s New in SSRS 2012
          1. Discontinued Functionality and Breaking Changes
        2. Reporting Services Architecture
          1. HTTP Architecture
        3. Installing and Configuring SSRS
          1. The Installation Sequence
          2. SSRS Configuration Using RSCM
        4. Developing Reports
          1. Tools of the Trade
          2. Report Basics
          3. Overview of the Report Development Process
          4. Data Planning and Preparation
          5. Using Shared Data Sources
          6. Using Datasets
          7. Using Shared Datasets
          8. Developing Reports Using SSDT
          9. Working with the Tablix
          10. Understanding Expressions
          11. Report Design Fundamentals
          12. Designing Reports Using Report Builder
          13. Report Builder and Report Model Security
          14. Enabling Report Builder
        5. Management and Security
          1. Securing Reports
          2. Subscriptions
          3. Report Execution Options
        6. Performance and Monitoring
          1. SSRS Trace Log
          2. Execution Log
          3. Windows Event Log
          4. Performance Counters
        7. Summary
      4. Chapter 56. Master Data Services and Data Quality Services
        1. Master Data Services
        2. Data Quality Services
        3. What’s New in MDS and DQS
        4. Master Data Management
          1. Master Data Services Terms and Concepts
          2. Master Data Services
          3. Data Quality Services
        5. Summary
      5. Chapter 57. Parallel Data Warehouse
        1. What’s New in Parallel Data Warehouse
        2. Understanding MPP and PDW
          1. MPP Architecture
          2. The PDW
          3. Data on a PDW
          4. PDW and Big Data (Hadoop)
          5. xVelocity Columnstore Indexes
          6. Columnstore Indexes
        3. Summary
    22. Part X: Bonus Chapters
      1. Chapter 58. Managing Linked Servers
        1. What’s New in Managing Linked Servers
        2. Linked Servers
          1. Distributed Queries
          2. Distributed Transactions
        3. Adding, Dropping, and Configuring Linked Servers
          1. sp_addlinkedserver
          2. sp_linkedservers
          3. sp_dropserver
          4. sp_serveroption
        4. Mapping Local Logins to Logins on Linked Servers
          1. sp_addlinkedsrvlogin
          2. sp_droplinkedsrvlogin
          3. sp_helplinkedsrvlogin
        5. Obtaining General Information About Linked Servers
        6. Executing a Stored Procedure via a Linked Server
        7. Setting Up Linked Servers Using SQL Server Management Studio
        8. Summary
      2. Chapter 59. SQL Server Disaster Recovery Planning
        1. What’s New in SQL Server Disaster Recovery Planning
        2. How to Approach Disaster Recovery
          1. Disaster Recovery Patterns
          2. Recovery Objectives
          3. A Data-Centric Approach to Disaster Recovery
        3. Microsoft SQL Server Options for Disaster Recovery
          1. Data Replication
          2. Log Shipping
          3. Database Mirroring and Snapshots
          4. Change Data Capture
          5. AlwaysOn Availability Groups
        4. The Overall Disaster Recovery Process
          1. The Focus of Disaster Recovery
          2. Planning and Executing a Disaster Recovery
        5. Have You Detached a Database Recently?
        6. Third-Party Disaster Recovery Alternatives
        7. Summary
      3. Chapter 60. SQL Server Configuration Options
        1. What’s New in Configuring, Tuning, and Optimizing SQL Server Options
        2. SQL Server Instance Architecture
        3. Configuration Options
        4. Fixing an Incorrect Option Setting
        5. Setting Configuration Options with SSMS
        6. Obsolete Configuration Options
        7. Configuration Options and Performance
          1. access check cache bucket count
          2. access check cache quota
          3. ad hoc distributed queries
          4. affinity I/O mask
          5. affinity mask
          6. Agent XP
          7. backup compression default
          8. blocked process threshold
          9. c2 audit mode
          10. clr enabled
          11. common criteria compliance enabled
          12. contained database authentication
          13. cost threshold for parallelism
          14. cross db ownership chaining
          15. cursor threshold
          16. Database Mail XPs
          17. default full-text language
          18. default language
          19. default trace enabled
          20. disallow results from triggers
          21. EKM provider enabled
          22. filestream_access_level
          23. fill factor
          24. index create memory
          25. in-doubt xact resolution
          26. lightweight pooling
          27. locks
          28. max degree of parallelism
          29. max server memory and min server memory
          30. max text repl size
          31. max worker threads
          32. media retention
          33. min memory per query
          34. nested triggers
          35. network packet size
          36. Ole Automation Procedures
          37. optimize for ad hoc workloads
          38. PH_timeout
          39. priority boost
          40. query governor cost limit
          41. query wait
          42. recovery interval
          43. remote access
          44. remote admin connections
          45. remote login timeout
          46. remote proc trans
          47. remote query timeout
          48. scan for startup procs
          49. show advanced options
          50. user connections
          51. user options
          52. XP-Related Configuration Options
        8. Summary
    23. Index

    Product information

    • Title: Microsoft® SQL Server 2012 Unleashed
    • Author(s):
    • Release date: December 2013
    • Publisher(s): Sams
    • ISBN: 9780133408539