New book: SQL Server 2017 Administration Inside Out

We’re pleased to announce the availability of SQL Server 2017 Administration Inside Out (ISBN 9781509305216), by William Assaf, Randolph West, Sven Aelterman, and Mindy Curnutt.

Purchase from these online retailers: Microsoft Press Store Amazon Barnes & Noble Independent booksellers – Shop local

Overview

Conquer SQL Server 2017 administration—from the inside out!

Dive into SQL Server 2017 administration—and really put your SQL Server DBA expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds—all you need to plan, implement, manage, and secure SQL Server 2017 in any production environment: on-premises, cloud, or hybrid.

Four SQL Server experts offer a complete tour of DBA capabilities available in SQL Server 2016 Database Engine, SQL Server Data Tools, SQL Server Management Studio, and via PowerShell. Discover how experts tackle today’s essential tasks—and challenge yourself to new levels of mastery.

• Install, customize, and use SQL Server 2017’s key administration and development tools
• Manage memory, storage, clustering, virtualization, and other components
• Architect and implement database infrastructure, including IaaS, Azure SQL, and hybrid cloud configurations
• Provision SQL Server and Azure SQL databases
• Secure SQL Server via encryption, row-level security, and data masking
• Safeguard Azure SQL databases using platform threat protection, firewalling, and auditing
• Establish SQL Server IaaS network security groups and user-defined routes
• Administer SQL Server user security and permissions
• Efficiently design tables using keys, data types, columns, partitioning, and views
• Utilize BLOBs and external, temporal, and memory-optimized tables
• Master powerful optimization techniques involving concurrency, indexing, parallelism, and execution plans
• Plan, deploy, and perform disaster recovery in traditional, cloud, and hybrid environments

For Experienced SQL Server Administrators and Other Database Professionals
• Your role: Intermediate-to-advanced level SQL Server database administrator, architect, developer, or performance tuning expert
• Prerequisites: Basic understanding of database administration procedures

Contents at a glance

Chapter 1     Getting started with SQL Server tools
Chapter 2     Introducing database server components
Chapter 3     Designing and implementing a database infrastructure
Chapter 4     Provisioning databases
Chapter 5     Provisioning Azure SQL Database
Chapter 6     Administering security and permissions
Chapter 7     Securing the server and its data
Chapter 8     Understanding and designing tables
Chapter 9     Performance tuning SQL Server
Chapter 10   Understanding and designing indexes
Chapter 11   Developing, deploying, and managing data recovery
Chapter 12   Implementing high availability and disaster recovery
Chapter 13   Managing and monitoring SQL Server
Chapter 14   Automating SQL Server administration

Foreword

The world as we know it is being inundated with data. We live in a culture in which almost every individual has at least two devices, a smart phone, and a laptop or computer of some sort. Everything we do on these devices is constantly collecting, sharing, or producing data. This data is being used not only to help organizations make smarter decisions, but also to shape and transform how we as a society live, work, make decisions, and sometimes think.

This massive explosion can be attributed to the technological transformation that every business and nearly every industry is undergoing. Every click or purchase by an individual is now triggering some event that triggers another event that likely amounts to hundreds or possibly thousands of rows of data. Multiply this by every person in the world and now you have an unprecedented amount of stored data that no one could have ever imagined. Now, not only must organizations store this data, but also ensure that this data—this massive amount of data—is readily available for consumption at the click of a button or the swipe of a screen.

This is where the database comes into play. Databases are the backbone or back end to possibly every aspect of business today. Back when Ted Codd, the father of the relational database, came up with this seminal idea, he probably had no idea how widespread their use would be today. Initially, database usage was intended to store data and retrieve data. The primary purpose was to simply ensure the security, availability, and reliability of any information written by on-premises applications at varying scales.

Today, all of that has changed. Data must be available 24 hours per day, 7 days each week, primarily via the internet instead of just by way of on-premises applications. Microsoft SQL Server 2017 was designed with all of this in mind. It can support high-volume Online Transactional Processing (OLTP) databases and very large Online Analytical Processing (OLAP) systems out of the box. And, by taking advantage of Microsoft Azure, developers can grow and scale databases dynamically and transparently behind the scenes to accommodate planned and unplanned spikes in demand and resource utilization. In other words, the latest version of SQL Server was built to not only accommodate this new world of data, but to push the limits of what organizations are doing today and what they will be doing tomorrow and deeper into the future.

Close your eyes and imagine a world in which a DBA can configure a database system to automatically increase or decrease resource utilization based on end-user application usage. But that’s not all. What if the relational database management system (RDBMS) could automatically tune performance based on usage patterns? All of this is now possible with SQL Server and Azure SQL Database. By using features such as the Query Store and Elastic Database Pools, DBAs can proactively design solutions that will scale and perform to meet any application Service-Level Agreement.

In addition to world-class performance, these databases also include security and high-availability features that are unparalleled to any other RDBMS. Organizations can build mission-critical secure applications by taking advantage of SQL Server out-of-the-box built-in features without purchasing additional software. These features are available both in the cloud and on-premises and can be managed using SQL Server Management Studio, SQL Server Data Tools, and SQL Operations Studio. All three tools are available to download for free, and will be familiar to DBAs and database developers.

Throughout this book, the authors highlight many of the capabilities that make it possible for organizations to successfully deploy and manage database solutions using a single platform. If you are a DBA or database developer looking to take advantage of the latest version of SQL Server, this book encompasses everything needed to understand how and when to take advantage of the robust set of features available within the product.

This book is based on the skills of a group of seasoned database professionals with several decades experience in designing, optimizing, and developing robust database solutions, all based on SQL Server technology. It is written for experienced DBAs and developers, aimed at teaching the advanced techniques of SQL Server.

SQL Server, Microsoft’s core database platform, continues its maturity from supporting some of the smallest departmental tasks to supporting some the largest RDBMS deployments in the world. Each release not only includes capabilities that enhance its predecessor, but also boasts features that rival and exceed those of many competitors.

This trend continues with SQL Server 2017. This release, just like all past releases, continues to add capabilities to an already sophisticated and reliable toolkit. Features include a secure, elastic, and scalable cloud system; advanced in-memory technologies; faster and consolidated management and development experiences; and continued growth and enhancements in the area of high availability and disaster recovery. In addition, concerted efforts have been focused on making the number one secure RDBMS in the world even more secure, by adding capabilities such as row-level security, Always Encrypted, and dynamic data masking. Finally, and as always, performance is at the center of this release. With enhancements to the Query Store, DBAs can take a more proactive approach to monitoring and tuning performance.

All in all, this book is sort of like an “Inside Out” look of each of the core components of SQL Server 2017, with a few excursions into the depths of some very specific topics. Each chapter first provides and overview of the topic and then delves deeper into that topic and any corresponding related topics. Although it’s impossible to cover every detail of every Transact-SQL statement, command, feature or capability, this book provides you with a comprehensive look into SQL Server 2017. After reading each page of this book, you will be able implement a cloud-based or on-premises scalable, performant, secure, and reliable database solution using SQL Server 2017.

Patrick LeBlanc, Microsoft

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).

About the authors

William Assaf, MCSE, is a Microsoft SQL Server consultant and manager and blogs about SQL at sqltact.com. William has been a designer, database developer, and admin on application and data warehousing projects for private and public clients. He has helped write the last two generations of Microsoft SQL Server certification exams since 2012 and has been a Regional Mentor for PASS since 2015. William and fellow author Patrick Leblanc worked together on SQL Server 2012 Step by Step (Microsoft Press, 2015), having met at and together led the SQL Server User Group and SQLSaturday in Baton Rouge. William and his high school sweetheart enjoy travelling to speak at SQLSaturdays around the south, and hope to see to see you there, too.

Randolph West is a Data Platform MVP from Calgary, Alberta, Canada. He is co-organizer of the Calgary SQL Server User Group and Calgary SQLSaturday. He speaks at various conferences around the world, and acts on stage and screen. Randolph specializes in implementing best practices, performance tuning, disaster recovery, and cloud migrations, through his company Born SQL. You can read his blog at bornsql.ca.

Sven Aelterman started with SQL Server when he first deployed version 2000 in a failover cluster scenario. Since then, he has worked as IT manager, principal consultant, and IT director. He currently serves the Trojans (students) of Troy University as a lecturer in information systems in the Sorrell College of Business and as director of IT for the College. In addition, he is cloud software architect for Sorrell Solutions, a business services nonprofit through which Trojans can gain real-world business and IT experience. In a fledgling attempt to give back to the community, he has spoken at many SQLSaturdays and code camps in the southeastern United States since 2005. He spoke about SSIS 2012 at Microsoft TechEd 2011. In 2012, he coauthored a book dedicated to SQL Server FILESTREAM. His involvement with Microsoft Azure resulted in the organization of two Global Azure Bootcamp events at Troy University. Sven blogs about a variety of Microsoft technologies at svenaelterman.wordpress.com and tweets and retweets about technology @svenaelterman.

Mindy Curnutt, an independent consultant, is 4X Microsoft Data Platform MVP and Idera ACE. She has been actively involved in the SQL Server Community for more than a decade, presenting at various User Group Meetings, SQLPASS Summits, as well as SQLSaturdays across North America. For two years, she was a team lead for the SQLPASS Summit Abstract Review Process and since 2015 has served as one of the three SQLPASS Summit program managers. She was a SME for a couple of the SQL 2012 and 2014 Microsoft SQL Server Certification Exams and helped to author SQL Server 2014 Step by Step. Mindy currently serves on the board of directors for the North Texas SQL Server User’s Group. She also serves as a mentor to others, helping to educate and promote scalable and sustainable SQL Server architecture and design. She is passionate about Data Security, Accessibility, Usability, Scalability and Performance. You can follow Mindy at her blog, mindycurnutt.com and on Twitter where she’s known as @sqlgirl.