Part III. SQL Server Management

This section covers the more traditional aspects of database administration, monitoring, automation, recovery, High Availability (HA) and Disaster Recovery (DR).

The section opens with a look at the detection and prevention of database corruption. As usual, some very insightful tips are provided. I often feel the DBCC option REPAIR_ALLOW_DATA_LOSS should be called something more dramatic (REPAIR_BUT_YOU_MAY_SERIOUSLY_LOSS_YOUR_DATA?!), there’s a useful link to Paul Randel’s ideas on this setting. The importance of valid backups is emphasised – being the DBAs most important task.

The importance of index fragmentation and up-to-date statistics is discussed in relation to performance. Monitoring usage via DMOs (DMVs/DMFs) and Extended Events (XEs) are discussed with simple examples. Useful methods for collecting monitoring data are outlined. New software upgrades and patches are always of interest to DBAs, in the later versions of SQL Server service packs (SPs) are retired and replaced by scheduled cumulative updates (CUs) – you just need to know when is a good time to test and then apply them to your live servers.

With a greater focus on saving money, there has been increased movement towards task automation, which additionally often provides improved performance and reduces human error. Various automation components are discussed, including SQL Agent, Database Mail, Maintenance Plans, Master/Target servers, and PowerShell. There’s a useful section on troubleshooting database mail, with helpful code. There’s a very useful tip about using dbatools to further enhance your use of PowerShell for SQL Server - I cannot recommend this tool highly enough.

The next chapter covers what is undoubtedly the most important area for DBAs, backup and recovery. All relevant topics are covered with expert insight. This leads naturally to a discussion of the related topics of High Availability (HA) and Disaster Recovery (DR). Configuration, design, and administration of the various options is discussed. There’s a useful table comparing the capabilities of the various HA/DR technologies.

In many ways, this section covers the more traditional and most important SQL Server administration areas. As always, rather than step-by-step implementation walkthroughs, the authors outline a topic and then discuss its salient points, together with some expert tips.  

Part IV. Security

With data breaches increasingly being reported, the importance of security cannot be overemphasised. This section opens with details on how SQL Server implements security via authorization. Topics covered include: authentication modes (including Azure AD), server and database principals, and moving logins and permissions. There’s a very helpful section covering common security problems together with their solutions (e.g. fixing orphaned SIDs).

Closely aligned to permissions, the next chapter looks at the privacy aspect of security via encryption, and auditing. Various types and layers of security are discussed in relation to thwarting attacks. The new Ledger feature, which detects any data tampering, is discussed. There’s a very useful background primer on protocols to get you up to speed.

The information in this section should be read by all DBAs, it contains an array of features and tips to ensure only authenticated logins have authorized access to appropriately protected SQL Server data.