[New-Command] Request: AlwaysOn Related #755

forensicsguy20012004 opened this Issue Feb 16, 2017 · 14 comments


None yet

5 participants


Is this a feature OR bug:


AlwaysOn related request.....pass in a CNO and depending on switches return the following

  1. Number of AGs associated to the CNO
  2. Number [count] of databases per AG
  3. Total size of all databases per AG

System Details

  • Operating system name and version:
  • Output from $PSVersionTable:
Evaluate $PSVersionTable in PowerShell and paste the output here
  • Output of dbatools version:
Evaluate (Get-Module dbatools -ListAvailable).Version and paste output here
  • SQL Server version for source/target

Steps to Reproduce

Action Results

Expected Results

Attached Logs or any exception errors

  • Use of Start-Transcript can help collection of console output and exceptions
  • Certain commands will generate an exception log that you can find in following directory: dir $env:USERPROFILE\Documents\dbatools-exceptions.txt
wsmelton commented Feb 16, 2017 edited

So what is the difference between passing in the CNO compared to passing in the instance (SQL Server) name that is running on that cluster? We already have Get-DbaAvailabilityGroup for that purpose.

(1) - Number of AGs associated to the CNO
Not sure this will be fair to acquire being that all you capture reading the cluster is the role name. There is nothing from the cluster side that states that role is associated to an AG itself.


In a large, large enterprise where you have over 15,000 CNO(s) it is tough to go and get the AGs for every single CNO...so for all us with very large enterprises we need more things that are descriptive at a higher level for management. Agree or disagree...but I am thinking on a much larger scale.

wsmelton commented Feb 16, 2017 edited

Ok so the ResourceType does give that it is a "SQL Server Availability Group" (at least using Get-ClusterResource). So there are technically already commands that could be used with the cluster module to pull what you are asking for (1).

$cnos = 'MyCluster1','MyCluster2'
foreach ($c in $cnos) { 
$cl = Get-Cluster $c; Get-ClusterResource -InputObject $cl | 
 where ResourceType -eq "SQL Server Availability Group" | 
 select cluster | Measure-Object -Property Cluster}


So in that sense I don't think it would be prudent to try and write something else in dbatools module, that a built-in module already covers. There are ways to likely use WMI or CIM to get the information as well but it is much more code to maintain compared to using the cluster module.

Now for (2) and (3) those are doable in the sense you use the above code snippet to pull the cluster nodes that do have the AGs. That is piped to another command that pulls the AGs and grabs the list of databases involved...piping to a second command that gets the database information (name, size, etc.).


@Stuart-Moore submitted a command that had some basic cluster info, but I saw the need for a really robust cluster object (think what you see when you see an SMO Server object) so we decided to return to it in the future. If grabbing "SQL Server Availability Group" is doable, perhaps that is a property that we can add. I'm sucky with AGs and so don't have too much to offer other than that.



I am game for whatever the team would like to do...It was just a suggestion...let me know...


@wsmelton I am cool with not doing anything for Point 1, but it would be great if we can move forward on points two and points three. If we get something up and running I would be more than happy to test and validate.


@forensicsguy20012004 when are you asking for size do you mean the total size of the database or the size detail at the file level (e.g. Get-DbaDatabaseFreeSpace )


[Just moving the conversation from Slack to this thread...]

My first thought would be to simply add 2 properties to the Get-DbaAvailabilityGroup command. Something like DatabaseCount and DatabaseNames, the second property would contain an array value of the names in that AG.

The purpose of the array of database names should then allow you to pipe that to Get-DbaDatabase (which would need to be updated to add the size property) to get the desired information.

The longer way would be creating a Get-DbaAvailabiiltyGroupDatabase (way to long of a command name to me) that pulls the details of the database as it relates to an AG (e.g. sync status, etc.) There is an SMO object for the AG that contains database properties specific to an AG. It just does not include the "size", as I feel that belongs in the Get-DbaDatabase command.

Thoughts @ctrlb or @powerdbaklaas


@wsmelton I think it would be better to have one function that gets the AG, which you already made, and another one to get the DB in an AG, which we may have too if there's a possibility to expand Get-DbaDatabase so it can accept -AG in a parameter set next to accepting -SqlInstance.

Then we could:
Get-DbaAvailabilityGroup $CNO | Get-DbaDatabase | Get-DbaDatabaseFreespace | Group-Object -Property server | select name, count, @{l='totalsize';e={($_.group | measure -Property filesizemb -sum).sum}}

If that's not possible, we could indeed have a new Get-DbaAGDatabase, but I would avoid that, since it's a variation on Get-DbaDatabase we should be able to implement there.


@wsmelton and @PowerDBAKlaas ... both of you thank you for continuing to look into this request...much appreciated. @wsmelton to answer your question...I was not thinking about total size on disk [Get-DbaDatabaseFreeSpace]...but more along the lines of this concept:

4 AGs associated to this CNO
AG 1 has a total of 500GB associated to it [add up the 'size' of each DB in that AG]
AG 2 has a total of 700GB associated to it [add up the 'size' of each DB in that AG]
AG 3 has a total of 100GB associated to it [add up the 'size' of each DB in that AG]
AG 4 has a total of 45GB associated to it [add up the 'size' of each DB in that AG]

4 AGs associated to this CNO
AG 1 has a total 1 DBs
AG 2 has a total 96 DBs
AG 3 has a total 50 DBs
AG 4 has a total 7 DBs

So my thought for all of this has to do with auto-provisioning. Some, enterprises may only want to add a new DB to an AG if the AG contains less then X DBs or they may only want to provision a DB to an AG if it has a total of XXXGB or less. There is where my thought process was coming from. It would help DBAs and DBA managers properly size, place, and allocated resources. However, they cannot do that if they do not have these very level numbers that are tracked. I hope all of this makes sense to some extent.....Let me know.....Thanks...Matt


Well, function output should really be an object, like:

CNO AG DBSize DBcount

CNO1 AG1 500 GB 1
CNO1 AG2 700 GB 96

I think the calculated properties are still covered with what I wrote above, but there may be a foreach and / or an -expandproperty necessary.

What do you mean by total DB Size if not the size on disk?


That is fine...size on disk works. I was thinking that if you go into ssms and click properties and look at the size of the DB. That is what I 2 as thinking. ..but size on disk is perfect.


@forensicsguy20012004 Overall, that type of output is local to what you need. The output we would work on would not give that exact layout or values, but you can use PowerShell to get it.

I think it will be best to do a Get-DbaAvailabilityGroupDb command (or similar name). This would be the command to get the database details around the AG specific properties. We have Get-DbaDatabase that could get the size of each database. We just need to make sure all these commands play together nice, or just see how we can make it the easiest to get all of it together...at least initial thought.


Yeah, that is cool. Let's see if we can string together existing commands to come up with something, no need to reinvent the wheel. I think bundling all this together in one nice and concise command would be awesome

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment