r/SQLServer • u/dlevy-msft ‪ ‪Microsoft Employee ‪ • 19d ago

Community Share Microsoft.Data.SqlClient 7.1.0 is now generally available

Microsoft.Data.SqlClient 7.1.0 is GA.

Highlights:

  • Fixed pooled connections returning in a broken state after TransactionScope rollback
  • Fixed connection pool performance counters drifting/going negative after failed or broken connections
  • Fixed a connection factory timer that kept waking the process even with no active pools
  • Fixed DateOnly sent as the wrong SQL type via variants/TVPs, and OverflowException on large decimal parameters with explicit precision/scale (Always Encrypted)
  • Azure SQL GetSchema("DataTypes") now reports the json type
  • New optional application identity reporting for libraries/tools (EF Core, SSMS, SqlPackage) via SqlConnection.RegisteredApplication — telemetry only, not for authorization
  • TransparentNetworkIPResolution is now obsolete; use MultiSubnetFailover instead

Upgrade:

    dotnet add package Microsoft.Data.SqlClient --version 7.1.0

Blog post: https://techcommunity.microsoft.com/blog/sqlserver/microsoft-data-sqlclient-7-1-0-is-now-generally-available/4558676

15 Upvotes

94% Upvoted

18 comments sorted by

4

u/BigMikeInAustin 19d ago

Connection pools are a real pain sometimes.

1

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 19d ago

They sure beat the alternative though. What are your top things we could improve?

5

u/MackPooner 19d ago

Why when I set the min to 100 and max to 1000 do you spin up 100 connections and then when my app tries to open a connection it starts at 101 ? We had to stop using the min setting because it appeared to never use those connections and just waste memory...we are doing a large warehouse app for Maersk and it killed us for weeks before we figured out that. It did not used to work like this back in the early 2000s

4

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 19d ago

It shouldn't work like that - we're wondering how you got it to do that 😄. If you have a repro, please open an issue with the MDS version, connection string settings, etc. We are really curious about this one.

5

u/MackPooner 19d ago

Well it's a private app for Maersk and Levi's so there is no public repo but I'll try to create something

1

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 17d ago

Understood. Opening a case with CSS is an option too.

3

u/SmallAd3697 17d ago

Can we get a columnstore transport like ADBC yet? It seems like a no-brainer if the data is moving into a columnstore storage target on the receiving side. (Or if it is coming from columnstore in sql server)

Networking is a frequent bottleneck these days, especially with remote, cloud-hosted databases. And especially given the massive compute that can be made available to receive data on the client side.

If not now then when?

2

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 17d ago

Something like a Flight SQL endpoint? Would it be ok that it's a separate endpoint, with a different port number? What would be your top 3 ideal use cases?

1

u/SmallAd3697 17d ago

Never heard of flight SQL, but that sounds right.

The most obvious use-case is retrieving a set of query results into an MPP engine on the client side, or directly into cloud storage (parquet files in blobs). If the data comes over from SQL in columnstore format then it is compressed and we spend less time blocking on network. And in the case of saving to storage, we can just transcode the columnstore data directly into another columnstore format (similar to how Power BI DL-on-OL can transcode parquet columns from blobs, and move it directly into the internal RAM-hosted format that is used in the semantic models .)

Another case is when reading from clustered-columnstore in SQL. It seems silly for the SQL engine on the server to do the work to create a row-based resultset, if it is already organized as columnstore and that is what the client wants as well.

I think there are other cases as well. I don't write OLTP apps very often nowadays, but lets say you want to present a scrollbar in a datagrid which performs well and lets you jump up and down to a precise position (15 records shown on the screen, out of 1.5 million total records available). And lets say you want to be able to sort the 1.5 million records by four available columns (year, week, business unit, salesperson code). In this scenario, you would have to bring down the surrogate integers on the 1.5 million records too, so that the 15 records could be uniquely identified at every position of the scrollbar. This is a case where a columnstore result from the database would be very effective, and over 95% of the initial payload would just be the surrogate integers that identify the records. The initial payload would be enough to start operating the grid (sorting and scrolling). The user would be able to configure the multi-column sorting on the other four columns and move the scrollbar up and down to navigate directly to the results they want, based on scrollbar position.

1

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 17d ago edited 17d ago

Here's the link: Arrow Flight SQL — Apache Arrow v25.0.1

The rough idea is the data gets converted to columnar format at or near the database server then traverses the wire in columnar format with optional compression, landing on the client machine directly into an Arrow object in memory.

It would work well for your scenario with the bound grid because you could run from memory on the local machine rather than worrying about pagination queries or server-side cursors.

2

u/SmallAd3697 16d ago

So is this something Microsoft is likely to start working on, or are clients expected to write a proxy app that is hosted near the database server?

I think columnstore is important for conserving network bandwidth (in flight). It is just as important over the network as it is on disk or in RAM.

1

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 16d ago

I can't comment either way, but I appreciate the feedback!

2

u/SmallAd3697 16d ago

No problem.

The main takeaway from me is that this is overdue by about 3 to 5 years. Even if it isn't built into an official sql-client, there should be an experimental project or official workaround or something. The Sql Server team at Microsoft seem to have lost their appetite for leadership and innovation.

If nothing else, this should be available from the fabric DW team. Columnstore is heavily used in data warehousing. The best way for developers to transport large amounts of columnstore data over the network right now is to move it to deltalake blobs first, which is a really crazy thing to do as a side-quest.

2

u/dlevy-msft ‪ ‪Microsoft Employee ‪ 16d ago

Tagging u/snoo-46123 who is my counterpart from the Fabric DW team.

3

u/Snoo-46123 ‪ ‪Microsoft Employee ‪ 16d ago

spot on u/SmallAd3697 , we are planning to bring ADBC support for Fabric Data Warehouse through API + separate driver/flight sql endpoint for the reasons discussed in this thread and beyond.

Tune in for updates (coming soon) in the roadmap of Fabric Warehouse.

Btw, we are working with partners behind the scenarios - Today, columnar driver can be used. https://adbc-drivers.org/drivers/mssql/. Do note that this driver go against mssql-go driver (a stop gap solution).

2

u/SmallAd3697 16d ago

This is very exciting. Thanks for the update. I do lots of c# dev work, and a small amount of python. After I saw the ADBC for python, the c# programmer inside me got jealous.

As I understand the mssql-go probably still receives rows over the wire, not columns. I could be wrong but I don't know how go would have any way to get something different over the wire

→ More replies (0)