Microsoft® SQL Server 2012 Unleashed - …

1 Microsoft SQL Server 2012 Unleashed 800 East 96th Street, Indianapolis, Indiana 46240 USA Ray Rankins Paul Bertucci Chris Gallelli Alex T. Silverstein Hilary Cotter Microsoft SQL Server 2012 Unleashed Copyright 2014 by Pearson Education, Inc. All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, record-ing, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein.

2 Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein. ISBN-13: 978-0-672-33692-8 ISBN-10: 0-672-33692-8 Library of Congress Control Number: 2013944514 Printed in the United States of America Second Printing: April 2014 Trademarks All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized.

3 Sams Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark. Warning and Disclaimer Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an as is basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book or from the use of the CD or programs accompanying it.

4 Special SalesFor information about buying this title in bulk quantities, or for special sales oppor-tunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at or (800) government sales inquiries, please contact For questions about sales outside the , please contact Editor-in-Chief Greg Wiegand Executive Editor Neil Rowe Development Editor Mark Renfrow Managing Editor Kristy Hart Project Editor Andy Beaster Copy Editor Keith Cline Indexer Rebecca Salerno Proofreader Chrissy White Technical Editor J.

5 Boyd Nolan Editorial Assistant Cindy Teeters Cover Compositor Mark Shirar Compositor Nonie Ratcliff Contents at a Glance Introduction .. 1 Part I Welcome to Microsoft SQL Server 1 SQL Server 2012 Overview .. 9 2 What s New in SQL Server 2012 .. 37 3 Examples of SQL Server Implementations .. 49 Part II SQL Server Tools and Utilities 4 SQL Server Management Studio .. 63 5 SQL Server Command-Line Utilities .. 105 6 SQL Server Profiler .. 125 Part III SQL Server Administration 7 SQL Server System and Database Administration.

6 169 8 Installing SQL Server 2012 .. 189 9 Upgrading to SQL Server 2012 .. 233 10 Client Installation and Configuration .. 269 11 Database Backup and Restore .. 297 12 Database Mail .. 351 13 SQL Server Agent .. 373 14 Administering SQL Server 2012 with PowerShell .. 405 15 SQL Server Policy-Based Management .. 447 Part IV SQL Server Security Administration 16 Security and Compliance .. 469 17 Security and User Administration .. 489 18 Data Encryption .. 537 Part V Database Administration 19 Creating and Managing Databases.

7 563 20 Creating and Managing Tables .. 599 21 Creating and Managing Indexes .. 665 22 Implementing Data Integrity .. 685 23 Creating and Managing Views .. 711 Microsoft SQL Server 2012 Unleashediv 24 Creating and Managing Stored Procedures .. 743 25 Creating and Managing User-Defined Functions .. 795 26 Creating and Managing Triggers .. 829 27 Transaction Management and the Transaction Log .. 865 28 Database Snapshots .. 911 29 Database Maintenance .. 935 Part VI SQL Server Performance and Optimization 30 Data Structures, Indexes, and Performance.

8 959 31 Understanding Query Optimization .. 1079 32 Query analysis .. 1167 33 Locking and Performance .. 1207 34 Database Design and Performance .. 1273 35 Monitoring SQL Server Performance .. 1295 36 SQL Server Database Engine Tuning 1387 37 Managing Workloads with the Resource Governor .. 1407 38 A Performance and Tuning Methodology .. 1433 Part VII SQL Server High Availability 39 SQL Server High Availability Fundamentals .. 1467 40 Data Replication .. 1493 41 Database Mirroring .. 1593 42 SQL Server Failover Clustering.

9 1629 43 SQL Server AlwaysOn and Availability Groups .. 1661 Part VIII SQL Server Application Development 44 What s New for Transact-SQL in SQL Server 2012 .. 1687 45 Transact-SQL Programming Guidelines, Tips, and Tricks .. 1761 46 Advanced Stored Procedure Programming and Optimization .. 1871 47 SQL Server and the .NET Framework .. 1925 48 SQLCLR: Developing SQL Server Objects in .NET .. 1965 49 Using XML in SQL Server 2012 .. 2009 50 SQL Server Service Broker .. 2079 51 SQL Server Full-Text 2119 52 Working with SQL Azure.

10 2171v Part IX SQL Server Business Intelligence Features 53 SQL Server 2012 analysis 2213 54 SQL Server Integration services .. 2283 55 SQL Server 2012 Reporting services .. 2353 56 Master Data services and Data Quality services .. 2419 57 Parallel Data Warehouse .. 2463 Part X Bonus Chapters 58 Managing Linked Servers .. 2481 59 SQL Server Disaster Recovery Planning .. 2507 60 SQL Server Configuration Options .. 2533 Index .. 2579 Table of Contents Introduction.