Workshop: Modernizing Your Data Estate With SQL Ground-to-Cloud

A Microsoft workshop from the SQL Server team

03 - SQL Server Improvements

In this workshop you'll cover how to systematically create a modern data estate using SQL Server in on-premises, in-cloud and hybrid solutions.

In each module you'll get more references, which you should follow up on to learn more. Also watch for links within the text - click on each one to explore that topic.

(Make sure you check out the Pre-Requisites page before you start. You'll need all of the items loaded there before you can proceed with the workshop.)

Using the processes, tools and procedures from the Auditing Module, the WideWorldImporters company has completed an initial audit of their data estate. They found multiple sources of data in their organization, including text files, spreadsheets, and Relational Database Management Systems (RDBMS) including SQL Server 2012 Instances along with some Instances of SQL Server 2017 for their primary Line of Business (LOB) databases.

WideWorldImporters needs to upgrade the software that is out of support and also evaluate which versions and editions will solve their problems. They have settled on evaluating the delta between SQL Server 2017 and the latest version of the on-premises SQL Server product, and they have divided their evaluation into various categories that they are interested in:

3.1 - Deployment Options
3.2 - Manageability
3.3 - Availability
3.4 - Performance
3.5 - Security
3.6 - Development
3.7 - Analytics

3.1 Deployment Improvements

In the latest version, you can deploy SQL Server to the Microsoft Windows operating systems as well as various distributions of the Linux operating system. Newer deployment scenarios also include deploying SQL Server on Containers, Kubernetes, and new options in the Microsoft Azure platform such as Azure SQL Edge.

WideWorld Importers has decided to evaluate the on-premises latest version of SQL Server separately from the Microsoft Azure platform deployment options. They also want to evaluate the on-premises to cloud and cloud to on-premises interactions separately.

SQL Server on Windows

The latest version of SQL Server is supported on all current Microsoft Windows desktop and server operating systems, including Windows 11 (Developer and Express Editions) and Windows 2022 Server. You can learn more about each version of the Microsoft Windows operating system and the SQL Server version it supports at this reference.

Your selection of features and components should be based on your application needs. The different Editions of SQL Server have unique features, performance, runtime, and cost implications. You can learn more about the Editions of SQL Server and the features supported in each at this reference.

All features in each Edition for the latest version of SQL Server are supported on the Microsoft Windows operating system, including:

Hybrid buffer pool with direct write, which reduces the number of memcpy commands that need to be performed on modified data or index pages residing on PMEM devices
Integrated acceleration & offloading acceleration technologies from partners such as Intel to provide extended capabilities, such as Intel® QuickAssist Technology (QAT) which provides backup compression and hardware offloading
Leverages new hardware capabilities, including the Advanced Vector Extension (AVX) 512 extension to improve batch mode operations

SQL Server on Virtual Machines

Bare-metal installations of an operating system such as Windows are deployed on hardware using an operating system Kernel, and additional software to bring all of the hardware into a set of calls to the "Big Four": CPU, Memory, Storage, and Networking.

One abstraction layer above installing software directly on hardware is using a Hypervisor. In essence, this layer uses the base operating system to emulate hardware. You install an operating system (called a Guest OS) on the Hypervisor (called the Host), and the Guest OS acts as if it is on bare-metal.

In this abstraction level, you have full control (and responsibility) for the entire operating system, but not the hardware. This isolates all process space and provides an entire "Virtual Machine" to applications. For scale-out systems, a Virtual Machine allows for a distribution and control of complete computer environments using only software.

You can read the details of Microsoft's Hypervisor at this reference.

The latest version of SQL Server supports all of the features associated with the applicable Edition for both Windows and Linux in a Virtualized environment, with the considerations described at this reference.

SQL Server on Linux

In the latest version, SQL Server runs on Red Hat Enterprise Linux, SUSE Linux Enterprise Server, and Ubuntu Linux. WideWorld Importers is currently running Red Hat Linux at one of their processing plants, and is interested in some of the workloads that might be useful on that platform. As they test, their primary concern is the performance aspect of SQL Server in Linux, and they find that performance is on-par with the Windows deployments due to the architecture of the Platform Abstraction Layer (PAL) implemented to run the SQL Server Engine code on Linux:

The latest version of SQL Server on Linux supports the following improvements:

Replication
Support for the Microsoft Distributed Transaction Coordinator (MSDTC)
OpenLDAP support for third-party AD providers
Machine Learning Services on Linux
TempDB improvements, such as multiple TempDB data files, similar to the default SQL Server installation on Windows
PolyBase
PolyBase type mapping
Change Data Capture (CDC) support

There are specific versions and support considerations for each distribution of Linux. A list of these requirements is available at this reference, and a list of Editions and supported features for SQL Server on Linux is at this reference.

You can learn more about the SQL Server on Linux architecture at this reference.

SQL Server in Containers

The introduction of SQL Server running on Linux allows for the next level of hardware abstraction, called a Container. There are various types of Container technologies, in this workshop, you will focus on the Docker container format, as implemented in Windows using the Moby framework.

A Container is provided by the Container Runtime (Such as containerd) runtime engine, which sits above the operating system (Windows or Linux). In this abstraction, you do not control the hardware or the operating system. The Container has a specific protected memory space, and can contain binaries such as Python, R, SQL Server, or other binaries. A Container with all its binaries is called an Image. You create a container from a file.

This abstraction holds everything for an application to isolate it from other running processes. It is also completely portable - you can create an image on one system, and another system can run it so long as the same Container Runtime (Such as Docker) is installed. Containers also start very quickly, are easy to create (called Composing) using a simple text file with instructions of what to install (called a manifest or Dockerfile) on the image. The instructions pull the base image, and then any binaries you want to install. Several pre-built Containers are already available, and SQL Server is one of these. You can read more about installing SQL Server on Container Runtimes (Such as Docker) at this reference.

You can have several Containers running at any one time, based on the amount of hardware resources where you run it. For scale-out systems, a Container allows for distribution and control of complete applications using only declarative commands.

SQL Server on Kubernetes

For Big Data systems, having lots of Containers is very advantageous to segment purpose and performance profiles. However, dealing with many Container Images, allowing persisted storage, and interconnecting them for network and internetwork communications is a complex task. One such Container Orchestration tool is Kubernetes, an open source Container orchestrator, which can scale Container deployments according to need. The following table defines some important Container Orchestration Tools (Such as Kubernetes or OpenShift) terminology:

Component	Used for
Cluster	Container Orchestration (Such as Kubernetes or OpenShift) cluster is a set of machines, known as nodes. One node controls the cluster and is designated the master node; the remaining nodes are worker nodes. The Container Orchestration master is responsible for distributing work between the workers, and for monitoring the health of the cluster.
Node	A node runs containerized applications. It can be either a physical machine or a virtual machine. A Cluster can contain a mixture of physical machine and virtual machines as Nodes.
Pod	A Pod is the atomic deployment unit of a Cluster. A pod is a logical group of one or more Containers and associated resources needed to run an application. Each Pod runs on a Node; a Node can run one or more Pods. The Cluster master automatically assigns Pods to Nodes in the Cluster.

You can learn much more about Container Orchestration systems here. You can implement a Kubernetes environment using the Azure Kubernetes Service (AKS), and you can find a set of tutorials for you to learn more at this reference.

You can practice with Kubernetes using online emulator to work with the minikube platform.

You can deploy the latest version of SQL Server deployments on Kubernetes. In this case, the Container Orchestration system (Such as Kubernetes or OpenShift) is responsible for the state of the BDC; it is responsible for building and configuring the Nodes, assigns Pods to Nodes, creates and manages the Persistent Volumes (durable storage), and manages the operation of the Cluster.

You can learn more about deploying SQL Server to Kubernetes at this reference. SQL Server containers can also be deployed to Kubernetes using Helm charts. To understand how to deploy a container to Kubernetes see this Microsoft Learn article: https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-containers-deploy-helm-charts-kubernetes?view=sql-server-ver16

Activity: Review the process for running SQL Server in a Container

In this Activity you will review the process for pulling the latest version of a SQL Server container and running it on the Docker platform, and connecting to it with a query.

If you have a Linux or Windows operating system with Docker installed and running, you can implement this tutorial, otherwise watch the instructor as they perform the pull.

Steps

Open this link and review the steps you see there. Bookmark for later reference.
Optional: If time permits, you can install the Docker platform on your test system and complete the steps to deploy SQL Server on Linux and connect with a test to that environment.

3.2 Manageability

For the administration team for the Data Estate, manageability includes everything from the interface to the system, to being able to deploy and configure it. The latest version of SQL Server includes the following improvements:

An integrated setup experience for the Azure extension for SQL Server which installs the Azure extension for SQL Server at setup
You can now use the SQL Server Configuration Manager to manage an Azure extension for SQL Server service, allowing you to create Azure Arc-enabled SQL Server instances, and for other Azure connected features
There are new upgraded max server memory calculations
There are several improvements to address persistent version store (PVS) storage and improve overall scalability, including a persistent version store cleaner thread per database instead of per instance and the memory footprint for PVS page tracker has been improved. There are also a number of ADR efficiency improvements, such as concurrency improvements that help the cleanup process to work more efficiently. ADR cleans pages that couldn't previously be cleaned due to locking
A new shrink database option has been added: WAIT_AT_LOW_PRIORITY
XML compression is now available
There is a new improvement for asynchronous auto update statistics concurrency

Tools

There are several tools you can use to connect to, query, and manage a SQL Server Instance. The two broad groups are Graphical User Interfaces (GUI) and Command-Line or Scripting Interfaces.

New improvements include:

Azure Data Studio includes connection and management support for the latest version of SQL Server
SQL Server Management Studio connects and manages the latest version of SQL Server, and includes several other improvements
SqlPackage.exe provides support for the latest version of SQL Server
VS Code now supports the latest version of SQL Server

This Module focuses on the use and improvements in the SQL Server Management Studio (SSMS) and Command-Line utilities. The Azure SQL Module will focus more on the Azure Data Studio (ADS) tool, although both SSMS and ADS can be used to connect to and manage either platform.

SQL Server Management Studio

SQL Server Management Studio (SSMS) is a graphical interface for the Microsoft Windows operating system used to manage, monitor and program SQL Server Instances. The latest version of this tool supports connecting to and managing the latest versions of SQL Server for the following activities: - Connect to SQL Server and see object status - Creation of databases and database objects - Configuration of connection options - Scripting database options - Creation of SQL Server Agent Jobs and Schedules - Creation of Maintenance plans, backup and restore operations - Multiple out-of-the-box reports and the ability to create custom reports - Multiple "wizards" for tasks such as security compliance and risk reports - Multiple graphical tools to view and report performance and query plans and the query store feature - View the AlwaysOn AG Dashboard - View SQL Server Log Files - Create Extended Events

New and enhanced features in SQL Server Management Studio include:

Support for Azure AD authentication, including Multi-Factor Authentication (MFA)
Azure Data Studio installation integration Installation of SSMS installs Azure Data Studio
The Object Explorer Dropped Columns folder now exists under the Columns folder for Ledger tables, which have been altered to remove one or more columns
Scripting Compatibility level now defaults to 160 when scripting
Added Showplan support for Hyperscale Optimized Query Processing
Analysis Services Connection to Analysis Services is now available
Added missing options in SqlParser for CREATE USER and CREATE LOGIN.
The “Schedule…” menu item in SQL Server Integration Services is now visible in the Azure SSIS Catalog
Added the ability to explicitly configure an attestation protocol in the "Connect To Server" dialog when using Always Encrypted with secure enclaves (column encryption)
Client Driver changed to Microsoft.Data.SqlClient
Added support for Contained Always On Availability Groups
Improvements to Data Classification user interface
Added support for Ledger feature Database ledger
Improved checks for open connections.
Updated Query Tuning Assistant user interface for improved accessibility
The showplan XML schema has been updated

You can find a complete overview of the SSMS product at this reference.

Command-Line Utilities

The SQLCMD utility allows you to connect to SQL Instances via a command-line utility in Windows or in Linux. It allows you to send commands to the Instance in an immediate query/response mode, or send in scripts, and will also create output streams in an automated way.

Improvements to SQLCMD include:

Support for Azure AD authentication, including Multi-Factor Authentication (MFA)
Support for SQL Database, Azure Synapse Analytics, and Always Encrypted features

You can learn more about the SQLCMD utility at this reference.

Activity: SQL Server Management Studio Tips

In this Activity you will review a few tips and tricks you may not have been familiar with in SQL Server Management Studio. The instructor may also reference additional interesting tasks and features you might not be familiar with.

Steps

Open this link and perform the steps you see there. Bookmark for later reference.

3.3 Availability

One of the important considerations for WideWorld Importers is ensuring Business Continuity. As part of their strategy, the Data Estate is an important component. The general area for Business Continuity in Data is High Availability, which in turn contains important concepts such as Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO). A few key terms are important to note as we discuss the improvements in this area of the latest version of SQL Server:

High Availability (HA): The ability for your systems to recover from localized events in an automated way
Disaster Recovery (DR): The process of recovering from a disaster of some sort. A natural or man-made disaster has occurred and manual intervention may be required. While some things can be automated, other items may require manual intervention
Business Continuity: The planning process for how businesses and their IT systems will react in the event of an outage. This planning will typically be for DR, but it could also include HA planning

There are multiple capabilities in SQL Server that serve as part of an overall Availability strategy, including several that have new improvements in the latest version:

Database Backups
- Intel QAT backup hardware offloading support
- T-SQL Snapshot Backup, adding Transact-SQL support for freezing and thawing I/O without requiring a VDI client
- backupset (Transact-SQL) improvements to add additional columns
- Backup and restore to S3-compatible object storage
Database and Database article Replication
- "Last Writer Wins" conflict detection
Database Log Shipping
Failover Clustering
- Simplified Failover Clustering setup for Virtual Machines
Always-On Availability Groups - Improvements in the latest version of SQL Server include:
- Up to five synchronous replicas starting in SQL Server 2019 (15.x) increases the maximum number of synchronous replicas to 5, up from 3 in SQL Server 2017 (14.x). You can configure this group of five replicas to have automatic failover within the group. There is one primary replica, plus four synchronous secondary replicas
- Secondary-to-primary replica connection redirection allows client application connections to be directed to the primary replica regardless of the target server specified in the connection string
- Create an Always On Contained Availability Group that manages its own metadata objects (users, logins, permissions, SQL Agent jobs etc.) at the availability group level in addition to the instance level, and includes specialized contained system databases within the availability group

The latest version of SQL Server also has the ability to participate in Availability to and from the Microsoft Azure Platform. These improvements will be detailed in the last Module of this Workshop.

Activity: Examine the new Information in the backupset system table

The backupset table in the msdb system database contains a row for each backup set. A backup set contains the backup from a single, successful backup operation. You can use this information to check for your backup status, time to run, and much more. These checks should be integrated into your overall Disaster Recovery strategy.

Steps

Back Up your WideWorld Importers database to disk
Open this link and perform the steps you see there to review the new information for backups. Bookmark for later reference.

3.4 Performance

The latest version of SQL Server includes multiple features to enable higher performance, some without even altering your code. These improvements include:

Online clustered columnstore index build and rebuild
Resumable online rowstore index build See Perform Index Operations Online
Suspend and resume initial scan for Transparent Data Encryption (TDE)
In-Memory Database - leverages memory to store more data in memory to unlock a new level of scalability across all your database workloads. Improvements in the latest version of SQL Server include:
- In-memory online transaction processing (for OLTP)
- Hybrid buffer pool, where database pages sitting on database files placed on a persistent memory (PMEM) device will be directly accessed when required
- Memory-optimized TempDB metadata allows effectively removes tempDB metadata bottlenecks and unlocks a new level of scalability for TempDB heavy workloads. The system tables involved in managing temporary table metadata can be moved into latch-free non-durable memory-optimized tables
- In-Memory OLTP support for Database Snapshots which includes memory-optimized filegroups

You can find more information on features at this reference.

Two "families" of performance improvement in the newest version of SQL Server are important to call out:

Intelligent Performance
Intelligent Query Processing

Intelligent Performance

This group of improvements help overcome known resource bottlenecks and provide options for configuring your database server to provide predictable performance across all your workloads.

New improvements include:

OPTIMIZE_FOR_SEQUENTIAL_KEY which turns on an optimization within the SQL Server Database Engine that helps improve throughput for high-concurrency inserts into the index. This option is intended for indexes that are prone to last-page insert contention, which is typically seen with indexes that have a sequential key, such as an identity column, sequence, or date/time column
Forcing fast forward and static cursors, which provides Query Store plan forcing support for fast forward and static cursors. See Plan forcing support for fast forward and static cursors
Resource governance has a new configurable value for the REQUEST_MAX_MEMORY_GRANT_PERCENT option of CREATE WORKLOAD GROUP and ALTER WORKLOAD GROUP has been changed from an integer to a float data type, to allow more granular control of memory limits. See ALTER WORKLOAD GROUP and CREATE WORKLOAD GROUP
Reduced recompilations for workloads which improves performance when using temporary tables across multiple scopes by reducing unnecessary recompilations
Concurrent Page Free Space (PFS) pages, which are special pages within a database file that SQL Server uses to help locate free space when it allocates space for an object
Scheduler worker migration allows an idle scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage in situations where long-running tasks happen to be assigned to the same scheduler

Intelligent Query Processing

Of special importance in the newest performance improvements is a family of features in the SQL Server Database Engine called Intelligent Query Processing (IQP). Introduced in the latest versions of SQL Server, this family of features constantly adapts to queries in the safest way to ensure that your workloads get the highest possible performance, all without adding indexes or changing code. Some of the latest improvements in SQL Server in the IQP include:

Adaptive Joins (Batch Mode) dynamically select a join type during runtime based on actual input rows
Approximate Count Distinct provides approximate COUNT DISTINCT for big data scenarios with the benefit of high performance and a low memory footprint
Batch Mode on Rowstore provides batch mode for CPU-bound relational DW workloads without requiring columnstore indexes
Cardinality estimation (CE) feedback automatically adjusts cardinality estimates for repeating queries to optimize workloads where inefficient CE assumptions cause poor query performance. CE feedback will identify and use a model assumption that better fits a given query and data distribution to improve query execution plan quality
Degrees of Parallelism (DOP) feedback automatically adjusts degree of parallelism for repeating queries to optimize for workloads where inefficient parallelism can cause performance issues
Interleaved Execution uses the actual cardinality of the multi-statement table valued function encountered on first compilation instead of a fixed guess
Memory grant feedback (Batch Mode) is used if a batch mode query has operations that spill to disk, adding more memory for consecutive executions. If a query wastes > 50% of the memory allocated to it, it reduces the memory grant size for consecutive executions
Memory grant feedback (Row Mode) is used if row mode query has operations that spill to disk, add more memory for consecutive executions. If a query wastes > 50% of the memory allocated to it, it reduces the memory grant size for consecutive executions.
Memory grant feedback (Percentile) addresses existing limitations of memory grant feedback in a non-intrusive way by incorporating past query execution to refine feedback
Memory Grant, CE, and DOP feedback persistence provides new functionality to persist memory grant feedback. CE and DOP feedback is always persisted. Requires Query Store to be enabled for the database and in READ_WRITE mode
Optimized plan forcing reduces compilation overhead for repeating forced queries. For more information, see Optimized plan forcing with Query Store
Scalar UDF Inlining transforms Scalar UDFs into equivalent relational expressions that are "inlined" into the calling query, often resulting in significant performance gains
Parameter Sensitivity Plan Optimization addresses the scenario where a single cached plan for a parameterized query is not optimal for all possible incoming parameter values, for example non-uniform data distributions
Table Variable Deferred Compilation uses the actual cardinality of the table variable encountered on first compilation instead of a fixed guess

The following graphic details the family of IQP features and when they were first introduced for SQL Server. All IQP features are available in Azure SQL Managed Instance and Azure SQL Database (Some features depend on the database's compatibility level):

The Query Data Store

The Query Store feature provides you with insight on query plan choice and performance for SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. The Query Store simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes. Query Store automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows so you can see database usage patterns and understand when query plan changes. the Query Store is used by all the Intelligent Database and Intelligent Query Performance improvements. Improvements in the latest version of SQL Server for Query Store include:

Query Store on secondary replicas enables the same Query Store functionality on secondary replica workloads that is available for primary replicas
Query Store hints leverage the Query Store to provide a method to shape query plans without changing application code. Previously only available on Azure SQL Database and Azure SQL Managed Instance, Query Store hints are now available in the latest version of on-premises SQL Server
Query Store is enabled by default for newly created databases int he latest version of SQL Server
Every query recompilation SQL Server compares the runtime stats of the query using existing feedback to the runtime stats of the previous compilation with the existing feedback

You can learn more about the Query Data Store at this reference.

Activity: Learn and use Query Data Store

In this Activity, you will learn more about the Query Data Store, and find a resource to implement it on your test system.

Steps

Open this resource and move to Unit 5 of the "Explore query performance optimization" Learn module to learn more about the Query Data Store.
Open this reference and run the queries you see there. Bookmark for later reference.

3.5 Security

Over the past ten years, SQL Server has had fewer vulnerabilities than any other major database platform . Building on this security posture, several new improvements and features have been added to increase security for the platform.

Data Classification and Access Control Enhancements

Data Discovery and Classification is a new set of commands and metadata that allow you to "tag" columns of data in your databases with standard or configurable labels, from Transact-SQL statements or by using the graphical interface in SQL Server Management Studio (SSMS).

You can learn more about Data Classification and Discovery at this reference.

There are a new set of Server-level Roles in the newest version of SQL Server to enable least privileged access for administrative tasks that apply to the whole SQL Server Instance. You can learn more about these roles at this reference.

Dynamic Data Masking, Row-Level Security and Always Encrypted Improvements

Fine-grained encryption and data-level masking are important tools for the administrators and developers of data applications to completely secure data access, all while reducing the coding load. Several new improvements provide these capabilities.

Dynamic Data Masking Dynamic data masking (DDM) helps prevent unauthorized access to sensitive data by enabling you to specify how much sensitive data to reveal, all with minimal impact on the application layer. DDM can be configured on designated database fields to hide sensitive data in the result sets of queries.

With DDM, the data in the database isn't changed. DDM is easy to use with existing applications, since masking rules are applied in the query results.

You can learn more about Dynamic Data Masking and find a tutorial on implementing it at this reference.

Row-Level Security Row-Level Security enables you to use Role membership or execution context to control access to rows in a database table. Row-Level Security (RLS) simplifies the design and coding of security in your application, since the access restriction logic is located in the database tier rather than away from the data in another application tier.

You can learn more about Row-Level Security and find a tutorial on implementing it at this reference.

Always Encrypted with Secure Enclaves the latest version of SQL Server expands on the Always Encrypted features with in-place encryption and rich computations by enabling computations on plaintext data inside a server-side secure enclave. In-place encryption improves the performance and the reliability of cryptographic operations (encrypting columns, rotating columns, encryption keys, and so on), because it avoids moving data out of the database.

This improvement also supports rich computations (pattern matching and comparison operations) which unlocks Always Encrypted to a much broader set of scenarios and applications that demand sensitive data protection, while also requiring richer functionality in Transact-SQL queries.

Auditing Enhancements

Auditing in SQL Server is now improved to include a new field called data_sensitivity_information in the audit log record, which contains the sensitivity classifications (labels) of the actual data that was returned by the query, which leverages the Data Classification features shown earlier.

You can learn more about Auditing in SQL Server at this reference.

Ledger

Establishing trust around the integrity of data stored in database systems has been a longstanding problem for all organizations that manage financial, medical, or other sensitive data. The ledger feature provides tamper-evidence capabilities in your database. You can cryptographically attest to other parties, such as auditors or other business parties, that your data hasn't been tampered with.

The latest version of SQL Server includes a new feature called Ledger which, once enabled on a table, adds a hashed value (a Block) at the end of each row of data, and a computed hashed value from those Blocks (representing a Chain of Blocks) to uniquely identify the data in the database. A Digest is created for those values, which can be in separate, protected storage, to compare the state of the internal blockchain to an immutable copy, which can prove whether data has been tampered with.

Activity: Implement a Ledger on your Test Database

In this Activity, you will implement Ledger for SQL Server on your test system. You can use any sample database, or make one specifically for this exercise.

Steps

- Open this resource and complete the steps you see there.

3.6 Development

Developer enhancements include not only new Transact-SQL (T-SQL) changes including new time series, JSON improvements, aggregate changes, and Bit manipulation functions, but also data types and changes to features such as using SQL Server as a Graph Database and new Unicode feature support. A few of the improvements in the latest version of SQL Server are:

Graph enhancements
- Edge constraint cascade delete actions allow you to define cascaded delete actions on an edge constraint in a graph database
- A new graph function - SHORTEST_PATH - exists inside MATCH to find the shortest path between any two nodes in a graph or to perform arbitrary length traversals
- Graph tables now support table and index partitioning
- You can now use derived tables or view aliases in a graph match query
Unicode support improvements include support for UTF-8 character encoding for import and export encoding, and as database-level or column-level collation for string data. Support includes PolyBase external tables, and Always Encrypted (when not used with Enclaves)
Language extensions
- A new Java language SDK that simplifies the development of Java programs that can be run from SQL Server
- The Microsoft Extensibility SDK for Java for Microsoft SQL Server is now open source and available on GitHub
- Support for new Java data types
- SQL Server now includes Azul Systems Zulu Embedded for Java support throughout the product
- SQL Server Language Extensions allows you to execute external code with the extensibility framework
- A new Data Definition Language (DDL), CREATE EXTERNAL LANGUAGE, registers external languages, such as Java, in SQL Server
New spatial reference identifiers (SRIDs), using the Australian GDA2020 which provides a more robust and accurate datum that's more closely aligned with global positioning systems. The new SRIDs are: 7843 for geographic 2D, 7844 for geographic 3D
Error messages include verbose truncation warnings - the data truncation error message defaults to include table and column names, and the truncated value

You can learn more details about each individual language improvement at this reference.

SQL Server Machine Learning Services

SQL Server Machine Learning Services provides a mechanism to create, train, and deploy Machine Learning models that stay in the database where the source data lives. It can use the R, Java, and Python languages and libraries, and includes the ability to add other languages if desired. Improvements to this feature in the latest version of SQL Server include:

You can process external scripts per partition of your data by using the new parameters added to sp_execute_external_script. This functionality supports training many small models (one model per partition of data) instead of one large model
You can configure high availability for Machine Learning Services on a Windows Server Failover Cluster

You can learn more about Machine Learning Services in SQL Server at this reference, which also contains multiple tutorials.

Activity: Work with a Graph Database

One of the new enhancements in the latest version of SQL Server is in the SQL Graph feature. Many data professionals have not worked with a Graph database before, and are not aware of how powerful it can be. In this Activity you will create a simple Graph database, and run a simple MATCH query on it.

Steps

Open the following reference and follow all the steps you see there.

3.7 Analytics

SQL Server contains not only Transact-SQL statements that facilitate data analysis, but also the integration of R, Python, and Java with the Extensibility Framework for extended data analysis. There are also various services included with SQL Server that provide advanced analytic capabilities, and these have had significant improvements in the latest version.

SQL Server Analysis Services
- Calculation groups in tabular models can significantly reduce the number of redundant measures by grouping common measure expressions as calculation items
- Query interleaving is a new tabular mode system configuration that can improve user query response times in high-concurrency scenarios
- Many-to-many relationships in tabular models allows many-to-many relationships between tables where both columns are non-unique
- New Property settings for resource governance includes new memory settings: Memory\QueryMemoryLimit, DbpropMsmdRequestMemoryLimit, and OLAP\Query\RowsetSerializationLimit for resource governance
- Governance setting for Power BI cache refreshes introduces the ClientCacheRefreshPolicy property, which overrides caching dashboard tile data and report data for initial load of Live connect reports by the Power BI service
- Online attach Online attach can be used for synchronization of read-only replicas in on-premises query scale-out environments
- Azure Synapse Link for SQL provides near real time analytics over operational data in SQL Server with a seamless integration between operational stores in SQL Server and Azure Synapse Analytics dedicated SQL pools, Azure Synapse Link for SQL enables you to run analytics, business intelligence and machine learning scenarios on your operational data with minimum impact on source databases with a new change feed technology
- A new object storage integration is now added to the data platform, enabling you to integrate SQL Server with S3-compatible object storage, in addition to Azure Storage. The first is backup to URL and the second is Data Lake Virtualization. Data Lake Virtualization integrates PolyBase with S3-compatible object storage, adds support for to querying parquet files with T-SQL
- Data Virtualization allows you to query different types of data on different types of data sources from SQL Server
- SuperDAX for multidimensional models (SuperDAXMD) allows DAX-based clients to use SuperDAX functions and query patterns against multidimensional models, providing improved performance when querying model data. SuperDAX first introduced DAX query optimizations for tabular models with Power BI and SQL Server Analysis Services 2016. SuperDAXMD now brings these improvements to multidimensional models
- Horizontal fusion is a new query execution plan optimization aimed at reducing the number of data source queries required to generate and return results. Multiple smaller data source queries are fused together into a larger data source query. Fewer data source queries mean fewer round trips and fewer expensive scans over large data sources, which results in sizeable DAX performance gains and reduced processing demand at the data source. DAX queries run faster with Horizontal Fusion, especially in DirectQuery mode. In addition, scalability also increases

SQL Server Integration Services
- Flexible file task allows you to perform file operations on Local File System, Azure Blob Storage, and Azure Data Lake Storage Gen2
- Flexible file source and destination adds read and write data for Azure Blob Storage, and Azure Data Lake Storage Gen2

SQL Server Master Data Services
- Support for Azure SQL Managed Instance databases now allows you to host Master Data Services on Azure SQL Managed Instance
- HTML controls replace all former Silverlight components. Silverlight dependency removed.

SQL Server Reporting Services
- Azure SQL Managed Instance support means that you can now host a database catalog used for SQL Server Reporting Services (SSRS) in an Azure SQL Managed Instance (MI) that's hosted either in a VM or in your data center
- Power BI Premium dataset support allows you to connect to Power BI datasets using either Microsoft Report Builder or SQL Server Data Tools (SSDT). Then you can publish those reports to SSRS using SQL Server Analysis Services connectivity
- AltText (alternative text) support for report elements, so that when authoring reports, you can use tooltips to specify text for each element on the report. Screen reader technology identifies these tooltips properly
- Azure Active Directory Application Proxy support means that you no longer need to manage your own web application proxy in order to allow secure access via the web or mobile apps
- New Custom headers sets header values for all URLs matching the specified regex pattern. Users can update the custom header value with valid XML to set header values for selected request URLs. Admins can add any number of headers in the XML
- SQL Server now supports Transparent Database Encryption for the SSRS catalog database
- The newly released version of Report Builder is fully compatible with the 2016, 2017, 2019 and versions of Reporting Services. It's also compatible with all released and supported versions of Power BI Report Server

For Further Study

Introduction to SQL Server on Linux
Explore query performance optimization
Enable and manage Microsoft Defender for Cloud
Ledger overview
Business continuity and database recovery - SQL Server
Understanding the Big Data Landscape
Linux for the Windows Admin
Container Runtimes (Such as Docker) Guide
Video introduction to Kubernetes
Complete course on the Azure Kubernetes Service (AKS)
KubeAcademy
Working with Spark
Full tutorial on Jupyter Notebooks

Next, continue on to Module 04 - SQL Server on the Microsoft Azure Platform

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!