image

CONTENTS

image

I’d like to dedicate this book to my Lord and Savior Jesus Christ, and my parents who always stressed the importance of the “All-important question.” This time the answer is yes!

—Adam Jorgensen

To my wonderful team at Pragmatic Works, who inspire me to always take myself to the next level.

—Brian Knight

To my parents, for being the pillars of the person that I am now, and to the three greatest loves of my life: my queen Madeline and my two princesses, Sofia and Stephanie, for filling my life with love, tenderness, and joy.

—Jose Chinchilla

To my beautiful wife Jessica. I love you and thank you for all your patience, love, understanding, and support.

—Jorge Segarra

To my dad Chris A. R. Swanepoel. I owe everything I am to you. You are my hero.

—Gareth Swanepoel

ABOUT THE AUTHORS

image ADAM JORGENSEN () is the president of Pragmatic Works Consulting, a Director for the Professional Association of SQL Server (PASS), SQL Server MVP, and a well-known speaker, author, and executive mentor. His focus is on helping companies realize their full potential by using their data in ways they may not have previously imagined. Adam is involved in the community as the Director at large for the Professional Association of SQL Server (PASS) and delivers more than 75 community sessions per year. He is based in Jacksonville, FL, and has written and contributed to five previous books on SQL Server, analytics, and SharePoint.

image STEVEN WORT has been working with SQL Server since 1993, starting with version 4.2 running on OS2. He has more than 30 years of experience developing applications in the IT industry, working in a wide range of industries. Steven joined Microsoft in 2000 as an escalation engineer on the Systems Integration Engineering (SIE) team, where he co-authored multiple workshops on debugging Windows and .NET. In 2004, he moved to the SQL Server team to work on scalability for SQL Server 2005. After a short spell in the Windows group, spent working on scaling large database systems, he is now back in SQL Server where he is a Lead Engineer on the SQL Server Appliance Engineering Team, responsible for the Microsoft Database Consolidation Appliance. Steven has co-authored several books on SQL Server Administration, troubleshooting, and performance tuning.

image ROSS LOFORTE is a technology architect at the Microsoft Technology Center Chicago focused on Microsoft SQL Server solutions. Ross has more than 20 years of business development, project management, and SQL architecture solutions. For the past 11 years, Ross has been working with the Microsoft Technology Centers and has led architecture design and proof-of-concept engagements for Microsoft’s largest and most strategic customers to design enterprise, mission-critical SQL Server solutions. Ross is a SQL Server instructor at DePaul University in Chicago, and regularly presents at TechEd, SQL PASS, Gartner, TDWI, and Microsoft internal conferences. Ross is a published author and has been active with the Professional Association for SQL Server, the Chicago SQL Server Users Group, and the SQL Server community for many years.

BRIAN KNIGHT, SQL Server MVP, MCITP, MCSE, MCDBA, is the owner and founder of Pragmatic Works. He is also the co-founder of , , and . He runs the local SQL Server users group in Jacksonville (JSSUG). Brian is a contributing columnist at several technical magazines and does regular webcasts at Jumpstart TV. He is the author of a dozen SQL Server books. Brian has spoken at conferences such as PASS, SQL Connections and TechEd, SQL Saturdays, Code Camps, and many pyramid scheme motivational sessions. You can find his blog at . Brian lives in Jacksonville, FL, where he has been baking at 350 degrees for the past 35 years.

image ROBERT C. CAIN () is a Microsoft MVP in SQL Server, MCTS Certified in BI, and works as a Senior Consultant for Pragmatic Works. He is also a technical contributor to Plurasight Training and co-author of Volumes 1 and 2 of SQL Server MVP Deep Dives (Manning Publications, 2009). A popular speaker, Robert has presented at events such as TechEd, SQL Rally, and numerous SQL Saturdays. Robert has over 25 years of experience in the IT industry, working in a variety of fields ranging from manufacturing to telecommunications to nuclear power.

image DENNY CHERRY is an independent consultant with more than a decade of experience working with platforms such as Microsoft SQL Server, Hyper-V, vSphere, and Enterprise Storage solutions. Denny’s areas of technical expertise include system architecture, performance tuning, security, replication, and troubleshooting. Denny currently holds several of the Microsoft certifications related to SQL Server for versions 2000 through 2008 including the Microsoft Certified Master and has also been a Microsoft MVP for several years. Denny has written several books and dozens of technical articles on SQL Server management and on how SQL Server integrates with various other technologies.

image JOSE CHINCHILLA () is a certified Microsoft SQL Server Database Administrator and Business Intelligence Professional with more than 12 years of experience in the Information Technology field. Jose has a proven record of success architecting and developing Data Warehouse and Business Intelligence solutions and incorporating Master Data Management and Data Quality frameworks for the retail, manufacturing, financial, health, not-for-profit, and local government sectors. Jose is president and CEO of Agile Bay, Inc. (), a full-service consulting firm based in Tampa, FL, and is a frequent speaker at SQL Saturday and Code Camp events around the country. He also serves as president of the Tampa Bay Business Intelligence user group and PASS Chapter. Jose is an avid networker and blogger active in social media and is known in Twitter under the @SQLJoe handle.

AUDREY HAMMONDS is a database developer, blogger, presenter, and thanks to this publication, writer. Fifteen years ago, she volunteered to join a newly formed database team so that she could stop writing COBOL. (And she never wrote COBOL again.) Audrey is convinced that the world would be a better place if people would stop, relax, enjoy the view, and normalize their data. She’s the wife of Jeremy, mom of Chase and Gavin, and adoptive mother to her cats Bela, Elmindreda, and Aviendha. She blogs at and is based in Atlanta, Georgia.

SCOTT KLEIN has taken his love and passion for SQL Server to Microsoft, recently joining the company as a SQL Azure technical evangelist. Prior to joining Microsoft, Scott was co-founder of Blue Syntax, an Azure consulting and services company. Scott’s background is in SQL Server, having spent the last 20+ years working with SQL Server. He started with SQL Server 4.2, but after hearing about the Azure platform, he focused his efforts on SQL Azure and the Azure platform. He has presented at nearly two dozen Azure Boot Camps around the United States and Canada. Not forgetting his South Florida roots, Scott ran the South Florida SQL Saturday events as well as the South Florida SQL Server user group. Scott is the author of several other Wrox books, including Professional SQL Server 2005 XML (John Wiley & Sons, 2006), Professional LINQ (John Wiley & Sons, 2008), and the recently released Pro SQL Azure (Apress, 2010). He has also contributed chapters to a few other books and has written several articles for MSDN Magazine. Scott is the husband to a wonderful wife and the father to four wonderful children who won’t stop growing up.

JORGE SEGARRA is a DBA-turned BI consultant for Pragmatic Works in Jacksonville, Florida. He’s a SQL Server MVP, blogger (blogs at ), PASS volunteer, and Regional Mentor, and is the founder of the online community blog project SQL University ().

GARETH SWANEPOEL () is a systems administrator turned SQL Server DBA. He has been working in the IT industry doing support and administration for almost 20 years. He enjoys solving the complex problems that his clients encounter when deploying SQL Server in a data warehouse environment. He is originally from South Africa and currently lives in the quiet outskirts of Jacksonville, Florida, with his beautiful wife, 5-year-old-son, and soon-to-be baby daughter.

ABOUT THE TECHNICAL EDITORS

JASON STRATE, FROM DIGINEER INC., is a database consultant with more than 15 years of experience. His experience includes design and implementation of both OLTP and OLAP solutions as well as assessment and implementation of SQL Server environments for best practices, performance, and high availability solutions. He is a recipient of the Microsoft Most Valuable Professional (MVP) award for SQL Server since July 2009. Jason is a SQL Server MCITP and participated in the development of Certification exams for SQL Server 2008 and 2012.

image DENNY CHERRY is an independent consultant with more than a decade of experience working with platforms such as Microsoft SQL Server, Hyper-V, vSphere, and Enterprise Storage solutions. Denny’s areas of technical expertise include system architecture, performance tuning, security, replication, and troubleshooting. Denny currently holds several of the Microsoft certifications related to SQL Server for versions 2000 through 2008 including the Microsoft Certified Master as well as being a Microsoft MVP for several years. Denny has written several books and dozens of technical articles on SQL Server management and how SQL Server integrates with various other technologies.

CREDITS

EXECUTIVE EDITOR

Robert Elliott

PROJECT EDITOR

Victoria Swider

TECHNICAL EDITORS

Jason Strate

Denny Cherry

PRODUCTION EDITOR

Kathleen Wisor

COPY EDITOR

San Dee Phillips

EDITORIAL MANAGER

Mary Beth Wakefield

FREELANCER EDITORIAL MANAGER

Rosemarie Graham

ASSOCIATE DIRECTOR OF MARKETING

David Mayhew

MARKETING MANAGER

Ashley Zurcher

BUSINESS MANAGER

Amy Knies

PRODUCTION MANAGER

Tim Tate

VICE PRESIDENT AND EXECUTIVE GROUP PUBLISHER

Richard Swadley

VICE PRESIDENT AND EXECUTIVE PUBLISHER

Neil Edde

ASSOCIATE PUBLISHER

Jim Minatel

PROJECT COORDINATOR, COVER

Katie Crocker

PROOFREADER

Jen Larsen, Word One New York

INDEXER

Robert Swanson

COVER DESIGNER

Ryan Sneed

COVER IMAGE

© Christian Delbert

ACKNOWLEDGMENTS

I’D LIKE TO THANK my family and fiancée Cristina for their support while writing this book. I also want to applaud this incredible author and technical editing team! You’ll see why this team is so exceptional in the introduction to this book. Special thanks go to Brad Schacht, whose help was critical in completing several of these chapters. My two pups, Ladybird and Mac, who kept my feet warm while writing on my patio in the winter (yes, I know it’s FL winter) and a special thanks to Brian Knight and Bob Elliot for getting me started in this crazy writing game in the first place. Thank you to the Wrox team for keeping us on track and helping to make this title a reality. Your support of me and the team made all the difference!

—Adam Jorgensen

I’D LIKE TO THANK my wife Anna and my daughter Jennifer for the support and dedication while writing this book. I’d like to thank the Microsoft Technology Center’s staff, including the director Adam Hecktman, for their support and for making the Microsoft Technology Center Chicago a great facility to learn and experience. In addition and foremost I’d like to thank the SQL Server development team for delivering another excellent release full of features in SQL Server 2012. Last but not least, thanks to the technical reviewer Jason Strate and several folks at Wiley, including Victoria Swider and Robert Elliott, for getting these words in print to enable our readers to share my passion for SQL Server.

—Ross LoForte

THANKS TO EVERYONE who made this book possible. As always, I owe a huge debt to my wife Jenn, for putting up with my late nights and my children, Colton, Liam, Camille, and my newest son John for being so patient with their tired dad who has always overextended. Thanks also to the makers of Guinness and other hard liquors for sustaining my ability to keep on writing technical books. Thanks for all the user group leaders out there who work so hard to help others become proficient in technology. You make a huge difference! Finally, thanks to my vocal coach Mike Davis for getting me ready for my American Idol tryout this upcoming season. This time I won’t disappoint.

—Brian Knight

FIRST AND FOREMOST I NEED TO THANK my lovely wife Ammie. Without her patience and support none of this would be possible. I also need to thank my daughters, Raven and Anna, for putting up with Daddy’s late nights and crazy schedule.

I would be remiss if I didn’t acknowledge my coworkers at both Pragmatic Works and Pluralsight. Working with so many technical experts has both challenged and inspired me to continually strive for technical excellence.

Finally, I need to thank you, all of you in the SQL community. Thanks for reading my blog, my books, for listening to me when I present in person and on video. It’s all of you wonderful folks in the community that infuse me with the passion to learn, teach, and share.

—Robert C. Cain

A LOT OF TIME AND EFFORT was dedicated to bring to you all the information contained in this book. My efforts would not have been possible without the support of my wife and my two daughters and their unconditional love and understanding of my professional commitments. I would also like to extend my eternal gratitude to Adam Jorgensen () for giving me the opportunity to contribute to this book and to my good friend Nicholas Cain () for his expert contribution on the SQL Server Clustering Chapter.

—Jose Chinchilla

THANKS TO ADAM JORGENSEN for inviting me to participate on this book, Jason Strate for editing the crap out of my work (literally), and the good folks at Wiley Publishing for their patience and perseverance in keeping us all on the rails. A big thank you to my family: Jeremy, Chase, and Gavin, for fending for yourselves when I was buried in work. To Julie Smith, my fellow Datachix, thanks for broad meetings, pep talks, and wine. To the SQL Community, thanks for being so flippin’ awesome, especially my Atlanta friends. . .you guys are the best. And finally, thanks to my grandfather, Bruce Bryant, who taught me that learning is a lifetime endeavor.

—Audrey Hammonds

FIRST I’D LIKE TO THANK my wife Jessica whose love, understanding, and support allow me to actually get stuff like this done. I’d like to especially thank Adam Jorgensen and Brian Knight for giving me the opportunity to propel my career in a direction I never thought possible and giving me the opportunity to write on titles like this. Thank you guys for your support and mentorship. Big thanks to Victoria Swider and Bob Elliot, professional cat herders/editors for this title. Sorry for all the stress guys! Finally, a HUGE thanks to the SQL Server Community as a whole. This Community truly is like a family, and without everyone supporting each other and freely sharing knowledge, I definitely would not be where I am today.

—Jorge Segarra

I WOULD LIKE TO THANK the Lord Jesus Christ who has provided me with the talents to embark on a project like this. I would like to thank my wonderful wife Jen who puts up with my stubbornness every day. Thank you, too, to my little guy who has had to go without “playing with daddy” at certain times due to deadlines. I would like to thank my family back home in South Africa for allowing me to seek my dreams in the Land of Opportunity. Mom, Dad, Bernie, Kirsty, and Tessie, this is for you.

—Gareth Swanepoel

INTRODUCTION

SQL SERVER 2012 REPRESENTS A SIZABLE jump forward in scalability, performance, and usability for the DBA, developer, and business intelligence (BI) developer. It is no longer unheard of to have 40-terabyte databases running on a SQL Server. SQL Server administration used to just be the job of a database administrator (DBA), but as SQL Server proliferates throughout smaller companies, many developers have begun to act as administrators and BI developers as well. In addition, some of the new features in SQL Server are more developer-centric, and poor configuration of these features can result in poor performance. SQL Server now enables all roles through significantly improved data tools experiences, better security integration, and drastic improvements in data integration, administration, availability, and usability. Professional Microsoft SQL Server2012 Administration is a comprehensive, tutorial-based book to get you over the learning curve of how to configure and administer SQL Server 2012.

WHO THIS BOOK IS FOR

Whether you’re an administrator or developer using SQL Server, you can’t avoid wearing a DBA hat at some point. Developers often have SQL Server on their own workstations and must provide guidance to the administrator about how they’d like the production configured. Oftentimes, they’re responsible for creating the database tables and indexes. Administrators or DBAs support the production servers and often inherit the database from the developer.

This book is intended for developers, DBAs, and casual users who hope to administer or may already be administering a SQL Server 2012 system and its business intelligence features, such as Integration Services. This book is a professional book, meaning the authors assume that you know the basics about how to query a SQL Server and have some rudimentary concepts of SQL Server. For example, this book does not show you how to create a database or walk you through the installation of SQL Server using the wizard. Instead, the author of the installation chapter provides insight into how to use some of the more advanced concepts of the installation. Although this book does not cover how to query a SQL Server database, it does cover how to tune the queries you’ve already written.

HOW THIS BOOK IS STRUCTURED

This book follows the same basic path of previous editions, with one major change. The author team has been selected specifically to focus on their areas of expertise. The authors are the same people seen at major conferences and delivering top-tier services for topics such as performance tuning, business intelligence, database design, high availability, PowerShell, and even SQL Azure! This approach has led to unprecedented focus on quality and content with even better access to folks at Microsoft to drive the content in this new release of SQL Server. Hundreds of Connect items were filed and resolved as a direct result of the work of this author team pushing for higher quality for you. Connect is the primary method for industry professionals and SQL Server MVPs to provide bug reports and vote on feature requests from Microsoft. It’s a great outlet for improving the product.

This edition of the book covers all the same great information covered in the previous edition, but with loads of new content added for SQL Server 2012, which includes numerous new features to improve the DBA’s life. In short, the new version of SQL Server focuses on improving your efficiency, the scale of your server, and the performance of your environment, so you can do more in much less time, and with fewer resources and people. The following is a brief description of each chapter.

Chapter 1: SQL Server 2012 Architecture — The book starts off with a review of the new architecture changes and focuses on the overall components that make up SQL Server 2012.

Chapter 2: Installing SQL Server 2012 Best Practices — This chapter reviews the different ways to install SQL Server 2012 and covers best practices for the process.

Chapter 3: Upgrading SQL Server 2012 Best Practices — This chapter covers upgrading to SQL Server 2012 and best practices to keep in mind while upgrading. Choosing the best upgrade method, requirements, and benefits of upgrading are also covered.

Chapter 4: Managing and Troubleshooting the Database Engine — This chapter focuses on the database engine and working through challenges as they arise. It also covers management and tools appropriate for the task.

Chapter 5: Automating SQL Server — This chapter focuses on automation throughout the SQL Server 2012 world including jobs, PowerShell, and other ways to automate.

Chapter 6: Service Broker in SQL Server 2012 — Service Broker is a great tool to handle messaging inside the database. This chapter covers setup, operations, and management of Service Broker.

Chapter 7: SQL Server CLR Integration — SQL Server and .NET work together inside the Common Language Runtime. This chapter focuses on integrating .NET and the CLR with SQL Server, including assemblies and other options.

Chapter 8: Securing the Database Instance — Security is critical in the database engine. This chapter helps you outline and implement your security plan.

Chapter 9: Change Management — Managing change is paramount to operational stability. This chapter focuses on features in SQL Server that support change management.

Chapter 10: Configuring the Server for Optimal Performance — Configuring and setting up your server properly is important for maximizing application and database performance. This chapter discusses storage, server options, and other settings critical to system performance.

Chapter 11: Optimizing SQL Server 2012 — This chapter covers topics that help the reader review and analyze performance. It also focuses on settings and configuration items that improve SQL Server performance.

Chapter 12: Monitoring Your SQL Server — SQL Server is critically important to make sure you keep performance where it needs to be. This chapter covers the important aspects and tools used to monitor SQL Server 2012.

Chapter 13: Performance Tuning T-SQL — Writing efficient and effective T-SQL is important to have good application performance and scalability. This chapter explains how to optimize your T-SQL to make it more efficient. It focuses on how SQL Server’s engine and internals read and execute your queries. You then learn how to take advantage of areas where this process can be tweaked and best practices can be leveraged.

Chapter 14: Indexing Your Database — Indexing is critical to successful database performance. This chapter discusses considerations and strategies for effective indexing for your database.

Chapter 15: Replication — Replication is a key feature in SQL Server for keeping tables and databases in sync and supporting applications. This chapter will cover the types of replication, how to set them up, and the pros and cons of each.

Chapter 16: Clustering SQL Server 2012 — Clustering has been improved again in SQL 2012 and this chapter takes the reader through the setup, configuration, and testing of your clustered configuration.

Chapter 17: Backup and Recovery — Backup and recovery is critical to the success of a continuity plan and operational achievement. This chapter outlines the options in SQL Server for backups and recoveries, and provides recommendations to make the most of these features.

Chapter 18: SQL Server 2012 Log Shipping — This chapter goes through setup, configuration, and administration of log shipping.

Chapter 19: Database Mirroring — There is more functionality in this release for availability than ever before. This chapter covers new and existing features to help you keep your systems online for your organization.

Chapter 20: Integration Services Administration and Performance Tuning — Integration is the key to making sure systems stay in sync. This chapter focuses on administering and tuning this great feature in SQL Server.

Chapter 21: Analysis Services Administration and Performance Tuning — Analysis Services is the Online Analytical Processing (OLAP) product of choice and cannot be ignored by data administrators. This chapter helps you get prepared.

Chapter 22: SQL Server Reporting Services Administration — Reporting Services is often administered by the DBA and this book prepares you no matter what your role to handle those Reporting Services challenges.

Chapter 23: SQL Server 2012 SharePoint 2010 Integration — SharePoint is a bigger part of SQL Server than ever. This chapter covers what you need to know about how SharePoint 2010 integrates with SQL Server so you can be prepared to interact with that team or take on some SharePoint database administration responsibilities yourself.

Chapter 24: SQL Azure Administration and Configuration — This chapter introduces the reader to SQL Server Azure and gets you up and running on this exciting new cloud platform.

Chapter 25: AlwaysOn Availability Groups — This chapter focuses on the availability group feature in Always On. These groups allow you to control instances and servers as groups and assign prioritization and additional flexibility to how failover and high availability are handled in your environment.

WHAT YOU NEED TO USE THIS BOOK

To follow the examples in this book, you need to have SQL Server 2012 installed. If you want to learn how to administer the business intelligence features, you need to have Analysis Services and the Integration Services components installed. You need a machine that can support the minimum hardware requirements to run SQL Server 2012; and you also need the AdventureWorks and AdventureWorksDW databases installed. You can find instructions for accessing these databases in the ReadMe file on this book’s website at .

Some features in this book (especially in the high-availability part) require the Enterprise or Developer Edition of SQL Server. If you do not have this edition, you can still follow through some of the examples in the chapter with the Standard Edition.

CONVENTIONS

To help you get the most from the text and keep track of what’s happening, you see a number of conventions throughout the book.

image

Boxes with a warning icon like this one hold important, not-to-be forgotten information that is directly relevant to the surrounding text.

image

The pencil icon indicates notes, tips, hints, tricks, and asides to the current discussion.

As for styles in the text:

We use a monofont type with no highlighting for most code examples.
 
We use bold to emphasize code that’s particularly important in the context of the chapter.

SOURCE CODE

As you work through the examples in this book, you may choose either to type in all the code manually or to use the source code files that accompany the book. All the source code used in this book is available for download at . You will find the code snippets from the source code are accompanied by a download icon and note indicating the name of the program so you know it’s available for download and can easily locate it in the download file. Once at the site, simply locate the book’s title (either by using the Search box or by using one of the title lists) and click the Download Code link on the book’s detail page to obtain all the source code for the book.

image

Because many books have similar titles, you may find it easiest to search by ISBN; this book’s ISBN is 978-1-118-10688-4.

After you download the code, just decompress it with your favorite compression tool. Alternatively, you can go to the main Wrox code download page at to see the code available for this book and all other Wrox books.

ERRATA

Every effort is made to ensure that there are no errors in the text or in the code. However, no one is perfect, and mistakes do occur. If you find an error in one of our books, such as a spelling mistake or a faulty piece of code, we would be grateful for your feedback. By sending in errata, you may save another reader hours of frustration, and at the same time you can help us provide even higher quality information.

To find the errata page for this book, go to and locate the title using the Search box or one of the title lists. Then, on the book details page, click the Book Errata link. On this page you can view all errata that has been submitted for this book and posted by Wrox editors. A complete book list, including links to each book’s errata, is also available at .

If you don’t spot “your” error on the Book Errata page, go to and complete the form there to send us the error you have found. The information will be checked and, if appropriate, a message will be posted to the book’s errata page and the problem will be fixed in subsequent editions of the book.

P2P.WROX.COM

For author and peer discussion, join the P2P forums at . The forums are a Web-based system for you to post messages relating to Wrox books and related technologies and interact with other readers and technology users. The forums offer a subscription feature to e-mail you topics of interest of your choosing when new posts are made to the forums. Wrox authors, editors, other industry experts, and your fellow readers are present on these forums.

At you can find a number of different forums to help you not only as you read this book, but also as you develop your own applications. To join the forums, follow these steps:

1. Go to and click the Register link.

2. Read the terms of use and click Agree.

3. Complete the required information to join as well as any optional information you want to provide, and click Submit.

4. You will receive an e-mail with information describing how to verify your account and complete the joining process.

image

You can read messages in the forums without joining P2P, but to post your own messages, you must join.

When you join, you can post new messages and respond to messages other users post. You can read messages at any time on the Web. If you want to have new messages from a particular forum e-mailed to you, click the Subscribe to this Forum icon by the forum name in the forum listing.

For more information about how to use the Wrox P2P, read the P2P FAQs for answers to questions about how the forum software works as well as many common questions specific to P2P and Wrox books. To read the FAQs, click the FAQ link on any P2P page.