Microsoft SQL Server 2022 Standard Edition – Corporate

Microsoft SQL Server 2019 Standard Edition – Corporate

Microsoft SQL Server 2022 Standard Edition – Corporate

SQL Server 2019 เป็นแพลตฟอร์มข้อมูลที่ ช่วยบริหารข้อมูลธุรกิจให้กับ องค์กร ด้วยการเป็นโซลูชันที่ช่วยวิเคราะห์ข้อมูล ค้นหา ทำรายงาน และ แจ้งเตือนได้อย่างเป็นระบบ ทั้งยังมีความปลอดภัยสูงและ รองรับการทำงานได้บนทุกแพลตฟอร์ม รวมทั้งสามารถขยายข้อมูลได้ทั้งบน เซิรฟ์เวอร์จริงและบนคลาวด์

What’s new in Microsoft SQL Server 2022

1. Big Data Clusters

Big data clusters are new additions to the SQL server 2022 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 to easily combine and analyze the high-value relational data with high-volume big data.

Features of Big Data Clusters

• Data Virtualization: SQL Server PolyBase has eased the task of querying the external data sources for the SQL Server big data clusters, by reducing the effort of moving or copying the data for making a query. SQL Server 2022 preview has introduced new connectors to data sources.
• Data Lake: The big data cluster allows for a scalable HDFS storage pool. This potentially increases the efficiency of big data storage from external sources.
• Scale-out data mart: Big data cluster provides scale-out compute and storage to improve the data analysis. The data can be ingested and stored across multiple data pool nodes as cache, for further analysis.
• Integrated AI and Machine Learning: The big data cluster allows for AI and ML on the data stored across multiple HDFS storage pools and data pools. SQL server provides many built-in AI tools like R, Python, Scala or Java.
• Management and Monitoring: The cluster administrator portal is the website that provides the status and health of the pods in the cluster. It also provides links for other dashboards for log analytics and monitoring.
• Management and monitoring will be done using the combination of the command line tools, APIs, administrator portal and dynamic management views.

Advantages of Big Data cluster

• It has built-in snippets for regular management tasks.
• It allows browsing HDFS, to create directories, to preview files and upload files.
• It allows creating, opening and running Jupyter-compatible notebooks.
• The creation of external data sources has been simplified by the Data Virtualization Wizard.
• Big data cluster with K8 infrastructure increases the speed of setting up the whole group infrastructure.
• The security concerns arising with the integration of the relational environment with the Big Data are handled completely by the big data clusters.
• The data virtualization allows for easy data integration without having to perform ETL (extract, transform, and load).

2. UTF-8 Support (CTP 2.2)

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.

• For reducing the memory occupancy or storage space.
• To provide data security for sensitive data.

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.

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.

3. Resumable online index create (CTP 2.0)

This is the feature that allows an index 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 database 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

• You can resume the index create operation after an index creates failure in case of overuse of disk space or during database loss.
• Pausing the ongoing index create operation in case of blockages will result in freeing up the resources temporarily, to resume the blocked tasks.
• The heavy log generation due to the cumbersome index creation operation, can be handled by pausing the index create operation, truncating or taking the backup of the log and then resuming the same.

Microsoft SQL Server Standard 2019 OLP
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.

4. Intelligent query processing (CTP 2.0)

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.

5. Always On Availability Groups

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.