About Microsoft SQL Server 2019
From its humble beginnings in OS/2 with version 1.1, SQL Server has proved over and over that it is a database that data professionals love to use. The engine is reliable, and the T-SQL dialect has everything the developer needs to quickly write resilient, high-performing applications.
With every release, SQL Server has improved on performance, functions, reliability, and security. As the releases progressed, more features were added, and then entirely new capabilities—a job engine, a reporting server, business intelligence, and data mining systems. Groundbreaking technologies, such as in-memory databases and columnstore indexes, made SQL Server one of the most installed Relational Database Management Systems (RDBMSes) in the world.
In Spring of 2016, Microsoft announced that SQL Server would be made available on the Linux operating system—something unbelievable to many technical professionals. Addition of Platform Abstraction Layer (PAL) in SQL Server allowed it to run on Linux operating systems such as Ubuntu, Red Hat Enterprise Linux, and SUSE. It also added support for Linux containers, opening up amazing new possibilities for deployment and operation.
SQL Server 2019 represents not only an evolutionary release, but a revolutionary release. The promise of containers is completely realized with support for Kubernetes. The new SQL Server 2019 Big Data Clusters leverages Kubernetes as the deployment platform and adds the power of Spark and Apache Hadoop File System (HDFS). Additionally, SQL Server 2019 supports Data Virtualization and workloads with deployable applications running on-premises, in the cloud, and even in hybrid configurations. This allows SQL Server 2019 to modernize your data estate and applications with intelligence over any data—structured and unstructured.
Like the releases before it, SQL Server 2019 isn't limited to just the Windows platform. In addition to SQL Server 2019 on Windows, Linux, and containers, Microsoft has also announced a new product—Azure SQL Database Edge—which is a small-footprint SQL Server engine that runs on Edge devices and the ARM platform. This allows a consistent developer experience from the ground to the cloud and the edge. Add to this the choice of platform and the choice of programming languages such as T-SQL, Java, C/C++, Scala, Node.js, C#/VB.NET, Python, Ruby, and .NET Core. Need more? You can add your own languages as well.
SQL Server 2019 supports machine learning and extensibility with R, Python, Java, and Microsoft .NET. You're able to operationalize your machine learning models close to the data, and developers can leverage Java and other languages server-side.
But it's not just about new features. SQL Server maintains its high standards in performance and security. This release boasts industry-leading performance. It has the #1 OLTP performance benchmarks, and #1 DW performance on 1 TB, 3 TB, and 10 TB non-clustered DW workloads. It supports in-memory across all workloads and is the most consistent on-premises data platform—in both IaaS and PaaS. SQL Server 2019 has intelligent query processing features that improve the performance of mission-critical queries. They also support in-memory transactions and in-memory analytics for hybrid transactional and analytical processing.
Security is essential in any data storage and processing system, and SQL Server has prided itself on being the most secure database over the last eight years according to the National Institute of Standards and Technology's (NIST's) Comprehensive Vulnerability Database. SQL Server supports enterprise security and compliance with security features such as Transparent Data Encryption, Auditing, Row-Level Security, Dynamic Data Masking and Always Encrypted. SQL Server 2019 adds support for secure enclaves in Always Encrypted to enable rich computations on encrypted data.
SQL Server 2019 allows you to solve modern data challenges. Data virtualization with PolyBase allows you to use SQL Server 2019 as a data hub, directly querying data from data sources. These sources include Oracle, SAP HANA, MongoDB, Hadoop clusters, Cosmos DB, and SQL Server—all using T-SQL, and without separately installing client connection software. SQL Server 2019 also gives you insights and rich new reports, even for mobile BI with Power BI Report Server.
SQL Server 2019 improves reliability with several features in the High Availability and Disaster Recovery architecture and works with the built-in availability features in Kubernetes. It recovers faster with Accelerated Database Recovery.
This book covers these features, giving you a tour of each of them and diving in with real-world examples and sample code you can try out on your own. Put together by recognized experts and members of the team that wrote the software, we'll get you up to speed quickly and ready to start your own adventure with this latest release of the world's best data platform.
About the chapters
Chapter 1, Optimizing for performance and real-time insights, explains how SQL Server 2019 gets the most out of your hardware and empowers your analytics with features such as Hybrid Buffer Pool, and hybrid transactional and analytical processing.
Chapter 2, Enterprise Security and Compliance, covers the essential elements in SQL Server 2019 to ensure your operations are not compromised and that they stay compliant with industry regulations for data usage.
Chapter 3, High Availability and Disaster Recovery, covers SQL Server 2019's built-in methods to increase availability, minimize downtime for maintenance, and assist when outages occur.
Chapter 4, Hybrid Features—SQL Server and Microsoft Azure, looks at how SQL Server 2019 and Azure Storage work together to offer enterprise-ready, highly scalable, and flexible storage solutions at competitive prices.
Chapter 5, SQL Server 2019 on Linux, looks at how SQL Server 2019 is building on the Linux features in the 2017 release to offer even more functionality.
Chapter 6, SQL Server 2019 in Containers and Kubernetes, explains how virtualization features have evolved and how you can deploy SQL Server across Docker and Kubernetes.
Chapter 7, Data Virtualization, highlights SQL Server 2019's position as a modern enterprise data hub and how you can use features such as hybrid transactional and analytical processing to query across disparate systems.
Chapter 8, Machine Learning Services Extensibility Framework, explores machine learning, the components and architectures in SQL Server 2019 you can use to implement such services, and the process you follow for your solutions.
Chapter 9, SQL Server 2019 Big Data Clusters, builds on the concepts covered in the previous chapter to show how SQL Server 2019 can be leveraged to handle scaled-out datasets.
Chapter 10, Enhancing the Developer Experience, covers the tools to develop and manage SQL Server projects, including Visual Studio, SQL Server Management Studio, and—especially for cross-platform development—Visual Studio Code.
Chapter 11, Data Warehousing, highlights mission-critical security features such as Row-Level Security, Always Encrypted, and data masking.
Chapter 12, Analysis Services, looks at how SQL Server 2019 provides superior performance for decision support and business analytics workloads via multidimensional mode and tabular mode.
Chapter 13, Power BI Report Server, looks at new features that are included in the latest releases of Power BI Report Server, as well as key differences between Power BI Report Server and SSRS.
Chapter 14, Modernization to the Azure Cloud, finishes the book with a discussion of Azure's role regarding modernization and the data platform.
Conventions
Code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles are shown as follows: "A non-durable table is declared with DURABILITY=SCHEMA_Only."
A block of code is set as follows:
USE master; GO BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\SQLSERVER\MyServerCert.cer' WITH PRIVATE KEY (FILE = 'C:\SQLSERVER\certificate_Cert.pvk', ENCRYPTION BY PASSWORD = '!£$Strongpasswordherewelikesqlver#')
New terms and important words are shown like this: "Most Windows Server-based WSFCs (and SQL Server deployments) use Active Directory Domain Services (AD DS)."
Words that you see on the screen, for example, in menus or dialog boxes, appear in the text like this: "Go to Actions and select Get Shared Access Signature as shown."
System requirements
You will need the following hardware and software to complete the examples in this book:
- SQL Server 2019 Developer edition or higher with SQL Server Management Studio.
- A computer that has a 1.4 GHz or faster x64 processor (2 GHz recommended)
- 1 GB of memory (4 GB recommended)
- 6 GB of available hard-disk space
- Super VGA 800 x 600 or higher resolution display
- Internet connection to download software, as described in applicable chapters.
- For non-Windows platforms such as Linux or virtual machines, please refer to the release documentation.
Depending on your Windows configuration, you might require local administrator rights to install or configure SQL Server 2019 and related products.
Prerelease software
To help you become familiar with SQL Server 2019 as soon as possible after its release, we wrote this book by using examples that worked with SQL Server 2019 Release Candidate. Consequently, the final version might include new features, the user interface might change, or features that we discuss might change or disappear. Refer to What's New in SQL Server 2019 at https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sqlallproducts-allversions for the most up-to-date list of changes to the product.
AdventureWorks Database
Some demonstrations make a reference to the AdventureWorks database. This is a sample database published by Microsoft and used to demonstrated SQL Server 2019's new features. The database, along with download and setup instructions, can be found at https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15.
