DBA essentials—SQL Server 2017 security, performance tuning, and more—inside out

October 18, 2018

Topic
OLTP database management

Whether you’re an experienced DBA with multiple certifications or just starting on your SQL Server DBA journey, you face challenges constantly for putting your organization’s data to work. To turn the next-to-impossible into the practical, you need a comprehensive resource at your fingertips: SQL Server 2017 Administration Inside Out, by William Assaf, Randolph West, Sven Aelterman, and Mindy Curnutt. With this guide, you can get to know new and expanded SQL Server features like Columnstore, memory-optimized indexes, and Query Store, plus understand how to administer Microsoft Azure SQL Database or SQL Server 2017 virtual machines running on Azure.

Here’s just some of what you’ll find in the ultimate guide to SQL Server 2017 (spoiler alert—get free access to these chapters below):

Database server components

Even if you have a firm foundation in SQL Server configuration and administration, you want to keep the basics at-hand so you can easily refresh your memory. SQL Server 2017 Administration Inside Out provides a solid foundation in the infrastructure that makes up a database and how it works. The guide also offers helpful tips and best practices every DBA should know, like:

Keep in mind that In-Memory OLTP requires RAM overhead that’s two times the size of a memory-optimized object.
Disable power saving everywhere. Power-saving settings in your operating system (OS) can result in poor query performance. Turn on High Performance at the OS and BIOS levels.
Realize that Redundant Array of Independent Disks (RAID) shouldn’t take the place of performing backups. It doesn’t provide 100 percent protection from data loss.
Ensure all TCP/IP traffic to and from SQL Server is encrypted. If you’re using the Shared Memory Protocol with applications that are all on the same server, this isn’t required.

Securing the server and data

Recognizing that more frequent and complex cyberattacks are targeting your environment, SQL Server 2017 Administration Inside Out covers the security capabilities of SQL Server 2017 comprehensively. The guide helps you play defense to protect your data from attack and minimize the damage should you suffer a data breach. For example, you’ll learn (or be reminded) that you can:

Prevent common SQL injection attacks by making sure that all data is escaped, sanitized, and validated before input and that all SQL Server queries are parameterized.
Use newer capabilities like Row-Level Security to restrict access through security policies based on group membership or execution context.
Start SQL Server in single-user mode or with minimal configuration and remove the offending audit if that audit shuts down your SQL Server instance.

Performance tuning SQL Server

Ensuring your database performs at top speed is one of your highest priorities. You want to have an accurate and complete understanding of performance tuning concepts, the objects typically associated with tuning SQL Server database performance, and best practices that help your databases run at top speed. In SQL Server 2017 Administration Inside Out, you’ll gain this critical knowledge, including:

The NOLOCK hint (or READ UNCOMMITTED isolation level) can return invalid data. Not only can uncommitted data be read, but committed data can be read twice or skipped. You also run the risk of returning corrupt data or finding the query has failed.
You can use the Query Store feature to help you analyze execution plan performance by looking at live Query Store data as it happens as well as at the history of statement execution. Query Store is one of the features that first appeared in Azure SQL Database and then moved to SQL Server on-premises.

All this info—all in one place

This is only a small sample of the in-depth information you can find in SQL Server 2017 Administration Inside Out. Take the book for a test drive by downloading a free custom excerpt, including the three full chapters previewed above.