Microsoft.Data.SqlClient 2.0.0 is now available

Avatar

Davide

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 Open Source, 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:

Introducing the new Microsoft.Data.SqlClient

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.

dotnet add package Microsoft.Data.SqlClient

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.

New Features

The new version 2.0.0 introduces quite a few interesting features:

My favorite among those are the support for the ORDER hints in the SqlBulkCopy class and the better resilency to DNS failures.

The ORDER hint it allow to you to greatly improve performances if the data you want to import is already ordered by the target’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.

Make sure you take a look at the very long and detailed release note document. It won’t take much of your time, and it will provide a great overview of what you can use to improve your solution.

Ordered Bulk Load

Without the ORDER option, in fact, this is the query plan you’ll get:

Image sort

and in my specific case the sort was impacting for up to 50% the performance of my bulk load! After using the new library where sort order was specified, the plan didn’t show the Sort operator anymore:

Image no sort

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’s the code snipped I used to inform bulk load API of the existing ordering:

foreach (var ci in tableInfo.ClusteredIndex) { 
    bulkCopy.ColumnOrderHints.Add(ci.ColumnName, ci.IsDescending ? SortOrder.Descending : SortOrder.Ascending); 
}

DNS Failure Resiliency

The other feature that I really love is the better resiliency to DNS failures. Resiliency is really 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.

A Community Effort

Last but not least, as a former MVP 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.

12 comments

Leave a comment

  • Avatar
    MgSam

    I’m confused about the explanation of OrderHint. Is this saying that the DataTable being bulk-loaded needs to have the data already sorted based on the target table’s clustered index in order to use this feature? The way it’s worded makes it sound like just by telling SqlBulkCopy what the clustered index is you’ll somehow magically get improved perf, which doesn’t make sense to me.

    Under what scenarios would you have a DataTable with the data already sorted where you haven’t had to manually do that in memory? Outside of creating incrementing ids on the client (which would be unusual), I can’t think of a use case.

    • Avatar
      Davide MauriMicrosoft logo

      The use case for this feature (which, by the way, always existed, just the SqlBulkCopy API was not supporting it) is for when you have to read data from a table and write it into another table. If you read it following the Clustered Index order, and the target table has the same Clustered Index you can spare the sort operation. Another use case is when you have a file that you want to import and that file is, again, already ordered by the target table Clustered Index. This use cases are quite frequent in ETL / integration / Import-Export scenarios.

  • Avatar
    Jefe

    Thanks for the heads-up, I am actually in the middle of changing my Azure Functions database layer from table storage to Azure SQL, so this is really good timing.

    One question however, regarding the new “Active Directory Service Principal” authentication mode; does this also support managed resource identities? I.e. MSI? This would be huge.

    Cheers!

    • Avatar
      Cheena Malhotra

      Hi Jefe,

      “Active Directory Service Principal” applies to registered applications with App-Id and secret configured to connect.
      Active Directory authentication with Managed Identity (MSI) is not yet supported, but is planned for future releases. You may track issue dotnet/SqlClient#616 for further updates.

  • Avatar
    Theo Albers

    This is great news! I just spent hours a week ago on MARS issues with the v1 release on Linux App Services, because EFCore used the minimum version. I upgraded to the latest v1 release. Now I’m happy to upgrade to v2 with the connection resilience support and service principal support.

  • Jefferson Motta
    Jefferson Motta

    I was the first to download, when I clicked on the download had 0 downloads.

    So, when I upgraded from NuGet, SNI did not update together, and when I publish on production, just not works, without no messages, just new SqlConnection Open just returned null.

    After I check the SNI hasn’t updated It back to work.

    This happen on Desktop and Web App.

    I ‘m using c# 8, .NET CORE 3.1 and .NetFramework 4.8, VS 2019.

    Best,

    Jeff

    #jefferson2020