June 19th, 2020

Microsoft.Data.SqlClient 2.0.0 is now available

Davide Mauri

Principal Product Manager

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:

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:

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.

Author

Davide Mauri

Principal Product Manager

Principal Product Manager in Azure SQL, with a career in IT spanning since 1997, earning the prestigious Data Platform MVP status for 12 consecutive years. Currently, he serves as the Principal Product Manager for Azure SQL Database, focusing on developers and AI.

13 comments

Discussion is closed. Login to edit/delete existing comments.

Sort by :

Newest

Newest Popular Oldest

Jefferson Motta June 24, 2020 0

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
- Davide Mauri Author June 24, 2020 0
  
  Hi Jefferson. That’s interesting, thank for reporting this. I used NuGet (actually dotnet CLI) to add the package to a project of mine that was still using System.Data.SqlClient (https://github.com/yorek/smartbulkcopy/commit/6856456b083f9454d7fdebeff6a391787aea558f) and had no issues. But my project wasn’t referencing SNI directly. Were you able to have everything working at the end?
  - Jefferson Motta June 28, 2020 0
    
    Hi Davide, I was using 1.x, and when I upgrade to 2.0 when occurred error. To fix update manually the SNI component, it existed on the project but was outdated.
    Best,
    Jeff
Pablo Terevinto June 23, 2020 0

Will this be fixing this issue to let it work on a Blazor WASM app?
- AlanW June 23, 2020 0
  
  Hi Pablo,
  
  The issue with Blazor App is currently under investigation.
Theo Albers June 20, 2020 0

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.
Andrei Mironov June 20, 2020 0

Does EF Core use the new client?
- Jeremy Likness June 22, 2020 0
  
  The .NET 5 preview versions of EF Core use the new client.
Jefe June 20, 2020 0

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!
- Cheena Malhotra June 22, 2020 0
  
  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.
  - Mark Davies August 3, 2020 0
    this worked for me for MSI for an App Service on netcore and an earlier version of this library.
    
    Set up a Sql Command then...
    <code>
    then use the command - like
    <code>
    
    Read more
    this worked for me for MSI for an App Service on netcore and an earlier version of this library.
    
    Set up a Sql Command then…
    
    await using var sqlConnection = new SqlConnection("your connection string without user or password") { AccessToken = await (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/" "", cancellationToken); }; sqlCommand.Connection = sqlConnection;
    
    then use the command – like
    
    await sqlConnection.OpenAsync(cancellationToken) var reader = await sqlCommand.ExecuteReaderAsync(cancellationToken)
    
    Read less
MgSam June 19, 2020 0

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...
Read more
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.

Read less
- Davide Mauri Author June 19, 2020 0
  
  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...
  Read more
  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.
  
  Read less