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

Book Description

Harness the powerful new SQL Server 2012

Microsoft SQL Server 2012 is the most significant update to this product since 2005, and it may change how database administrators and developers perform many aspects of their jobs. If you're a database administrator or developer, Microsoft SQL Server 2012 Bible teaches you everything you need to take full advantage of this major release. This detailed guide not only covers all the new features of SQL Server 2012, it also shows you step by step how to develop top-notch SQL Server databases and new data connections and keep your databases performing at peak.

The book is crammed with specific examples, sample code, and a host of tips, workarounds, and best practices. In addition, downloadable code is available from the book's companion web site, which you can use to jumpstart your own projects.

  • Serves as an authoritative guide to Microsoft's SQL Server 2012 for database administrators and developers

  • Covers all the software's new features and capabilities, including SQL Azure for cloud computing, enhancements to client connectivity, and new functionality that ensures high-availability of mission-critical applications

  • Explains major new changes to the SQL Server Business Intelligence tools, such as Integration, Reporting, and Analysis Services

  • Demonstrates tasks both graphically and in SQL code to enhance your learning

  • Provides source code from the companion web site, which you can use as a basis for your own projects

  • Explores tips, smart workarounds, and best practices to help you on the job

Get thoroughly up to speed on SQL Server 2012 with Microsoft SQL Server 2012 Bible.

Table of Contents

  1. Cover
  2. Part I: Laying the Foundations
    1. Chapter 1: The World of SQL Server
      1. SQL Server History
      2. SQL Server in the Database Market
      3. SQL Server Components
      4. Editions of SQL Server 2012
      5. Notable SQL Server 2012 Enhancements
      6. Summary
    2. Chapter 2: Data Architecture
      1. Information Architecture Principle
      2. Database Objectives
      3. Smart Database Design
      4. Summary
    3. Chapter 3: Installing SQL Server
      1. Preparing the Server
      2. Selecting the Edition
      3. The Installation Process
      4. Summary
    4. Chapter 4: Client Connectivity
      1. Enabling Server Connectivity
      2. SQL Server Native Client Features
      3. Summary
    5. Chapter 5: SQL Server Management and Development Tools
      1. Organizing the Interface
      2. Registered Servers
      3. Object Explorer
      4. Using the Query Editor
      5. Using the Solution Explorer
      6. Getting a Jumpstart on Code with Templates and Code Snippets
      7. Summary
  3. Part II: Building Databases and Working with Data
    1. Chapter 6: Introducing Basic Query Flow
      1. Understanding Query Flow
      2. FROM Clause Data Sources
      3. WHERE Conditions
      4. (0 row(s) affected)Columns, Stars, Aliases, and Expressions
      5. Ordering the Result Set
      6. Select Distinct
      7. TOP ()
      8. Summary
    2. Chapter 7: Relational Database Design and Creating the Physical Database Schema
      1. Database Basics
      2. Data Design Patterns
      3. Normal Forms
      4. Strategy Considerations
      5. Summary
    3. Chapter 8: Data Types, Expressions, and Scalar Functions
      1. Data Types
      2. Building Expressions
      3. Scalar Functions
      4. String Functions
      5. Soundex Functions
      6. Data-Type Conversion Functions
      7. Server Environment Information
      8. Summary
    4. Chapter 9: Merging Data with Joins, Subqueries, and CTEs
      1. Using Joins
      2. Set Difference Queries
      3. Using Unions
      4. Subqueries
      5. Summary
    5. Chapter 10: Aggregating, Windowing, and Ranking Data
      1. Aggregating Data
      2. Grouping Within a Result Set
      3. Windowing and Ranking
      4. Ranking Functions
      5. Summary
    6. Chapter 11: Projecting Data Through Views
      1. Why Use Views?
      2. The Basic View
      3. A Broader Point of View
      4. Locking Down the View
      5. Using SQL Synonyms
      6. Summary
    7. Chapter 12: Modifying Data In SQL Server
      1. Inserting Data
      2. Updating Data
      3. Deleting Data
      4. Merging Data
      5. Returning Modified Data
      6. Summary
  4. Part III: Advanced T-SQL Data Types and Querying Techniques
    1. Chapter 13: Working with Hierarchies
      1. HierarchyID
      2. HierarchyID Methods
      3. Indexing Strategies
      4. Hierarchical Data Alternatives
      5. Summary
    2. Chapter 14: Using XML Data
      1. The XML Data Type
      2. XML Data Type Methods
      3. FOR XML
      4. XQuery and FLWOR Operations
      5. Summary
    3. Chapter 15: Executing Distributed Queries
      1. Distributed Query Overview
      2. Developing Distributed Queries
      3. Performance Consideration
      4. Summary
  5. Part IV: Programming with T-SQL
    1. Chapter 16: Programming with T-SQL
      1. Transact-SQL Fundamentals
      2. Working with Variables
      3. Procedural Flow
      4. Examining SQL Server with Code
      5. Temporary Tables and Table Variables
      6. What's New in T-SQL for 2012
      7. Error Handling
      8. Bulk Operations
      9. Summary
    2. Chapter 17: Developing Stored Procedures
      1. Managing Stored Procedures
      2. Passing Data to Stored Procedures
      3. Returning Data from Stored Procedures
      4. Summary
    3. Chapter 18: Building User-Defined Functions
      1. Scalar Functions
      2. Inline Table-Valued Functions
      3. Multistatement Table-Valued Functions
      4. Best Practices with User-Defined Functions
      5. Summary
  6. Part V: Enterprise Data Management
    1. Chapter 19: Configuring SQL Server
      1. Setting the Options
      2. Configuration Options
      3. Summary
    2. Chapter 20: Policy Based Management
      1. Defining Policies
      2. Evaluating Policies
      3. Summary
    3. Chapter 21: Backup and Recovery Planning
      1. Recovery Concepts
      2. Recovery Models
      3. Backing Up the Database
      4. Working with the Transaction Log
      5. Recovery Operations
      6. System Databases Recovery
      7. Performing a Complete Recovery
      8. Summary
    4. Chapter 22: Maintaining the Database
      1. DBCC Commands
      2. Managing Database Maintenance
      3. Summary
    5. Chapter 23: Transferring Databases
      1. Copy Database Wizard
      2. Working with SQL Script
      3. Detaching and Attaching
      4. Import and Export Wizard
      5. Data-Tier Application (DAC)
      6. Summary
    6. Chapter 24: Database Snapshots
      1. How Do Database Snapshots Work?
      2. Using Database Snapshots
      3. Summary
    7. Chapter 25: Asynchronous Messaging with Service Broker
      1. Configuring a Message Queue
      2. Working with Dialogs
      3. What's New in Service Broker for SQL Server 2012?
      4. Monitoring and Troubleshooting Service Broker
      5. Summary
    8. Chapter 26: Log Shipping
      1. Availability Testing
      2. Warm Standby Availability
      3. Defining Log Shipping
      4. Checking Log Shipping Configuration
      5. Monitoring Log Shipping
      6. Modifying or Removing Log Shipping
      7. Switching Roles
      8. Summary
    9. Chapter 27: Database Mirroring
      1. Database Mirroring Overview
      2. Defining and Configuring Database Mirroring
      3. Checking Database Mirroring Configuration
      4. Monitoring Database Mirroring
      5. Pausing or Removing Database Mirroring
      6. Role Switching
      7. High Availability/AlwaysOn
      8. Summary
    10. Chapter 28: Replicating Data
      1. Moving Data Between Servers
      2. Replication Concepts
      3. Configuring Replication
      4. Summary
    11. Chapter 29: Clustering
      1. What Does Clustering Do?
      2. Configuring Clustering
      3. Summary
    12. Chapter 30: Configuring and Managing SQL Server with PowerShell
      1. Why Use PowerShell?
      2. Basic PowerShell
      3. SQL Server PowerShell Extensions
      4. Communicating with SQL Server via SMO
      5. Scripting SQL Server Tasks
      6. Summary
    13. Chapter 31: Managing Data in Windows Azure SQL Database
      1. Overview of Azure SQL Database
      2. Managing Windows Azure SQL Database
      3. High Availability and Scalability
      4. Migrating Data to SQL Database
      5. Summary
  7. Part VI: Securing Your SQL Server
    1. Chapter 32: Authentication Types in SQL Server
      1. Windows Authentication
      2. SQL Authentication
      3. Differences Between SQL and Windows Authentication
      4. Kerberos and Windows Authentication Delegation
      5. Summary
    2. Chapter 33: Authorizing Securables
      1. Permission Chains
      2. Object Ownership
      3. Securables Permissions
      4. Object Security
      5. A Sample Security Model
      6. Views and Security
      7. Summary
    3. Chapter 34: Data Encryption
      1. Introducing Data Encryption
      2. Summary
    4. Chapter 35: Row-Level Security
      1. The Security Table
      2. Assigning Permissions
      3. Checking Permissions
      4. Summary
  8. Part VII: Monitoring and Auditing
    1. Chapter 36: Creating Triggers
      1. Trigger Basics
      2. Working with the Transaction
      3. Multiple-Trigger Interaction
      4. Transaction-Aggregation Handling
      5. DDL Triggers
      6. Managing DDL Triggers
      7. Developing DDL Triggers
      8. Summary
    2. Chapter 37: Performance Monitor and PAL
      1. Using PerfMon
      2. Summary
    3. Chapter 38: Using Profiler and SQL Trace
      1. Features of SQL Server Profiler
      2. Running Profiler
      3. Using SQL Trace
      4. Summary
    4. Chapter 39: Wait States
      1. The SQL Server OS
      2. Examining Wait Statistics
      3. Common Red-Flag Wait Types
      4. Other Ways to Gather Wait data
      5. Summary
    5. Chapter 40: Extended Events
      1. The Extended Events Object Model
      2. The system_health Session
      3. The Extended Events Profiler
      4. Summary
    6. Chapter 41: Data Change Tracking and Capture
      1. Configuring Change Tracking
      2. Querying Change Tracking
      3. Removing Change Tracking
      4. Change Data Capture
      5. New in SQL Server 2012
      6. Enabling CDC
      7. Working with Change Data Capture
      8. Removing Change Data Capture
      9. Summary
    7. Chapter 42: SQL Audit
      1. SQL Audit Technology Overview
      2. Creating an Audit
      3. Server Audit Specifications
      4. Database Audit Specifications
      5. Viewing the Audit Trail
      6. Summary
    8. Chapter 43: Management Data Warehouse
      1. Using the Management Data Warehouse
      2. Configuring MDW
      3. Setting Up Data Collection
      4. Viewing MDW Reports
      5. Creating Custom Data Collector Sets
      6. Summary
  9. Part VIII: Performance Tuning and Optimization
    1. Chapter 44: Interpreting Query Execution Plans
      1. Viewing Query Execution Plans
      2. Understanding Execution Plan Operators
      3. Summary
    2. Chapter 45: Indexing Strategies
      1. Zen and the Art of Indexing
      2. Indexing Basics
      3. The Path of the Query
      4. A Comprehensive Indexing Strategy
      5. Specialty Indexes
      6. Summary
    3. Chapter 46: Maximizing Query Plan Reuse
      1. Query Compiling
      2. Query Recompiles
      3. Summary
    4. Chapter 47: Managing Transactions, Locking, and Blocking
      1. The ACID Properties
      2. Programming Transactions
      3. Default Locking and Blocking Behavior
      4. Monitoring Locking and Blocking
      5. Dealing with Deadlocks
      6. Understanding SQL Server Locking
      7. Transaction Isolation Levels
      8. Application Locks
      9. Application Locking Design
      10. Transaction-Log Architecture
      11. Transaction Performance Strategies
      12. Summary
    5. Chapter 48: Data Compression
      1. Understanding Data Compression
      2. Applying Data Compression
      3. Summary
    6. Chapter 49: Partitioning
      1. Partitioning Strategies
      2. Partitioned Views
      3. Partitioned Tables and Indexes
      4. Summary
    7. Chapter 50: Resource Governor
      1. Exploring the Fundamentals of the Resource Governor
      2. Performance Monitoring of Resource Governor
      3. Views and Limitations
      4. Summary
  10. Part IX: Business Intelligence
    1. Chapter 51: Business Intelligence Database Design
      1. Data Warehousing
      2. Designing a Data Warehouse Using a Star Schema
      3. Designing Your Data Warehouse Using a Snowflake Schema
      4. Ensuring Consistency within a Data Warehouse
      5. Loading Data
      6. Summary
    2. Chapter 52: Building, Deploying, and Managing ETL Workflows in Integration Services
      1. Exploring the SSIS Environment in Brief
      2. Exploring the SSIS Environment in More Detail
      3. Deploying and Executing Projects and Packages
      4. Summary
    3. Chapter 53: Building Multidimensional Cubes in Analysis Services with MDX
      1. Analysis Services Quick Start
      2. Analysis Services Architecture
      3. Building a Database
      4. Dimensions
      5. Cubes
      6. Data Storage
      7. Cube Processing
      8. Summary
    4. Chapter 54: Configuring and Administering Analysis Services
      1. Installing Analysis Services
      2. Configuring Basic Analysis Services Settings
      3. Advanced SSAS Deployments
      4. Reviewing Query Performance with SQL Profiler
      5. Summary
    5. Chapter 55: Authoring Reports in Reporting Services
      1. Report Authoring Environments
      2. The Basic Elements of a Report
      3. Building a Report with the Report Wizard
      4. Authoring a Report from Scratch
      5. Exploring the Report Designer
      6. Using Reporting Services Features to Visualize Your Data
      7. Designing the Report Layout
      8. Building Reports with Report Builder
      9. Summary
    6. Chapter 56: Configuring and Administering Reporting Services
      1. Installing Reporting Services
      2. Deploying Reporting Services Reports
      3. Managing Security with Reporting Services
      4. Disaster Recovery
      5. Summary
    7. Chapter 57: Data Mining with Analysis Services
      1. The Data Mining Process
      2. Modeling with Analysis Services
      3. Algorithms
      4. Cube Integration
      5. Summary
    8. Chapter 58: Creating and Deploying BI Semantic Models
      1. What Is a BI Semantic Model?
      2. The Development Environment
      3. Creating BI Semantic Models Using PowerPivot
      4. Extending a BI Semantic Model with PowerPivot
      5. Deploying BI Semantic Models to SharePoint
      6. Managing Automatic Data Refresh of PowerPivot Workbooks in SharePoint 2010
      7. Creating BI Semantic Models Using SQL Server Data Tools
      8. Extending a BI Semantic Model with SQL Server Data Tools
      9. Deploying BI Semantic Models to an Analysis Services Instance
      10. Summary
    9. Chapter 59: Creating and Deploying Power View Reports
      1. Power View Requirements
      2. Creating and Deploying Reports with Power View
      3. Deploying Power View Reports
      4. Summary
  11. Introduction