SQL Server- New Features 2019
SQL Server 2019 introduces new and improved features which provide new data models, better performance, better managements and analyzing of data, and better developer experience. This includes new and improved data models like Big Data Clusters and graph-database, additional capabilities, and improvements for the SQL Server database engine with Intelligent Database, new Query Processing features, enhancements to spatial data types, UTF-8 support, new extensibility framework that allows developers to embed the language of their choice in T-SQL queries, improved security with new features, internals tools, and more.
SQL Server 2019 redefining the definition of what SQL Server is! It is extending well beyond just the relational database model, giving access to query and process data outside of the boundary of a traditional SQL Server instance. It introduces additional PolyBase connectors to SQL Server, Oracle, Teradata, and MongoDB. HDFS and Spark were built into the box so you can process and store unstructured data on a petabyte scale.
During this course we will explore the top new features and functionality in SQL Server 2019 and the Azure database (from 2019 to 2021). We will dive into implementation of a few key features and we will discuss practical scenario, tips, and tricks.
Who Should Attend
- Developers and Database Managers that use Azure Database or SQL Server databases.
- Database managers that need to monitor SQL Servers, improve performance, and enable better security.
- Data Platform Architects that want to learn about new available features for better designing solutions.
- Familiarity with using SQL Server
- Data virtualization
- External Data source with PolyBase
PolyBase which first introduced in SQL Server 2016 allows accesses external data across multiple different data sources via the T-SQL language. SQL Server 2019 introduces additional connectors, including SQL Server, Oracle, Teradata, and MongoDB.
- Big Data Clusters – a review and a full deployment demo.
SQL Server Big Data Clusters allow to deploy scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes. This enables us to read, write, and process big data using Transact-SQL or Spark and easily combine and analyze high-value relational data with high-volume big data.
- External Data source with PolyBase
- Security enhancement
- SQL Server 2019 continues the road to secure the data using new and improved features like Always Encrypted with Secure Enclaves, Integrated certificate management into the SQL Server Configuration Manage. SQL Server 2019 introduces the ability to create safer containers by starting the SQL Server process as a non-root user by default. It provides improvement to Data Discovery & Classification, accessing the classification metadata directly using the new DMV sys.sensitivity_classifications, and review all classified columns with their corresponding classifications using sys.sensitivity_classifications.
- Enhance Performance and monitoring executions
- SQL Server 2019 provides new and improved Dynamic Management Views (DMV) to get information about executed queries, queries that are blocked or in wait status, and the information about the binary data in the page headers that stored on the disk (formally we could get this information using undocumented tools like DBCC PAGE). It provides new database scoped configuration, new functions, and new extended event.
- SQL Server In-Memory Database technologies leverage performance using persistent Memory and Hybrid buffer pool, Memory-optimized TempDB metadata, and In-Memory OLTP support for Database Snapshots.
- Improved Server Engine predictable performance using new features:
- Forcing fast forward and static cursors
- Resource governance
- Reduced recompilations for workloads
- Indirect checkpoint scalability
- Concurrent PFS updates
- Scheduler worker migration
- New and improved Intelligent-query-processing features which improve performance with minimal implementation effort to adopt – Review and demo
Improve performance using new features:
- APPROX_COUNT_DISTINCT (new function)
- Batch Mode on Rowstore
- Memory Grant Feedback (Row Mode)
- Scalar UDF Inlining
- Table Variable Deferred Compilation
- Accelerated database recovery, reduce the time to recover after a restart or a long-running transaction rollback.
- Suspend and resume Transparent Data Encryption (TDE) scan: To enable TDE on a database, SQL Server scan reads each page from the data files into the buffer pool and then writes the encrypted pages back out to disk. This process can consume a lot of resources and by suspend the process we can pause the scan while the workload on the system is heavy.
- Developer experience – deep dive into the theory, demo, and scenarios
SQL Server 2019 added new graph-database features, UTF-8 support, and a new extensibility framework that allows developers to execute external code in languages of their choice.