SQL Server 2017 Administrator's Guide
Article Index
SQL Server 2017 Administrator's Guide
Chapters 7 - 12; Conclusion

Author: Marek Chmel & Vladimir Muzny
Publisher: Packt 
Pages: 434
ISBN: 978-1786462541
Print: 1786462540
Kindle: B075V9LQ97
Audience: DBAs and curious devs
Rating: 1.0
Reviewer: Ian Stirk

This book aims to give you the skills to set up, administer, and maintain SQL Server 2017 databases - how does it fare?

See SQL Server 2019 Administrator’s Guide, 2nd Ed for Ian Stirk's review of the latest edition of this book.

SQL Server 2017 is the latest version of Microsoft’s flagship database. With each version, complexity increases as new features are added and existing features extended – explaining this complexity can be a difficult task.

This book aims to cover a broad range of SQL Server topics, including: set up, administration, maintenance, configuration, security, optimization, upgrading, Disaster Recovery (DR), High Availability (HA), and best practices.

The book is primarily aimed at database administrators, but should also be accessible to curious SQL developers. The book assumes some experience of previous versions of SQL Server, it also optimistically suggests the book might be useful for those new to database administration.  

Below is a chapter-by-chapter exploration of the topics covered.

Chapter 1 Setting up SQL Server 2017    

This chapter opens with an overview of the major components in SQL Server 2017, outlining their main purpose. The features examined include: 

  • Database Engine

  • SQL Server Integration Services (SSIS)

  • SQL Server Analysis Services (SSAS)

  • SQL Server Reporting Services (SSRS)

  • Machine Learning Services (i.e. R and Python)

  • SQL Server Agent (A scheduling tool) 

Next, some of the more common pre-installation tasks are discussed, including memory needs, disk space, and software. The rest of the chapter is taken up with the installation process, discussing both attended and unattended installation. Various setup screens are discussed and important features highlighted (e.g. collation sequence – can’t be changed after install unless reinstall SQL Server).

I found the chapter quirky, the authors have a habit of selecting certain areas only for comment – I expected a more consistent and leveled approach. Additionally, at times the content reads like overhearing a conversation between SQL Server experts, the language used can be loose e.g. “As mentioned previously, it's obvious that SSAS has to be installed on its own computer. The only disadvantage is that separate installations of SQL Server services lead to separate licensing.” – Well no, there are many other disadvantages to having a separate installation, including hardware cost, maintenance, complexity, etc. What the authors say is largely correct, but the loose language should have been more qualified. Perhaps I’m being picky…

To get the most out of the book you need to be an admin already, else you won’t understand the selective content, incidental comments, and the sudden appearance of various tips, pitfalls etc. This is not a book for beginners.

The chapter is generally well written, with helpful diagrams and useful links. Unfortunately, several of the subsequent chapters are marred by the poor use of English.

Chapter 2 Keeping Your SQL Server Environment Healthy    

This brief chapter highlights the importance of keeping your SQL Server software up to date. The differences between Service Packs, Cumulative Updates, and Security Updates are highlighted.

The chapter discusses some common configuration settings that affect your server’s health (e.g. Power Plan setting). The chapter ends with a discussion on the importance of taking a performance baseline, and suggests some tools/settings to monitor (e.g. Performance Monitor). The use of Performance Monitor is given in greater detail in Chapter 7, but no cross reference is provided here.

I found this chapter a good read, but again selective in content (there is so much more to ensuring you have a healthy environment).

Chapter 3 Backup and Recovery

Backup and recovery are the most important tasks for any DBA. This chapter opens with a discussion on how data is stored, covering filegroups, data files, and the transaction log. Next the purposes of the major system databases (e.g. MSDB) are outlined.

The chapter discusses the backup process, covering the recovery model, backup types, differential backups, and the transaction log. The section ends with a review of some advanced backup features (e.g. filegroup backup).

The chapter continues with a look at the restore process, covering backup strategies, full and transaction log backups, point in time recovery, and restoring system databases.

I found this chapter the most worrying in the whole book. The English usage is poor, and some of the examples provided are tortuous – both these factors lead to muddled explanations. Additionally, some of the explanations are incorrect (e.g. the two phase commit). Often assertions are made without giving a satisfactory explanation.  

If you’re a native English speaker, the problems with the use of English can gripe after a while. Here are some of the many examples: “…called write-ahead-logging, shortly WAL…”, “…copy of used data pages…”, “…and for how long time committed transactions will be stored… ”, “…a full backup can be performed with no respect to the recovery model set…”, “It could lead to disk insufficiency as well…”, “A transaction log backup is a consequencing backup…”.

In many ways, this is the most important chapter in the book, and is singularly the worst written chapter, muddled, incorrect, with poor English usage. This chapter sinks the whole book. I was glad to reach the end of this chapter, but it almost discouraged me from continuing reading. This chapter could also be viewed as dangerous…

Chapter 4 Securing Your SQL Server

The increasing reporting of security breaches and its impact on company reputation and profitability shows the importance of security.  This chapter opens with a review of the various SQL Server service accounts, discussing how and where they can be used.

Next, the chapter moves on to the various means that SQL Server uses for Authentication, Authorization, and auditing. The chapter ends with a brief look at how SQL Server uses encryption, covering Transparent Data Encryption, and the newer Always Encrypted functionality.

This chapter provides a useful overview of securing your SQL Server, but it is only an overview, it may have been helpful to include some best practices.

Chapter 5 Disaster Recovery Options

Ensuring your databases are available after problems occur is high on the list of important DBA tasks. This chapter opens with a good overview of the options SQL Server provides for High Availability (HA) and Disaster Recovery (DR), including: 

  • Always On Failover Cluster

  • Always On Availability Groups (AOAG)

  • Database mirroring

  • Log shipping

  • Replication 

Each option is discussed only briefly, and some have supporting diagrams – however the diagrams are not explained. The chapter then discusses replication in more detail, providing a step-by-step walkthrough on setting it up. Configuration of database mirroring and log shipping is discussed with useful screenshots. The importance of having a DR plan that is regularly tested is highlighted.    

This chapter provides some useful content, but leaves a lot unanswered. It says that DR and HA are not the same thing – but then proceeds not to tell the reader the differences. Some formulae/terms are given (e.g. MTBF, failover quorum, witness server) but not explained.  I would have expected the chapter on AOAG to follow next, since DR and HA do have a degree of similarity, but we have to wait until Chapter 10. There are some minor problems with the use of English.

Chapter 6 Indexing and Performance

This chapter opens with an overview of the importance of performance monitoring, and the value of having a performance baseline is noted – although this is not linked to the discussion of having a baseline given in chapter 3.

The chapter proceeds with a look at some of the common tools used in monitoring performance, including: 

  • Activity Monitor

  • Performance Monitor

  • SQL Profiler and Trace

  • Extended Events

  • Dynamic Management Views (DMVs)

  • Management Data Warehouse (MDW) 

For each tool, a brief overview of its functionality together with how to use it is given. Helpful screenshots and some step-by-step walkthroughs are provided.  

Next, the chapter changes track and discusses various aspects of indexes. Indexes are a primary means of improving performance. The chapter discusses the various types of indexes (i.e. heap, clustered, non-clustered, columnstore, and others).  The chapter ends with a useful list of index recommendations.

This chapter contains some useful areas, however, like other chapters these are offset significantly by poor use of English, muddled thinking, together with unsubstantiated and incorrect assertions.

Some sample concerns (there are many more) are: 

  • Section incorrectly entitled “How to use dynamic management”

  • Poor English “Dynamic management consists of a huge set of views and table-valued functions so it's intended for the querying.”, and “…we need to find the rot cause of why the system slows down”

  • Incorrectly states “SQL Server enables the creation of up to 499 non-clustered indexes on one table…”

  • Weak/incorrect description of parameter sniffing and covered indexes

  • Incorrectly says “Heap is not a complicated structure but it is absolutely useless for tables bigger than several rows.” (They are useful for fast loading of staging tables, writing log rows that are rarely queried, also temp tables are heaps!)

  • Although performance monitor is mentioned, the use of Performance Analysis for Logs (PAL) to aid the analysis of the output is not mentioned

  • The chapter should link to the chapter on automation, defrag indexes etc 

Last Updated ( Tuesday, 24 November 2020 )