{"id":186,"date":"2020-06-19T05:48:19","date_gmt":"2020-06-19T12:48:19","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=186"},"modified":"2020-06-26T00:53:31","modified_gmt":"2020-06-26T07:53:31","slug":"microsoft-data-sqlclient-2-0-0-is-now-available","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/microsoft-data-sqlclient-2-0-0-is-now-available\/","title":{"rendered":"Microsoft.Data.SqlClient 2.0.0 is now available"},"content":{"rendered":"<p>As you probably already know, one year ago a new Microsoft.Data.SqlClient library was released to replace the well-know System.Data.SqlClient. This was done to add support to .Net Core, to be <a href=\"https:\/\/github.com\/dotnet\/SqlClient\"><strong>Open Source<\/strong><\/a>, and to serve as the starting point for all future development and investments Azure SQL and SQL Server data access drivers. If you missed such news, you can read all the details here:<\/p>\n<p><a href=\"https:\/\/devblogs.microsoft.com\/dotnet\/introducing-the-new-microsoftdatasqlclient\/\">Introducing the new Microsoft.Data.SqlClient<\/a><\/p>\n<p>A few days ago the version 2.0.0 has been released with some interesting news. All you have to do to use this new version, if you are already using version 1.x is just update it via NuGet or the .NET Core CLI.<\/p>\n<pre>dotnet add package Microsoft.Data.SqlClient<\/pre>\n<p>If you are still using System.Data.SqlClient, you just have to replace the old library with the new one, as it is a true drop-in replacement. All you code will still work as before. But it will be more future-proof.<\/p>\n<h2>New Features<\/h2>\n<p>The new version 2.0.0 introduces quite a few <a href=\"https:\/\/github.com\/dotnet\/SqlClient\/releases\/tag\/v2.0.0\">interesting features<\/a>:<\/p>\n<ul>\n<li><a href=\"https:\/\/github.com\/dotnet\/SqlClient\/pull\/594\">Added internal driver support to provide resiliency to DNS failures<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/dotnet\/SqlClient\/blob\/master\/release-notes\/2.0\/2.0.0.md#additional-active-directory-authentication-modes\">Added support for Active Directory Integrated, Active Directory Interactive and Active Directory Service Principal authentication mode for .NET Core and .NET Standard<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/dotnet\/SqlClient\/blob\/master\/release-notes\/2.0\/2.0.0.md#active-directory-service-principal\">Added support for\u00a0Active Directory Service Principal authentication mode for .NET Framework<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/dotnet\/SqlClient\/blob\/master\/release-notes\/2.0\/2.0.0.md#order-hints-for-sqlbulkcopy\">Added support for optional\u00a0ORDER\u00a0hints in\u00a0SqlBulkCopy for improved performance<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/dotnet\/SqlClient\/blob\/master\/release-notes\/2.0\/2.0.0.md#new-connection-string-property-synonyms\">New Connection String properties synonyms<\/a><\/li>\n<\/ul>\n<p>My favorite among those are the support for the ORDER hints in the SqlBulkCopy class and the better resilency to DNS failures.<\/p>\n<p>The ORDER hint it allow to you to greatly improve performances if the data you want to import is <em>already<\/em> ordered by the target&#8217;s table clustered index. By informing the database engine that data is coming in already ordered, data can be loaded as is, without an additional sorting step, that usually is the most expensive on the whole operation.<\/p>\n<p>Make sure you take a look at the very long and detailed <a href=\"https:\/\/github.com\/dotnet\/SqlClient\/blob\/master\/release-notes\/2.0\/2.0.0.md#summary-of-changes-in-20\">release note document<\/a>. It won&#8217;t take much of your time, and it will provide a great overview of what you can use to improve your solution.<\/p>\n<h2>Ordered Bulk Load<\/h2>\n<p>Without the ORDER option, in fact, this is the query plan you&#8217;ll get:<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-193 size-full\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/06\/sort.png\" alt=\"Image sort\" width=\"977\" height=\"162\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/06\/sort.png 977w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/06\/sort-300x50.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/06\/sort-768x127.png 768w\" sizes=\"(max-width: 977px) 100vw, 977px\" \/><\/p>\n<p>and in my specific case the sort was impacting for up to <strong>50%<\/strong> the performance of my bulk load! After using the new library where sort order was specified, the plan didn&#8217;t show the Sort operator anymore:<\/p>\n<p><img decoding=\"async\" class=\"wp-image-192 size-full aligncenter\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/06\/no-sort.png\" alt=\"Image no sort\" width=\"702\" height=\"138\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/06\/no-sort.png 702w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/06\/no-sort-300x59.png 300w\" sizes=\"(max-width: 702px) 100vw, 702px\" \/><\/p>\n<p>This also means I can bulk load the table without having to drop and recreate the clustered index to get the maximum throughput, which in turns means better overall performances. Here&#8217;s the code snipped I used to inform bulk load API of the existing ordering:<\/p>\n<pre class=\"prettyprint\">foreach (var ci in tableInfo.ClusteredIndex) { \r\n    bulkCopy.ColumnOrderHints.Add(ci.ColumnName, ci.IsDescending ? SortOrder.Descending : SortOrder.Ascending); \r\n}<\/pre>\n<h2>DNS Failure Resiliency<\/h2>\n<p>The other feature that I really love is the better resiliency to DNS failures. Resiliency is <em>really<\/em> a key point when developing for the cloud, but unfortunately the majority of developers are not used to properly deal with it, so is quite often overlooked. Having the client library to deal with this for us solves the problem at the root and also removes the need to manually deal with this plumbing code.<\/p>\n<h2>A Community Effort<\/h2>\n<p>Last but not least, as a <a href=\"https:\/\/mvp.microsoft.com\/en-us\/PublicProfile\/36809\">former MVP<\/a> and lover of everything community, I really love the fact that SqlClient is really becoming a community effort as you can see from the contributor list.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft.Data.SqlClient version 2.0.0 has been released, with several interesting features. Make sure you check it out if you are a .NET developer and you are using Azure SQL or SQL Server in your solutions. Microsoft.Data.SqlClient is the new, open source, official data access library that replaces System.Data.SqlClient<\/p>\n","protected":false},"author":24720,"featured_media":81,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[244,245,30,246,247,167],"class_list":["post-186","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","tag-net","tag-net-core","tag-developers","tag-library","tag-performances","tag-reliability"],"acf":[],"blog_post_summary":"<p>Microsoft.Data.SqlClient version 2.0.0 has been released, with several interesting features. Make sure you check it out if you are a .NET developer and you are using Azure SQL or SQL Server in your solutions. Microsoft.Data.SqlClient is the new, open source, official data access library that replaces System.Data.SqlClient<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/186","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/24720"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=186"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/186\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/81"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=186"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=186"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=186"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}