{"id":41363,"date":"2022-07-29T14:21:15","date_gmt":"2022-07-29T14:21:15","guid":{"rendered":"https:\/\/www.techrepublic.com\/?p=3987010"},"modified":"2022-07-29T14:21:15","modified_gmt":"2022-07-29T14:21:15","slug":"sql-server-2022-heres-what-you-need-to-know","status":"publish","type":"post","link":"https:\/\/cloudnewshub.com\/?p=41363","title":{"rendered":"SQL Server 2022: Here\u2019s what you need to know"},"content":{"rendered":"<figure id=\"attachment_3973633\" aria-describedby=\"caption-attachment-3973633\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" class=\"size-article wp-image-3973633\" src=\"http:\/\/cloudnewshub.com\/wp-content\/uploads\/2022\/07\/sql-server-2022-heres-what-you-need-to-know.jpg\" alt=\"Computer technology isometric icon, server room, digital device set, element for design, pc laptop, mobile phone with smartwatch, cloud storage, flat vector\" width=\"770\" height=\"513\"><figcaption id=\"caption-attachment-3973633\" class=\"wp-caption-text\">Image: fullvector\/Adobe Stock<\/figcaption><\/figure>\n<aside class=\"pinbox right\">\n<h3 class=\"heading\">Must-read developer coverage<\/h3>\n<\/aside>\n<p>Three decades on, SQL Server is still a database workhorse that powers both an internal line of business applications and large-scale public websites like <a href=\"https:\/\/meta.stackexchange.com\/questions\/10369\/which-tools-and-technologies-are-used-to-build-the-stack-exchange-network\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\">Stack Overflow<\/a>. While some database workloads have migrated to the cloud\u2014and the next version of SQL Server has more tools and integration with Azure services for backup or analytics to help with that if it\u2019s the right choice for a business\u2014there are plenty of new features to support a wide range of database scenarios.<\/p>\n<p>\u201cAs a database platform, SQL Server provides customers the maximum flexibility from edge to cloud,\u201d said Asad Khan, director of program management for Microsoft Azure. \u201cDepending on their workload and application requirements, customers can deploy SQL Server in the environment of their choice.<\/p>\n<p>\u201cOur customers run SQL Server in small footprint edge devices supporting IoT-type scenarios to huge servers running mission-critical applications to hybrid footprint in public cloud.\u201d<\/p>\n<p>So while SQL Server 2022 enables migrating workloads to Azure SQL Managed Instance with virtually no downtime using distributed availability groups, users can also restore databases back to SQL Server on their own infrastructure.<\/p>\n<p>If you\u2019re doing a hardware upgrade on a server or physically moving it somewhere, the workload can temporarily be moved up to Azure and then brought back down when ready.<\/p>\n<p><strong>SEE:&nbsp;<a href=\"https:\/\/www.techrepublic.com\/article\/best-cloud-certifications\/\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\">Best cloud certifications in 2022: From Azure to Google and beyond<\/a> (TechRepublic)<\/strong><\/p>\n<h2>Use cloud without migrating<\/h2>\n<p>SQL Server can use Azure services for backup, business continuity, analytics, policy and data governance.<\/p>\n<p>\u201cSQL Server 2022 integrates with Azure Synapse Link and Microsoft Purview to enable customers to drive deeper insights, predictions and governance from their data at scale,\u201d Khan said. \u201cCloud integration is enhanced with managed disaster recovery (DR) to Azure SQL Managed Instance, along with near real-time analytics, allowing database administrators to manage their data estates with greater flexibility and minimal impact to the end user.\u201d<\/p>\n<p>The SQL Managed Instance effectively becomes a secondary replica you can fail over to and fail back from, without the need to set up an availability group. The hybrid capabilities also use Azure to fine tune how a SQL Server 2022 system runs.<\/p>\n<p>\u201cWith more intelligence built into the actual database operations, workloads automatically learn how to run better, faster and smarter without manual tuning or oversight,\u201d Khan said.<\/p>\n<p>The integration is helpful for hybrid cloud scenarios that go in the other direction. Microsoft Defender for Cloud can protect on-premises SQL databases through SQL Server Extension for Azure, which can now be installed when setting up SQL Server, and users can use <a href=\"https:\/\/cloudblogs.microsoft.com\/sqlserver\/2022\/07\/28\/azure-active-directory-authentication-for-sql-server-2022\/\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\">Azure AD authentication<\/a>, including multi-factor authentication (MFA), to access an on-premises SQL Server 2022.<\/p>\n<p>Also, Azure includes running Azure services on an organization\u2019s own infrastructure.<\/p>\n<p>\u201cWith Azure Arc enabled SQL Server, customer workloads connect to Azure to give customers always on database access, evergreen software updates and actionable intelligence from their deployment,\u201d Khan said.<\/p>\n<p>And it\u2019s not just Azure; the new object storage integration means users can integrate with S3-compatible storage for backup and restore, which includes storage fabrics from Dell, HPE, Nutanix, Pure Storage, Red Hat and Scality as well as AWS. According to Khan, the object storage can also be used for big data.<\/p>\n<p>\u201cData Lake Virtualization integrates PolyBase with S3-compatible object storage, which adds support for querying parquet files with T-SQL,\u201d said Khan.<\/p>\n<p>But if Azure is being used for big data, users can effectively do away with ETL (extract, transform, load) and use their SQL Server operational data for analytics.<\/p>\n<p><strong>SEE: <a href=\"https:\/\/www.techrepublic.com\/resource-library\/whitepapers\/microsoft-power-platform-what-you-need-to-know-about-it-free-pdf\/\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\">Microsoft Power Platform: What you need to know about it (free PDF) <\/a>(TechRepublic)<\/strong><\/p>\n<p>\u201cAzure Synapse Link for SQL Server 2022 provides near real-time analytics and hybrid transactional and analytical processing with minimal impact on operational systems,\u201d Khan said. \u201cWith a seamless integration between operational stores in SQL Server 2022 and Azure Synapse Analytics dedicated SQL pools, Azure Synapse Link for SQL enables customers to run analytics, business intelligence and machine learning scenarios on their operational data with minimum impact on source databases with a new change feed technology.\u201d<\/p>\n<p>That\u2019s the same approach Microsoft has already taken with Cosmos DB, so this is a very traditional SQL database getting features first seen in database services designed for the cloud.<\/p>\n<h2>A ledger without a blockchain<\/h2>\n<p>If you\u2019ve ever looked at the claims for blockchains and thought that an append-only database could do that without all the work of designing and maintaining a distributed system that likely doesn\u2019t scale to high-throughput queries (or the environmental impact of blockchain mining), another feature that<a href=\"https:\/\/techcommunity.microsoft.com\/t5\/azure-sql-blog\/announcing-azure-sql-database-ledger\/ba-p\/2200401\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\"> started out in Azure SQL<\/a> and is now coming to SQL Server 2022 is just what you need.<\/p>\n<p>\u201c<a href=\"https:\/\/docs.microsoft.com\/en-gb\/sql\/relational-databases\/security\/ledger\/ledger-overview?view=sql-server-ver16\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\">Ledger<\/a> brings the benefits of blockchains to relational databases by cryptographically linking the data and their changes in a blockchain structure to make the data tamper-evident and verifiable, making it easy to implement multi-party business process, such as supply chain systems, and can also streamline compliance audits,\u201d Khan explained.<\/p>\n<p>For example, the quality of an ice cream manufacturer\u2019s ice cream depends on both the ingredients that its suppliers send and the finished ice cream it delivers being shipped at the right temperature. If the refrigerated truck has a fault, the cream might curdle, or the ice cream might melt and then refreeze once it\u2019s in the store freezer.<\/p>\n<p>By collecting sensor information from everyone in its supply chain, the ice cream manufacturer can track down where the problem is. But, everyone has to be able to trust that the temperature readings are correct and haven\u2019t been tampered with to shift the blame.<\/p>\n<p>\u201cLedger allows participants to verify the integrity of the centrally housed data,\u201d Khan said.<\/p>\n<p>When creating a ledger table, any rows that are modified by a transaction are cryptographically hashed along with the transactions themselves. That\u2019s stored in the same Merkle tree data structure other blockchains use, and users can periodically write digests that represent the state of the database to tamper-proof storage, so they can check later that the live database hasn\u2019t been tampered with by an attacker or a malicious admin.<\/p>\n<p>For SQL Azure that\u2019s Azure Blob storage of Azure Confidential Ledger, which uses secure enclaves in Azure Confidential Computing. These tools can be used with SQL Server 2022 or with an organization\u2019s own WORM (write once read many) storage.<\/p>\n<p>But unlike many blockchain systems, Ledger is environmentally sustainable.<\/p>\n<p>\u201cIt does not rely on expensive consensus protocols, such as proof of work (PoW), commonly used by blockchain technologies,\u201d said Khan. \u201cThe PoW mechanism requires multiple servers to compete to solve complex mathematical problems, which consumes a lot of energy.\u201d<\/p>\n<p><strong>SEE:&nbsp;<a href=\"https:\/\/www.techrepublic.com\/article\/azure-database-postgresql-flexible-server\/\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\">What is Azure Database for PostgreSQL Flexible Server?<\/a> (TechRepublic)<\/strong><\/p>\n<p>There isn\u2019t any specific server hardware needed to run Ledger, like a CPU with secure enclave support. Users can create both updatable and append-only ledger tables depending on whether they need to update and delete rows or just insert new ones. Either way, users get a tamper-proof record they can show to an auditor.<\/p>\n<p>\u201cAs with a traditional ledger, the feature preserves historical data,\u201d said Khan. \u201cIf a row is updated in the database, its previous value is maintained in a relational form to support SQL queries and is protected in a history table.<\/p>\n<p>\u201cLedger provides a chronicle of all changes made to the database over time, while maintaining the power, flexibility and performance of the SQL database.\u201d<\/p>\n<h2>Improving query performance<\/h2>\n<p>SQL Server 2022 has more Intelligent Query Processing features to speed up existing workloads and database applications with what Khan calls \u201cminimal implementation effort.\u201d<\/p>\n<p>\u201cApplications can automatically benefit from these features without any code changes by just enabling Query Store, running on the latest application database compatibility level or turning on a database level configuration,\u201d said Khan.<\/p>\n<p>The new features tweak settings that are complex or just tedious for database admins to handle and improve some of the existing automatic performance enhancements, which can sometimes backfire.<\/p>\n<p>Getting the degree of parallelism for a query can be tricky, so DOP (Degrees of Parallelism) feedback uses a feedback loop to check if using more CPU cores actually makes the query faster or just takes resources from other database tasks where the cores would be more useful.<\/p>\n<p>\u201cDOP feedback automatically adjusts the degree of parallelism for repeating queries to optimize for workloads where inefficient parallelism can cause performance issues,\u201d Khan explained. \u201cMemory Grant feedback adjusts the size of the memory allocated for a query based on past performance.\u201d<\/p>\n<p>SQL Server 2022 can use percentiles to improve the feedback algorithm for queries where the amount of memory changes a lot each time users run them, and persist memory grants feedback even if users evict the cache or restart the server.<\/p>\n<p>The SQL Server query optimizer estimates the cost of running a query plan using the number of rows that will be processed, which is the cardinality for the query. There are some queries where it\u2019s difficult for SQL Server to make a good estimate of what the cardinality will be, so it gets the cost wrong.<\/p>\n<p><strong>SEE:&nbsp;<a href=\"https:\/\/www.techrepublic.com\/article\/combine-values-microsoft-excel-power-query\/\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\">How to combine values from a column into a single cell using Microsoft Excel\u2019s Power Query<\/a> (TechRepublic)<\/strong><\/p>\n<p>\u201cCardinality Estimation feedback identifies and corrects suboptimal query execution plans for repeating queries, when these issues are caused by [those] incorrect estimation model assumptions,\u201d Khan explained.<\/p>\n<p>The speed of some queries can vary dramatically because the data users are looking at can be very different. When looking at how many items a supplier has in stock, that\u2019s naturally going to be much faster if they only carry a few lines rather than hundreds or thousands. Parameter sensitive plan optimization looks for non-uniform data distributions like that and tells SQL Server to store multiple plans for how to run the stock query because the parameters will vary so widely.<\/p>\n<p>Batch mode operations will be faster if an organization has server CPUs with Advanced Vector Extension (AVX) 512 extensions.<\/p>\n<p>\u201c[This] improves the performance of buffer pool scan operations on large-memory machines by utilizing multiple CPU cores,\u201d Khan said.<\/p>\n<p>There are also some specific Query Store improvements, like using it on secondary replicas to get insights about workloads running there. Users also get the Query Store hints that database developers can already use to tell in Azure SQL DB and Managed SQL Instances to help speed up queries without rewriting them.<\/p>\n<p>That\u2019s the promise that Microsoft has made about Azure versions of server products all along\u2014that new features will be built for the cloud first. But when they will be useful running on an organization\u2019s own hardware, they\u2019ll show up in the next release of the server software.<\/p>\n<h2>How can I get SQL Server 2022?<\/h2>\n<p>Microsoft hasn\u2019t announced when it expects to ship SQL Server 2022, but it\u2019s common for new releases to arrive around the time of Microsoft Ignite, which is at the end of October this year. The <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/sql-server\/what-s-new-in-sql-server-2022?view=sql-server-ver16\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\">Community Technology Preview 2.1 release <\/a>of SQL Server 2022 is available now as a 180-day trial<a href=\"https:\/\/go.microsoft.com\/fwlink\/?linkid=2162126\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\"> Evaluation Edition<\/a> with all the features of the Enterprise edition, or users can run the Developer Edition as a 180-day trial in an Azure VM <a href=\"https:\/\/ms.portal.azure.com\/#create\/Microsoft.AzureSQL\" target=\"_blank\" rel=\"nofollow noopener sponsored noreferrer\">from the marketplace<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Image: fullvector\/Adobe Stock Must-read developer coverage Three decades on, SQL Server is still a database workhorse that powers both an internal line of business applications and large-scale public websites like Stack Overflow. While some database workloads have migrated to the cloud\u2014and the next version of SQL Server has more tools and integration with Azure services [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":41364,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[39,40,783,152,27],"tags":[],"class_list":["post-41363","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-big-data","category-cloud","category-cloudsync","category-microsoft","category-software"],"_links":{"self":[{"href":"https:\/\/cloudnewshub.com\/index.php?rest_route=\/wp\/v2\/posts\/41363","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cloudnewshub.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cloudnewshub.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cloudnewshub.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cloudnewshub.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=41363"}],"version-history":[{"count":0,"href":"https:\/\/cloudnewshub.com\/index.php?rest_route=\/wp\/v2\/posts\/41363\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cloudnewshub.com\/index.php?rest_route=\/wp\/v2\/media\/41364"}],"wp:attachment":[{"href":"https:\/\/cloudnewshub.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=41363"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cloudnewshub.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=41363"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cloudnewshub.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=41363"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}