SQL Server 2019 Administrator’s Guide, 2nd Ed
Article Index
SQL Server 2019 Administrator’s Guide, 2nd Ed
Chapters 5-9
Chapters 9-12; Conclusion

Chapter 9 Configuring Always On High Availability Features 

With systems needing to be increasingly available, the importance of High Availability (HA) is growing. This chapter opens with a step-by-step walkthrough of installing Windows Server Failover Cluster (WSFC) which underlies the preferred methods of HA in SQL Server. The walkthrough includes helpful screenshots and discussion of configuration settings. There’s a useful section showing how to manually failover with the Failover Cluster Management tool.

Next, there’s another useful walkthrough on configuring AlwaysOn Availability Groups (AOAG), together with the various configuration settings. There’s a very useful point about using secondaries for read-only access (allowing you to take advantage of additional CPUs, memory, IO subsystem etc). There’s also a useful section on monitoring and failover.

This is another good chapter, replete with helpful walkthroughs and screenshots, and updated to include Windows Server 2019. The chapter is mostly easy to read. I do wonder if this chapter should be adjacent to the Disaster Recovery chapter, although they are not the same, they do have a conceptual degree of overlap.

Chapter 10 In-Memory OLTP - Why and How to Use it 

In-Memory OLTP, with its tempting performance improvements, is probably the main reason to upgrade from earlier versions of SQL Server. Although introduced in 2014 it had many limitations, most of these have been removed in later versions.

This chapter opens with a discussion of the underlying In-Memory OLTP architecture, which is transparent to the user/client. The differences between the older disk-based system and the In-Memory OLTP system are discussed and then summarised in a useful table. Next the chapter discusses Natively Compiled Stored Procedures (NCSP), and other objects, which can run significantly faster than traditional SQL code. The remaining In-Memory OLTP limitations are discussed (e.g. can’t use XML data type). The new lock and latch free concurrency model (MVCC) is briefly discussed.

There’s a useful walkthrough of setting up In-Memory tables, this is followed with a practical section on creating In-Memory OLTP objects (indexes, NCSP). There’s a discussion on the different types of in-memory indexes (i.e. Hash, Range, and Columnstore). Next, there’s a section on migrating objects to In-Memory OLTP, and the Transaction Performance Analysis Overview report is highlighted (it’s a very useful starting point for migration!).

The section next looks at some practical example scenarios, relating to a water company with 100,000 sensors relaying data every 5 seconds to an In-Memory OLTP application. The examples create various tables, stored procedures, and indexes.

The Chapter ends with a brief look at the reports/DMVs that can be used to monitor In-Memory OLTP (e.g. memory usage).

Overall, the content of this chapter is often good, but... There is still a problem with errors, incorrect statements, and the use of English which can prevent an understanding of what is being discussed. The chapter says for in-memory tables “Clustered indexes are allowed” then later it states “In-memory tables do not support clustered indexes.” – the latter is correct. Several times the text has the number 220 instead of 220. The impact of bucket count with hash indexes, and its effect on performance is inadequate (also both under and over estimation should be explained). The chapter says “In the next section, we will briefly describe how to monitor data contention in an In-Memory OLTP environment.” – however the next section is about “MIGRATING DISK-BASED OBJECTS TO IN-MEMORY OLTP”. The “Transaction Performance Analysis Overview” report is incorrectly written as “Transaction Analysis Performance Overview”. And “…details for the Details for Table Name report” is nonsense, it should read “…details for the Table Usage report”.

Chapter 11 Combining SQL Server 2019 with Azure 

If you look up Microsoft Azure on the ITJobsWatch website you'll discover Microsoft Azure jobs, it is currently the 3rd most popular skill in demand in the UK. Two years ago it was the 17th most popular skill – highlighting the rapid rise of the importance of the cloud to SQL Server.

This chapter opens with a brief look at the history and advantages of using computers in the cloud (e.g.  elastic demand, cheaper). There’s a useful link to creating an Azure trial account, allowing you to follow along with the examples in the chapter.

Next, the chapter looks at data-related technologies, outlining Microsoft’s various offerings, including: Storage Account, Cosmos DB, Azure Data Factory, Azure SQL Database, Azure SQL Server. The technologies are discussed very briefly, leading to perhaps inadequate discussions.

There’s a closer look at the core technologies that make up Azure, namely: 

  • Azure SQL Database – service hosting isolated databases

  • Azure SQL Server – virtual machine hosted inside a virtual environment

  • Azure Synapse – load balanced massively parallel processing data warehouse 

In each case, details of how to create and use the technology are provided, together with instructive step-by-step walkthroughs and screenshots.

The chapter then changes direction, and examines how SQL Server 2019 can be used with various hybrid cloud scenarios. This is useful because sometimes it is not possible or easy to migrate to the deeper cloud Azure offerings. The options discussed are: 

  • Data files in Azure – SQL Server runs on-premise but database files hosted in Azure

  • Backups to Azure – storing backups in Azure 

The useful option of the StretchDB has been omitted (it was present in the book’s first edition). The chapter next provides a useful, if brief, overview of things to consider when using SQL workloads in Azure, these are: licensing, SLA, disaster recovery, and regular database maintenance. In each case, the advantages of each option are outlined, together with walkthroughs and screenshots.

The chapter ends with a discussion on migrating SQL Server workloads to Azure, covering: 

  • Using Azure Database Migration Guide (very useful central website)

  • Using SQL Server Migration Assistant (migrating from SQL Server)

  • Using Data Migration Assistant (migrating from non-SQL Server databases)

  • Using Azure Database Migration Service (automates repeatable migrations) 

In each case, a helpful, if brief, walkthrough of the tool is provided.

This chapter was an interesting read, and should prove useful for those wanting to know more about SQL Server and the cloud (which should be everyone).

There is a minor amount of waffle/unqualified statements at start of chapter. The standard of English, while generally understandable, is below what I would expect (e.g. “Typically, we need to transform data; that is, aggregate it somehow, normalize the data, and so on.” Various terms are not defined (e.g. AI, IoT, tenant, JSON, notebook, blade).

The technologies discussed are quite varied, and an introduction like this can only provide an overview – this may lead to assumptions being made and cutting corners.

I must admit, I get a little confused about all the SQL options on Azure, which is made worse with Microsoft’s penchant for renaming them (e.g. Azure Synapse used to be called Azure SQL Data Warehouse).

Chapter 12 Taming Big Data with SQL Server 

With ever increasing amounts of data, traditional relational databases are unable to process the data in a timely manner. Other technologies have been developed to process this Big Data, these typically involve processing the distributed and redundant data in-situ across many servers, and recombining the results. It is generally more efficient to process data in this way than you move it all into a central store for processing.

This chapter opens with a brief overview of Big Data, discussing the common 5Vs (i.e. volume, veracity, variety, velocity, value) that underly the need for Big Data.

Next, PolyBase is examined, this is Microsoft’s technology that allows you to access data without moving the data. It is able to work with various data sources, including: ODBC, NoSQL, Relational databases, and Big Data. Some very brief use cases are provided (borderline waffle to me). Details on how to install and configure PolyBase are given next, this section provides good instructions together with useful screenshots.

Next, the architecture SQL Server Big Data Clusters (BDC) is examined, being composed of SQL Server, Apache Spark, and HDFS – all running within Kubernetes. The section continues with a look at deploying BDC, using Azure Data Studio – with helpful screenshots and instructions are provided.

The chapter ends with a discussion on SQL Server Big Data Clusters workloads, with a useful example provided.

Perhaps because Big Data is a broad subject area, too often keywords are introduced with insufficient explanation (e.g. Spark, HDFS, MapReduce). You might need to know about Big Data technologies already to get the most from this chapter.

Conclusion  

I’m not sure how much you can learn from this book. In many ways, you need to be an expert in SQL Server administration, and have a good understanding of English, to understand it fully.

The book’s subject areas are largely the correct ones, it does have some useful content, tips, and walkthroughs. However, it is let down significantly, in several chapters, by the poor use of English, muddled thinking, and some incorrect assertions. The book clearly needs better editing.

Maybe 75% of the book is good (rating 4 out of 5), the other 25% is problematic, hopefully you can use this review to guide you to the good parts…

When I reviewed the 2017 edition of this book I gave it a rating of 1 on the grounds of it being ones of the most frustrating books I have ever reviewed. This second edition is better, but still falls short, having many of the same problems.

Echoing what I said then:

What can be done so the book’s next edition will be an improvement? Firstly, the book needs editing by someone with a better knowledge of English. It also needs a technical editor that can challenge the assertions made. It also needs a gentler introduction to the topics, rather than assume some detailed knowledge already.

For recommendations of SQL Server books see Pick of the Shelf - SQL Server in our Programmers' Bookshelf section

Banner
 


High Performance SQL Server, 2nd Ed (Apress)

Author: Benjamin Nevarez
Publisher: Apress
Pages: 420
ISBN: 978-1484264904
Print: 1484264908
Kindle: B08TQR3NMF
Audience: SQL Server DBAs and Devs
Rating: 4.5
Reviewer: Ian Stirk

This book aims to improve the performance of your SQL Server, how does it fare?



Python Programming and Visualization for Scientists 2nd Ed

Author: Alex DeCaria and Grant Petty
Publisher: Sundog Publishing
Pages: 372
ISBN: 978-0972903356
Print: 0972903356
Audience: Scientists wanting to use Python
Rating: 2
Reviewer: Mike James
Visualization - a difficult topic and difficult to see how to explain the ideas in a book.


More Reviews

 

 



Last Updated ( Tuesday, 24 November 2020 )