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:

• Added internal driver support to provide resiliency to DNS failures
• Added support for Active Directory Integrated, Active Directory Interactive and Active Directory Service Principal authentication mode for .NET Core and .NET Standard
• Added support for Active Directory Service Principal authentication mode for .NET Framework
• Added support for optional ORDER hints in SqlBulkCopy for improved performance
• New Connection String properties synonyms

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.

#azure sql #.net #.net core #developers #library #performances #reliability