Introduction
The velocity of change for the Microsoft SQL Server DBA has increased this decade. The span between the releases of SQL Server 2016 and 2017 was only 16 months, the fastest new release ever. Gone are the days when DBAs had between three to five years to soak in and adjust to new features in the engine and surrounding technologies.
This book is written and edited by SQL Server experts with two goals in mind: to deliver a solid foundational skillset for all of the topics covered in SQL Server configuration and administration, and also to deliver awareness and functional, practical knowledge for the dramatic number of new features introduced in SQL Server 2016 and 2017. We haven’t avoided new content—even content that stretched the boundaries of writing deadlines with late-breaking new releases. You will be presented with not only the “how” of new features, but also the “why” and the “when” for their use.
Who this book is for
SQL Server administration was never the narrow niche skillset that our employers might have suspected it was. Even now it continues to broaden, with new structures aside from the traditional rowstore, such as Columnstore and memory-optimized indexes, or new platforms such as Microsoft Azure SQL Database platform as a service (PaaS) and Azure infrastructure as a service (IaaS). This book is for the DBAs who are unafraid to add these new skillsets and features to their utility belt, and to give courage and confidence to those who are hesitant. SQL Server administrators should read this book to become more prepared and aware of features when talking to their colleagues in application development, business intelligence, and system administration.
Assumptions about you
We assume that you have some experience and basic vocabulary with administering a recent version of SQL Server. You might be curious, preparing, or accomplished with Microsoft Certifications for SQL Server. DBAs, architects, and developers can all benefit from the content provided in this book, especially those looking to take their databases to the cloud, to reach heights of performance, or to ensure the security of their data in an antagonistic, networked world.
This book mentions some of the advanced topics that you’ll find covered in more detail elsewhere (such as custom development, business intelligence design, data integration, or data warehousing).
Book Features
These are the book’s signature tips. In these tips, you’ll get the straight scoop on what’s going on with the software or service—inside information about why a feature works the way it does. You’ll also find field-tested advice and guidance as well as details that give you the edge on deploying and managing like a pro.
How this book is organized
This book gives you a comprehensive look at the various features you will use. It is structured in a logical approach to all aspects of SQL Server 2017 Administration.
Chapter 1, “Getting started with SQL Server tools” gives you a tour of the tooling you need, from the installation media to the free downloads, not the least of which is the modern, rapidly evolving SQL Server Management Studio. We also cover SQL Server Data Tools, Configuration Manager, performance and reliability monitoring tools, provide an introduction to PowerShell, and more.
Chapter 2, “Introducing database server components,” introduces the working vocabulary and concepts of database administration, starting with hardware-level topics such as memory, processors, storage, and networking. We then move into high availability basics (much more on those later), security, and hardware virtualization.
Chapter 3, “Designing and implementing a database infrastructure” introduces the architecture and configuration of SQL Server, including deep dives into transaction log virtual log files (VLFs), data files, in-memory Online Transaction Processing (OLTP), partitioning, and compression. We spend time with TempDB and its optimal configuration, and server-level configuration options. Here, we also cover running SQL Server in Azure virtual machines or Azure SQL databases as well as hybrid cloud architectures.
Chapter 4, “Provisioning databases” is a grand tour of SQL Server Setup, including all the included features and their initial installation and configuration. We review initial configurations, a post-installation checklist, and then the basics of creating SQL Server databases, including database-level configuration options for system and user databases.
Chapter 5, “Provisioning Azure SQL Database,” introduces Microsoft’s SQL Server database-as-a-service (DBaaS) offering. This Azure cloud service provides a database service with a very high degree of compatibility with SQL Server 2017. You will read about the concepts behind Azure SQL Database, learn how to create databases, and perform common management tasks for your databases.
Chapter 6, “Administering security and permissions” begins with the basics of authentication, the configuration, management, and troubleshooting of logins and users. Then, we dive into permissions, including how to grant and revoke server and database-level permissions and role membership, with a focus on moving security from server to server.
Chapter 7, “Securing the server and its data” takes the security responsibilities of the SQL Server DBA past the basics of authentication and permissions and discusses advanced topics including the various features and techniques for encryption, Always Encrypted, and row-level security. We discuss security measures to be taken for SQL Server instances and Azure SQL databases as well as the Enterprise-level SQL Server Audit feature.
Chapter 8, “Understanding and designing tables,” is all about creating SQL Server tables, the object that holds data. In addition to covering the basics of table design, we cover special table types and data types in-depth. In this chapter, we also demonstrate techniques for discovering and tracking changes to data.
Chapter 9, “Performance tuning SQL Server” dives deep into isolation and concurrency options, including READ COMMITTED SNAPSHOT ISOLATION (RCSI), and why your developers shouldn’t be using NOLOCK. We review execution plans, including what to look for, and the Query Store feature that was introduced in SQL Server 2016 and improved in SQL Server 2017.
Chapter 10, “Understanding and designing indexes” tackles performance from the angle of indexes, from their creation, monitoring, and tuning, and all the various forms of indexes at our disposal, past clustered and nonclustered indexes and into Columnstore, memory-optimized hash indexes, and more. We review indexes and index statistics in detail, though we cover their maintenance later on in Chapter 13.
Chapter 11, “Developing, deploying, and managing data recovery” covers the fundamentals of database backups in preparation for disaster recovery scenarios, including a backup and recovery strategy appropriate for your environment. Backups and restores in a hybrid environment, Azure SQL Database recovery, and geo-replication are important assets for the modern DBA, and we cover those, as well.
Chapter 12, “Implementing high availability and disaster recovery” goes beyond backups and into strategies for disaster recovery from the old (log shipping and replication) to the new (availability groups), including welcome new enhancements in SQL Server 2017 to support cross-platform and clusterless availability groups. We go deep into configuring clusters and availability groups on both Windows and Linux.
Chapter 13, “Managing and monitoring SQL Server” covers the care and feeding of SQL Server instances, including monitoring for database corruption, monitoring database activity, and index fragmentation. We dive into extended events, the superior alternative to traces, and also cover Resource Governor, used for insulating your critical workloads.
Chapter 14, “Automating SQL Server administration” includes an introduction to PowerShell, including features available in PowerShell 5.0. We also review the tools and features needed to automate tasks to your SQL Server, including database mail, SQL Server Agent jobs, Master/Target Agent jobs, proxies, and alerts. Finally, we review the vastly improved Maintenance Plans feature, including what to schedule and how.
About the companion content
We have included this companion content to enrich your learning experience. You can download this book’s companion content from the following page:
https://aka.ms/SQLServ2017Admin/downloads
The companion content includes helpful Transact-SQL and PowerShell scripting, as mentioned in the book, for easy reference and adoption into your own toolbox of scripts.
Acknowledgments
From William Assaf:
I’d like to thank the influencers and mentors in my professional career who affected my trajectory, and to whom I remain grateful for technical and nontechnical lessons learned. In no particular order, I’d like to thank Connie Murla, David Alexander, Darren Schumaker, Ashagre Bishaw, Charles Sanders, Todd Howard, Chris Kimmel, Richard Caronna, and Mike Huguet. There’s definitely a special love/hate relationship developed between an author and a tech editor, but I couldn’t have asked for a better one than Louis Davidson. Finally, from user groups to SQLSaturdays to roadshow presentations to books, I am indebted to my friend Patrick Leblanc, who climbed the ladder and unfailingly turned to offer a hand and a hug.
From Randolph West:
In June 2017, I told my good friend Melody Zacharias that I’d like to finish at least one of the many books I’ve started before I die. She suggested that I might be interested in contributing to this one. Piece of cake, I thought.
I have seven more gray hairs now. Seven!
I would like to thank Melody for recommending me in her stead, my husband for giving me space at the kitchen counter to write, and my dog Trixie for much needed distraction.
Trina, William, Louis, Sven and Mindy have been a great support as well, especially during the Dark Times.
This book would not be possible without the contributions of everyone else behind the scenes, too. Writing a book of this magnitude is a huge endeavour. (So help me if “endeavour” is the one word I get to spell the Canadian way!)
From Sven Aelterman:
I met William Assaf several years ago when I spoke at the Baton Rouge SQLSaturday. I have been back to this event many times since then and enjoyed preceding the Troy University Trojans’ victory over Louisiana State University. (This just added in case the actual college football game doesn’t make it in the history books. At least it will be recorded here.)
I am grateful for William’s invitation to contribute two chapters to this book. William made a valiant attempt to prepare me for the amount of work “just” two chapters would be. Yet, I underestimated the effort. If it weren’t for his support and that of Randolph West, technical editor Louis Davidson, editor Trina Macdonald, and even more people behind the scenes, the space for this acknowledgment might have been saved. They were truly a great team and valued collaborators. Without hesitation, I would go on the journey of book writing again with each of them.
My children, Edward and Sofia, and my wife, Ebony, have experienced firsthand that SQL Server can slow down time. “About two months” must have felt to them like months with 60 days each. I thank them for their patience while they had to share me with Azure and various table types. I hope that maybe my children will be inspired one day to become authors in their career fields.
Finally, I’d like to thank my coworkers at Troy University for inspiring me to do my best work. Working in a public higher education institution has some challenges, but the environment is so conducive to intellectual growth that it makes up for each challenge and then some.
From Mindy Curnutt:
I would like to thank Patrick LeBlanc for inviting me to participate in the creation of this book. Thanks also to Tracy Boggiano, for an amazing amount of help pulling together much of the chapter about automating administration. She’s an MVP in my eyes! To everyone in the 2016-2017 TMW DBA Services “Team Unicorn”: Eric Blinn, Lisa Bohm, Dan Andrews, Vedran Ikonic, and Dan Clemens, thank you for your proof reading and feedback. Thanks to my mom Barbara Corry for always swooping in to help with just about anything I needed. Of course, I couldn’t have done any of this without the support of my husband, Chris Curnutt. He is always supportive despite long work hours, phone conversations with strange acronyms, and travel, he’s also the love of my life. Last but not least, thanks to our two children, Riley and Kimball, who have supported and encouraged me in more ways than I can count.
Support and feedback
The following sections provide information on errata, book support, feedback, and contact information.
Errata & support
We’ve made every effort to ensure the accuracy of this book and its companion content. You can access updates to this book—in the form of a list of submitted errata and their related corrections—at:
https://aka.ms/SQLServ2017Admin/errata
If you discover an error that is not already listed, please submit it to us at the same page. If you need additional support, email Microsoft Press Book Support at [email protected].
Please note that product support for Microsoft software and hardware is not offered through the previous addresses. For help with Microsoft software or hardware, go to https://support.microsoft.com.
Stay in touch
Let’s keep the conversation going! We’re on Twitter at http://twitter.com/MicrosoftPress.