Sql Management Objects async overloads

[MgSam]

(Please let me know if this is the wrong repo)

I was pleasantly surprised to find SMO is staying up-to-date with the latest features of SQL Server and is available on .NET Core.

One area in which its sorely lacking, however is in async support. async/await has been in the C# language since 2012; Async overloads are crucial in nearly every framework doing I/O in .NET.

SMO should add Async overloads for any method call that results in a call to the database. This is important so that a thread is not just sitting there, blocked and idle, while waiting on an external operation.

[shueybubbles]

We could implement a small set of async or at least AsyncCallback-based methods for ServerConnection and Database/ExecutionManager etc. These would be only the methods that explicitly make database queries, such as Database.ExecuteWithResults etc. However, the object model in SMO treats database queries as an implementation detail such that referencing any property on a SMO object may or may not trigger a query, it depends on the property access pattern of the application. Any application that wants to guarantee all SMO-initiated DB queries happen on a background task will need to explicitly perform any initialization of SMO collections and SMO objects on a background task itself.

[Pilchard123]

I realise I'm bumping a three- or four-year issue, but is there any news on this?

[shueybubbles]

There are no plans to change the SMO model to be anything but synchronous. The abstraction in the object model is meant to hide database queries from the caller.

[MgSam]

There are no plans to change the SMO model to be anything but synchronous. The abstraction in the object model is meant to hide database queries from the caller.

Frankly, what you're saying sounds like you just came out of a time machine from 2002. The abstraction of pretending complex external I/O can run via synchronous calls is extremely dated and is considered a worst practice. This was first recognized all the way back in 2003 with the first attempt at asynchrony in .NET, IAsyncResult, which was released with .NET 1.1.

That said, async/await has been around in C# for a decade (since 2011). It is widely recognized in the .NET ecosystem that I/O with an external system should be represented with asynchronous (task-based) operations so you don't have threads sitting waiting around doing nothing.

I understand you guys don't have the resources to fix SMO and make it into a modern, well-designed API and that without substantial customer demand that is unlikely to change. But let's not muddy the waters by claiming SMO's API is just fine as-is, when by any modern .NET standard it is a dinosaur.

[Piedone]

I'm baffled as well. I can understand that it's not a priority right now but unless SMO is or will be deprecated, providing async APIs must eventually be done.

Perhaps if the project would be open-source, people would even contribute this?

[Pilchard123]

There are methods that explicitly say they're for making queries: Database.ExecuteWithResults and Database.ExecuteNonQuery. I don't see how you can hide the fact that a method that is for making database queries, that you pass a database query, and (in the case of ExecuteWithResults) gives you the result of making that database query... makes a database query. It's like trying to hide the fact that a sack labelled "bananas" and that you just put some bananas into contains bananas.

[dzsquared]

Hi folks! I'm glad to see interest in SMO.

Just a reminder that this repo has adopted the Microsoft Open Source Code of Conduct

In particular, I'd like to encourage this conversation to continue while being mindful of the standards:

Examples of behavior that contributes to a positive environment for our community include:.
Demonstrating empathy and kindness toward other people
Being respectful of differing opinions, viewpoints, and experiences
Giving and gracefully accepting constructive feedback
Accepting responsibility and apologizing to those affected by our mistakes, and learning from the experience
Focusing on what is best not just for us as individuals, but for the overall community

[dzsquared]

Sam (@MgSam) Zoltán Lehóczky (@Piedone) Richard Ward (@Pilchard123)
So I can better understand and advocate for async support within SMO, can you elaborate a bit on your specific scenarios and the utilization of SMO where it would be beneficial to have async/callback support?

[Piedone]

We're using SMO during UI testing (with our Lombiq UI Testing Toolbox).

Here, multiple instances of the same (ASP.NET Core) app are executed on the same machine (sometimes dozens) while Selenium tests click through them. The apps connect to their own SQL Server DBs on a local SQL Server instance. SMO is used to create and drop databases, as well as creating backups of them in case of a test failure (for troubleshooting) or just to snapshot the app's state. Conversely, backups are restored to restore these snapshots.

All this means is that usually we do the following hundreds of times for a given test run:

• Create DB
• Drop DB
• Restore DB

With some backup DB sprinkled in.

See the class where all this happens.

[Pilchard123]

At the moment I have nothing that uses it in production, I'm just prototyping, but I do have a likely future use-case where during the deployment of an application I will have to run a number of scripts (commonly hundreds, sometimes thousands) containing batch separators on a target database or databases. Currently it's done with a .BAT file that runs sqlcmd repeatedly, once for each script. It's not a dreadful solution, as ad-hoc solutions go, but it does require that it's run from a device that has sqlcmd installed. The team that uses and develops the tool are also much more at home in .NET so it would be good to be able to work in .NET if possible.

The lack of async isn't a deal-breaker, but it would mean that it would be cleaner to (for example) run the scripts on multiple databases at the same time, or be copying new application files around at the same time as running the scripts.

In the future we might want to use it for backing up/restoring databases before the deployment, or restoring a blank database if one doesn't exist.

[mdsharpe]

2026 and still no async methods on SMO! 😲

[shueybubbles]

I'd be happy to unleash an AI model on the problem - what I need from the community is a clearer specification than just "you need async!". Please contribute some code snippets showing how you'd like the public interface to be revised. Are folks just asking for the highest level wrapper objects like Scripter and ScriptMaker and RestorePlan to offer asynchronous methods while the underlying SFC objects like Request and Enumerator remain synchronous? Do you want asynchronous collection initialization like Database.Tables.InitializeAsync() which would fully populate the Table objects in the collection while keeping the default enumeration of collections synchronous?

[mdsharpe]

I can't speak for anyone else, but I ended up here after finding that ServerConnection.ExecuteNonQuery has no async equivalent.