Author: Kellyn Gorman et al
Publisher: Packt Publishing
Pages: 488
ISBN: 978-1838826215
Print: 1838826211
Kindle: B088BNMRQ4

Chapter 10. Enhancing the Developer Experience

Each new version of SQL Server introduces new and enhanced features for SQL developers. This chapter takes a selective look at some of these, including: 

• SQL Graph database – uses nodes (entities) and edges (relationships) to represent natural many-to-many relationships between things of interest

• Java Language extensions – allows external java code to be run (cf: R and python)

• JSON – standard method of representing name/value data, its support in SQL Server allows easier data movement and processing by applications

• UTF-8 support – has become the standard way to represent any characters

• Temporal tables – stores data changes, allowing data to be retrieved from any point in time, useful for auditing and recovering from errors 

• Spatial datatypes – data types used for mapping systems (has some new features for mapping Australia more accurately) 

In each case, the feature is explained and a suitable example provided together with website links for further information. Some of these features are old, special data types were introduced in 2008! (A minor Australian enhancement is introduced, but not used). Perhaps more emphasis could have been given to other newer/enhanced features.

This chapter provides a useful overview of some of the new and enhanced features available to SQL developers. The examples provides should prove useful.

Chapter 11. Data Warehousing

Data warehouses (DWs) are typically used to aggregate large volumes of data from various disparate systems, providing useful insights into historical data and as input into making predictions. This chapter discusses some of the newer DW features.

The chapter opens with a look at how SQL Server Integration Services (SSIS) is used to load DWs, there’s a useful list of best practices (e.g. filter the source to extract only changed records). 

Various DW enhancements are discussed next, including: 

• In-memory columnstore indexes – typically provides significant performance improvements

• Partitioning – typically improves maintenance and may improve performance

• Online resumable index management – allows for greater concurrency and performance

• Creating and maintaining statistics – helps optimizer create good execution plan 

In each case, the feature is discussed, but examples are not given, instead, website links are provided for further information. There are some useful suggestions for updating statistics on columnstore indexes (e.g. consider asynchronous statistics). While statistics is a very important topic, I’m not sure why it’s discussed here, it is neither new nor enhanced, or specific to DWs…   

The chapter now diverges, and looks at DW from an Azure perspective. Topics discussed include: 

• Azure SQL Data Warehouse architecture – control/compute nodes, storage

• Best practices – dynamic scalability, don’t over partition

• Azure Data Factory (ADF) – provides ‘similar’ functionality to SSIS

• Analyzing data – using Power BI (various types) 

In each case, the feature is discussed, and where suitable an example provided. 

Note: the new name for Azure Data Warehouse is Azure Synapse Analytics – which to my mind is confusing. The joys of marketing!

This chapter provides a useful introduction to a big topic. While much more could be said, it gives a useful starting point, and unlike many other chapters, doesn’t make too many assumptions about the reader’s knowledge of the topic. 

Chapter 12. Analysis Services

Analysis Services is used to create data models which are subsequently queried in decision support. There are 2 versions, multidimensional (cubes) and tabular (compressed and in-memory).

The chapter opens with a brief overview of both the multidimensional and tabular modes, including the tools typically used, deployment options, and clients for querying. The chapter looks at tabular mode enhancements, including: memory settings for resource governance, DirectQuery, and many-to-many relationships. Next, there’s an introduction to DAX (the language for querying Analysis Services), including calculations, filtering, and some best practices. 

The chapter then looks at Azure Analysis Services (AAS). The first item discussed is cost/functionality, where you need to ensure you select the tier appropriate to your usage (developer, basic or standard). The ability to scale up or down, together with pausing and resuming, are suggested as methods of reducing costs. Security, monitoring, and tools are briefly discussed. The chapter ends with a step-by-step walkthrough on provisioning AAS and deploying a tabular model.

This chapter provides a helpful overview of Analysis Services (both on-premise and Azure), what it is and how it can be used. The introduction to DAX should prove useful to readers new to Analysis Services. 

Chapter 13. Power BI Report Server

Power BI is Microsoft’s preferred SQL Server reporting tool. It contains all the functionality of SQL Server Reporting Services (SSRS), together with some additional functionality. Various versions of Power BI exist, and this chapter focuses on Power BI Report Server. 

First, there’s a look at how to migrate existing SSRS reports to Power BI Report Server, very useful if you want to get additional functionality, with frequent updates/releases. A step-by-step walkthrough of this is provided. 

Next, various new features are briefly discussed, including: Performance Analyzer (identify slow report components), New Modeling View (caters for complex datasets), and Reporting Theming (quickly apply corporate colour branding).  

The chapter proceeds with a look at report access, publishing, and viewing reports on various devices (browsers and mobile devices). This is followed with a brief practical discussion on how to get the most from the reports (e.g. filtering, sorting, and drill-down).

Overall, this chapter provides a useful all-round introduction to Power BI Report Server, covering migration, installation, report creation, publishing, viewing and exploration. 

Chapter 14. Modernization to the Azure Cloud

There is increasing movement towards using cloud based systems. These provide many advantages, including: speed of provisioning, scalability, and cost. 

The chapter opens with a look at Azure SQL Managed Instance, which is often used in lift-and-shirt scenarios. Patching, maintenance, High Availability (HA) and Disaster recovery (DR) are taken care of, allowing companies to concentrate on their core strengths, their business. There’s a brief discussion on how to set up a managed instance, via both the Azure portal and by using templates.

Next, there’s a discussion on how to migrate an existing database to a SQL Server Managed Instance. Several useful migration tools are briefly discussed (I can certainly vouch for the usefulness of the Data Migration Assistant).

The chapter continues with a look at using SQL Server in Azure VM, which gives most control over the server, but you typically have more administration tasks (e.g. patching). Again, the process of creation is discussed, using the Azure portal, and command line (latter is useful when you need to provision many servers consistently).

Perhaps the most popular SQL Server offering in Azure is Azure SQL Database, here there is a focus on the database rather than the server (you have less control, and more is done for you). This option is only briefly mentioned, in a confusing manner, where the author groups together Azure SQL Database and Managed Instance. 

To get some kind of perspective of what Azure provides, it might have been better to have briefly outlined the Azure options at the start of the chapter. For the record, here are the options: 

• Azure SQL Virtual Machine – Azure manages host/hardware, you manage the VM

• Azure SQL Managed Instance – Azure manages host/hardware and VM, you manage SQL Server

• Azure SQL Database – Azure manages host/hardware, VM, and SQL Server, you manage the database  

Since Azure is much more than SQL Server, there’s a lot of incidental knowledge needed to understand the chapter fully (e.g. subnet, routing table). This chapter does not go into any detail on these assumed topics.

I found this chapter a little confusing.

Conclusion 

This book aims to introduce SQL Server 2019, however I believe the title is misleading. Generally, this is not an introduction in the traditional sense of the word, where common topics (e.g. backup/restore) are introduced and explained to the novice to give you a mid-level understanding of the area. Instead, the topics are typically mid-level to advanced-level. Additionally, it could be argued several subtopics are at the fringes of SQL Server’s remit (e.g. Windows 2019).

Mostly, the book is easy to read, if you already have a wide, detailed, and up-to-date understanding of SQL Server. The more you know about the peripheral topics (e.g. Big Data) the easier it is to understand the book. Most chapters are self-contained, with very little cross-reference to other chapters. This is probably a consequence of having multiple authors. Perhaps the editor could have unified the book’s content.

Because SQL Server and its peripheral components are a huge subject, the book is necessarily selective in its topics, and perhaps this could have been highlighted. Some of the chapters assume too much prior knowledge, for example there’s an implied understanding that you’re aware of Spark, Hadoop, subnets etc. Sometimes, the subtopics are briefly discussioned, with the practical detail hidden away in website links – which may not be ideal.

Maybe the book needs to specify who it is aimed at? Overall, a book suitable for mid-level DBAs that want to expand their understanding of various selective topics, especially enterprise level features. The book’s title should be changed.

• For recommendations of SQL Server books see Pick Of The Shelf - SQL Server in our Programmer's Bookshelf section.

For more Book Watch just click.

Book Watch is I Programmer's listing of new books and is compiled using publishers' publicity material. It is not to be read as a review where we provide an independent assessment. Some, but by no means all, of the books in Book Watch are eventually reviewed.

To have new titles included in Book Watch contact  BookWatch@i-programmer.info

Follow @bookwatchiprog on Twitter or subscribe to I Programmer's Books RSS feed for each day's new addition to Book Watch and for new reviews.