Author: Stacia Varga et al
Publisher: Microsoft Press
Pages: 476 (215 as PDF)
Audience: DBAs, devs, architects
Reviewer: Ian Stirk
This free eBook aims to introduce you to the salient new and enhanced features in SQL Server 2016, how does it fare?
This book, which is subtitled Mission-Critical Applications, Deeper Insights, Hyperscale Cloud is aimed at DBAs, developers, architects, and technical-managers wanting to learn about the major enhancements in SQL Server 2016. It assumes some existing knowledge of SQL Server (perhaps at least one year of experience).
Below is a chapter-by-chapter exploration of the topics covered.
Chapter 1 Faster queries
The book opens with a look at features that enable faster queries. This primarily focuses on in-memory OLTP enhancements, which in many cases lifts the restrictions imposed in its initial release (e.g. you can now have CHECK constraints). Improved parallelism and multithreading capabilities are briefly discussed, as are the various T-SQL enhancements and improved data migration tools.
The chapter continues with a look at improvements made to in-memory analytics (cf. OLAP), and in particular some helpful ideas to reduce the potential contention between OLTP and analytics concurrent processing.
The chapter ends with a look at enhancements to Analysis Services, both multidimentional and tabular models. Whilst these changes are discussed functionally, they are for the most part side-effects of enhancements elsewhere (e.g. in-memory and DAX processing), and thus automatically gained without any specific configuration changes.
I’ve always felt the limitations included in the first version of in-memory OLTP were debilitating for serious SQL Server work, so the removal of many of these restrictions is welcomed.
This chapter is generally easy to read, with useful explanations, tips, example code, diagrams, and links for further information. These traits apply to the whole of the book.
Chapter 2 Better security
With the increasing occurrence of security breaches in the news, the critical importance of security becomes ever more obvious.
This chapter discusses three new security features, namely:
Always Encrypted (data encrypted when written and read)
Row-level security (automatically limits the rows users can see)
Dynamic data masking (obfuscate a portion of the viewed data)
In each case, usage is discussed with helpful examples and T-SQL code. Each of the security features provides a different level of security, which can be combined for improved security.
This chapter provides a useful overview of how to implement the new security features. Is there really any excuse not to implement these features on your systems?
Chapter 3 Higher Availability
High Availability (HA) is concerned with ensuring your systems are online as long as possible. Systems that are offline, even for short periods, can results in a loss of revenue and reputation.
The chapter discusses some enhancements to SQL Server’s already strong HA capabilities, for the most part these improvements are ‘minor’, although any one of them could be an important consideration for your adoption of SQL Server 2016.
New versions of Windows Server often provide new functionality that SQL Server can take advantage of (e.g. workgroup clusters, cloud witness).The second part of the chapter discusses the HA enhancements available with Windows Server 2016, which was in preview at the time the book was written (it became generally available in October 2016).
This chapter provides a helpful overview of the HA enhancements accompanying SQL Server 2016, together with additional features you can now use in Windows Server 2016.
Chapter 4 Improved database engine
Each new version of SQL Server provides new engine features, here we look at 3 of them, namely:
tempDB enhancements (reduced blocking and improved concurrency)
Query store (for tracking/improving query performance)
Stretch database (querying data archived in the cloud)
In each case, the enhancements are explained with step-by-step examples, and put into a wider context. It’s interesting to note the restrictions imposed on tables that can go into a stretch database - I presume, just like the initial release of in-memory OLTP, these will become less constrained with time.
This chapter provides a very good overview of some selective database engine features. There are of course a great many other enhancements (and I wished this chapter contained more of them).
Chapter 5 Broader data access
This chapter looks at some new features relating to data access, namely:
Temporal data (maintaining historical info)
JSON (a common application data format)
PolyBase (scalable integration of relational and Hadoop/Azure systems)
In each case, the purpose the feature is explained, illustrated with example code, diagrams, and tables. The code supplied, while basic, is sufficient to get you started with the feature.
Whilst the Big Data features of Polybase are interesting, I would suggest most shops interested in Big Data are going via the open source route (instead of proprietary offerings). Overall, the chapter provides a good overview of some enhanced data access features.
Chapter 6 More analytics
This chapter discusses analytic enhancements provided by the Tabular model of SQL Server Analysis Services (SSAS), and the integration of R services into SQL Server.
Tabular models tend to be easier to develop than multidimensional models, but in the past were less scalable, some of these limitations have now been removed, and these are discussed. These areas include: calculated tables, and various new DAX functions.
The section on R integration discusses its step-by-step installation and configuration. This is followed by a useful Getting Started section, and whilst it’s not extensive it will point you in the right direction.
Overall, this chapter provides a useful overview of the enhanced tabular model functionality, and R integration. I’m not sure why these two topics were placed in the same chapter, they are only tenuously linked.
Chapter 7 Better reporting
In the recent past, SQL Server Reporting Services (SSRS) has been bereft of enhancements, making it look stale and dated, however this edition includes several changes to bring it up-to-date, including:
New data visualizations (tree map and sunburst)
Mobile report development
Various user-facing modernisations
With the proliferation of mobile devices, mobile report development should prove a boon for report developers. There’s a useful section on the use of the various report elements (e.g. charts). SSRS has been waiting a long time for these improvements.
Chapter 8 Improved Azure SQL Database
This chapter provides a helpful overview of SQL Database, its evolution, pricing model, and its many advantages (e.g. scalability, easy admin, fast provisioning, automatic backups). Security is obviously a concern with off-premises databases, and a section here provides details of the various layers of security features available. The chapter ends with a look at features that provide scalability and elastic functionality.
The use of cloud databases is ever growing, this chapter provides a useful overview of the current state of Microsoft’s cloud database – a great improvement of its initial restrained offering. The cloud platform changes relatively rapidly, so be sure to check for the latest updates.
Chapter 9 Introducing Azure SQL Data Warehouse
Microsoft’s cloud data warehouse offering is discussed here, providing massive parallel processing to aid scalability. An overview of the various components (nodes, storage, services) and architecture is given. This is followed by a brief look at security and scalability, before looking at various methods of loading data (Azure Data Factory, PolyBase, SSIS). The chapter ends with a brief look at some cloud based analytics tools (Azure Machine Learning, Azure Stream analytics, Power BI) used to extract meaning from the data warehouse.
This book aims to introduce you to the salient new and enhanced features in SQL Server 2016, and succeeds easily. It is generally easy to read, with useful explanations, tips, example code, diagrams, and links for further information.
Whilst the book doesn’t cover all the changes, it does cover the major ones. Helpfully, deeper detail is provided in separate boxes, which can be bypassed without disrupting the main flow of the text. It should be noted that, in discussing the enhanced features, it doesn’t spend much time explaining the basics. I suspect the more you know about SQL Server already, the more useful this book will be.
I castigated the previous version of this book (Introducing Microsoft SQL Server 2014) because only half of it related to SQL Server, I have no such concerns with this edition.
It might have been useful to include a section discussing SQL Server changes in terms of some of the industry’s wider trends (e.g. Big Data, social media). As you might expect, there is a degree of marketing associated with this book, however it is not too onerous, just remember other solutions exist, especially with the cloud and Big Data.
Overall, if you want to know more about the new and enhanced features in SQL Server 2016, I can highly recommend this free book.