Introduction to SQL Server 2021
Microsoft, owing to the new demands and other technological advancements in the software industry, has released the upgraded version of SQL Server 2021, which is SQL server 2021. This was announced and introduced in the Ignite 2018 event, on September 24th. However, this was just a preview edition. A more technically advanced and detailed picture of SQL Server 2021, was submitted only in the PASS Summit, which happened later in November, in the same year.
The newer version of the SQL server has come up with higher-end integration of modules such as Azure SQL Database, Apache Kafka Support on Azure Event Hubs, Azure SQL Data Warehouse and Hadoop Distributed File System (HDFS), all packed together to provide a single, integrated solution
Microsoft SQL Server 2021 is built with an aim to take the Artificial Intelligence (AI), a step forward, by integrating Big Data with the Database services.
There are many advancements made to SQL server 2021 to release the new version, which is SQL Server 2021 making it more efficient and stable. These new features are discussed below in detail.
Big data clusters are new additions to the SQL server 2019 release. This feature allows you to deploy multiple, scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes, at once. The Big data Cluster, as an infrastructure, allows these clusters to run parallelly, where you can read, write, and process Big Data from Transact-SQL to Spark. It enables us to easily combine and analyze the high-value relational data with high-volume big data.
[Related Page: New SQL Server Cluster]
Advantages of Big Data Cluster:
The new SQL Server 2019 supports the very popular UTF-8 data encoding system. The UTF-8 character encoding is employed in data export, import, database-level, and column -level data collation. It is enabled when creating or changing the object collation type to object collation with UTF-8. It is supported for char and varchar data types.
The reason why data has to be encoded while storing and retrieving is mainly because of 2 reasons.
Note: As of Microsoft SQL Server 2016, UTF-8 is supported by BCP, BULK_INSERT, and OPENROWSET.
The earlier versions of SQL Server had encoding done in different formats like UCS-2 and they did not support the UTF-8 format. However, the introduction of Unicode encoding was done only from SQL Server 7.0.
[Related Page: Exception Handling In SQL Server]
Advantages of UTF Encoding:
This feature helps in storage saving, by using the right character set. For example, changing the existing data type of column with Latin strings from NCHAR(10) to CHAR(10) using a UTF-8 enabled collation, leads to 50% reduction in storage requirements. This saving happens because NCHAR(10) requires 20 bytes for storage, whereas CHAR(10) requires 10 bytes for the same Unicode string.
CTP 2.1 allows selecting UTF-8 collation as default during SQL Server 2019 preview setup.
CTP 2.2 allows selecting to use UTF-8 character encoding with SQL Server Replication.
This is the feature that allows an index to create operation to pause and resume later, right from the point where the operation failed or paused, instead of starting the process all over again.
The index is one of the powerful tools for database management. With more operations on databases like insert, update and delete, the index becomes more fragmented and hence less efficient. In order to combat this, index rebuild operations are increasingly adopted by the DBAs.
Resumable Online Index Rebuilding (ROIR) was adopted from SQL Server 2017 as an important feature to enhance database performance.
However, in the SQL Server 2019 version, a newer version of the feature is incorporated, which is "Resumable Online Index Create"
Features of the Resumable Online Index Create
In the older versions, when this feature was not introduced, upon the new index creates operation failure, the whole process had to start from the beginning.
The SQL Server 2019 also allows setting this as a default feature for a specific database.
[Related Page: SQL Server Constraints With Example]
The feature of Intelligent Query Processing (IQP) is a method adopted to obtain an optimal query execution plan with lower compiler time. This feature is expanded to include many other sub-features in the SQL Server 2019, CTP 2.2.
There are many factors considered while executing IQP, mainly to generate a good enough execution plan. These factors are Structures to be used, Joins to be made in a query (Hash Join, Nested Loop, Merge Adaptive, etc.), Outer Input, execution mode (Batch or Row execution mode), etc.
SQL Server 2017 had the feature of Intelligent Query Processing with the following sub-features:
There are, however, many improvements made in the IQP for the SQL Server 2019 CTP 2.0 preview version. These features are:
[Related Page: R Data Tables Tutorial With Examples ]
Always On availability groups is a disaster-recovery and a high-availability (HA) solution that aims at providing an enterprise-level alternative to database mirroring. This feature was initially introduced for SQL Server 2012, to increase the availability of a set of user databases for an enterprise.
An availability group is designed to support the replicate environment for a set of user databases called availability databases. An availability group can be either created for High Availability (HA) or for read-scale.
The failure of an availability group happens at the level of an availability replica. Availability database incurs, failover all together.
Features of Always On availability groups in SQL Server 2017
SQL Server 2017 introduces two sets of availability groups, differentiated on the basis of their architecture.
Always On availability groups
This provides high availability, disaster recovery, and read-scale balance. The availability groups here use the cluster manager in case of a cluster failover. In Linux, Pacemaker is used for the same while, Windows uses a cluster manager.
Read-scale availability group
This architecture provides replicas only for the read-only workloads. They don't provide the High Availability. There is no Cluster manager used in a Read-scale availability.
Every set of an availability database is hosted by an availability replica. The 2017 SQL Server version provides only 2 types of the replica. They are the primary replica and the secondary replica. An availability replica supports with redundancy only at the database level.
[Related Page: Different Types Of Joins In SQL Server]
New Added Features of SQL Server 2019
Apart from the existing features of the SQL Server 2017, there are new improvements and additions made to the SQL Server 2019.
Advantages of the new features of Always On availability groups in SQL Server 2019
Microsoft has always been keen on mixing up the data and the code. Microsoft SQL Server has seen a transition of this trend from T-SQL, to Azure-focused U-SQL, which then extended T-SQL with C# elements. SQL Server then added an embedded R support in 2016. In 2017, this focus was extended to the addition of Python to the SQL Server. This has insanely attracted the Machine Learning enthusiasts who are not even introduced to SQL Server!
Microsoft has always looked up to introduce new features that would make the SQL Servers on Linux get in parity with the SQL Server on Windows. Some of the improvements in Machine Learning, made in SQL Server 2019 on Linux, are discussed below.
Features of Machine Learning on Linux in SQL Server 2017
There are many advantages of having Python and R embedded in the SQL Server. Some of them are listed below:
New Added Features of the Machine Learning in SQL Server 2019
[Related Page: R Data Tables Tutorial With Examples ]
Since SQL Server is directly dealing with the database management and procuring, the security of the transactions and the data involved forms one of the most important requirement.
Security for accessing the SQL servers is managed by certificates. The new security feature of SQL Server 2019 includes Certificate management in SQL Server Configuration Manager (CTP 2.0). This certificate authenticates the secure access to SQL Server instances. The certificate management is now dedicated to SQL Server Configuration Manager, hence easing other tasks such as:
Always Encrypted was used as the Microsoft solution for data security. However, there are certain limitations to this method, where simple functions like mathematical operations cannot be performed on the encrypted data.
To overcome this, a new technology called "Secure Enclaves" is adopted which allows simple functions like the aggregate functions and LIKE queries to be executed on the Always Encrypted data.
Below are a few distinctive features that differentiate the SQL Server 2017 version from SQL Server 2019.
|Topics||SQL Server 2017||SQL Server 2019|
|Big Data clusters||Was not included||A new feature of big data cluster incorporated to handle the big data problems|
|Security||Always Encrypted” feature encodes data. Encoded data cannot handle any mathematical or relational operations on them.||“Secure Enclaves” improvises over the previously encoded data by allowing the basic mathematical or relational operations on encoded data.|
|Intelligent Query Processing||Adaptive Joins in batch mode and memory feedback in batch mode supported.||Along with previous version features, includes memory feedback in row store mode and Scalar UDF Inlining.|
|Indexes||Resumable Online Index Rebuilding||Resumable Online Index Create|
|Always On availability groups||2 replicas||
Secondary to primary index replica redirection
|Linux||Doesn’t support the OpenLDAP||Supports OpenLDAP|
This section deals with the introduction to the Standard and Enterprise Editions of SQL Server 2019 and the differences between them.
Standard: Standard provides the basic functionalities of databases like reporting, analysis, basic availability features, disaster-recovery, etc.
Enterprise: Enterprise version has all the Standard edition features along with some additional, advanced features. This version is suitable for companies that are looking for high scalability and performance.
Below are a few differences between the standard and the Enterprise edition
|Topics of difference||Standard Edition||Enterprise Edition|
|Performance and Scalability||Basic support for scalability and limited performance compared to Enterprise Edition.||Provides better scalability and performance|
|Support||Supports for 128 GB||Supports up to 524 petabytes|
|Security||Provides basic auditing, contained databases, encryption and backups, and user-defined roles||Provides fine-grained auditing, transparent database encryption, and extensible key management apart from the basic Standard Security features|
|Replication||Provides the fundamental SQL Server change tracking, merge replication, and snapshot replication.||Provides the high-end Oracle publishing and peer-to-peer transactional replication, besides the basic Standard edition replication capabilities|
Only 3 of the 11 features of the Enterprise edition are there in Standard edition when it comes to scalability and performance. Hence, the users should be wise enough to choose the enterprise edition, in case of any foreseen scalability.
Microsoft has been very strategical in bringing the enhancements and features to every SQL Server Version. A new version is brought, always with an ode to the limitations of the previous version and with a new target in mind. Below are some reasons as to why one should upgrade to SQL Server 2019.
The SQL Server 2019 is brought with a focus to enhance the big data capabilities and improvised Machine Learning experience and Linux SQL Server performance.
As most of the companies are adapting to the big data, it is important to incorporate the features that support big data, in SQL Server. The SQL Server 2019 has developed big data clusters, which handles all the tasks related to big data. However, this feature was not implemented in the SQL Server 2017.
Microsoft has always aimed at enhancing the SQL Server to adapt to multiple platforms like Windows, Linux, and Mac OS. There are continuous efforts made to match the performance in the other two platforms with that of the Windows SQL Server. The SQL Server 2019 has better-integrated support for Linux with Kubernetes. The performance of the SQL Server 2019 on Linux instances have become better with parallel processing of the tasks on the Containers.
There are multiple improvements made on the Always On Availability Group. New features of connection redirection and enhanced database health checks are ensured.
Resumable Online Index creation has eased the whole process of heavy Index creation, manageable. Otherwise, in older editions, with any pause to the Index creation process, either due to memory constraints or otherwise, the process had to be re-started, making it cumbersome.
SQL Server 2019 is definitely an improvised version of the previous SQL Server editions. There are many good reasons why companies need to adapt to the new version. Since most of the features are an extension to the existing features of the older SQL server versions, no new installation or environment setup needs to be done. Many feedbacks to the new installation and update include the ease of set up and easy usage of the tool. Overall, it's a reliable and stable product.
|SSAS||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
Arogyalokesh is a Technical Content Writer and manages content creation on various IT platforms at Mindmajix. He is dedicated to creating useful and engaging content on Salesforce, Blockchain, Docker, SQL Server, Tangle, Jira, and few other technologies. Get in touch with him on LinkedIn and Twitter.