Batch initializing availability database objects

[aguzev]

The task is to enumerate all availability databases in all availability groups on a server. SMO executes a query for each availability database in each AG. The number of trips to the SQL Server grows with the number of databases.

Is it possible to configure SMO to get the data as a single batch with one or a couple of queries?

[shueybubbles]

@aguzev what does the naive code loop look like?

[aguzev]

The pseudocode:

foreach(var ag in server.AvailabilityGroups)
    foreach(var state in ag.DatabaseReplicaStates)
    {
        // do something…
    }

We've found the following solution. Is it the right one?

var queryObj = new SfcObjectQuery(server);
var sfcQuery = new SfcQueryExpression("Server/AvailabilityGroup/DatabaseReplicaState");
foreach(var state in queryObj.ExecuteIterator(sfcQuery, fields, null))
{
    // do something…
}

[shueybubbles]

it looks reasonable to me, though I think you can use a Request object and server.ExecutionManager.GetEnumeratorData to get a data table.