Distributed AGs

[tboggiano]

Summary of new feature

Commands to support setting up distributed AGs.

• Clear/concise description of what the problem is that the new feature can solve. Setting up distributed ags.

Proposed technical details (if applicable)

I have all the commands written without dbatools and would like to add this to dbatools.

• e.g. Blog post reference that shows example code or functionality

Latest version of dbatools as of writing

• Please provide the latest released version of dbatools module.

[wsmelton]

What commands would have to be added that we don't have now?

Couldn't logic just be added to our current commands?

[tboggiano]

That may be possible and definitely use as much of what is available as possible.

[tboggiano]

New-DbaAvailabilityGroup can used to create the AG that on distributed AG servers with no option to do restores. I don't know how to force someone to know they are creating on for a distributed AG and don't need to restores without a separate function.

Then you need a similar function or logic that gets the PrimaryLIstener and ForwarderListner to create the distributed ag instead of use the Primary and Secondary server names and creates the AG with WITH (DISTRIBUTED) in the T-SQL unless I can find in it SMO, this would be whole other section in the code.

Inside of that function in needs to join the ForwarderListener to the PrimaryListener just like you do with the Secondary to the Primary. The rest of the options as far as seeding and restores of the databases are the same. You will need credentials that can access the end points on both clusters.

So if we reuse these variables: #Variables to be reused: [DbaInstanceParameter]$Primary, #Primary Dag Replica [PSCredential]$PrimarySqlCredential, [DbaInstanceParameter[]]$Secondary, #Secondary Dag Replicas [PSCredential]$SecondarySqlCredential,

and several under #Replica

#Ag $Name # for the Distributed AG Name or add an Alias for $DagName

Other variables needed in New-DbaAvailabilityGroup when it gets called the second time is: #Dag [string] $PrimaryListener, [string] $ForwarderListener, [switch] $Distributed = $false #set this up as $false by default set it to $true when setting up distributed ags

And logic will need to be added to grant service accounts access to the endpoints across both ags to the endpoints.

So in the end this function New-DbaAvailabilityGroup could be exapnded to include distrbuted ags but I believe it might become a bit hard to maintain and it will need to call itself at one point making a head twister. So I would like to propose New-DbaDistributedAvailabilityGroup.

Then we need to probably create and Failover function for the distributed AGs as the steps are slightly different there as well, and would keep it less confusing I believe as separate functions.

But open to hear feedback.

[wsmelton]

New-DbaAvailabilityGroup can used to create the AG that on distributed AG servers with no option to do restores. I don't know how to force someone to know they are creating on for a distributed AG and don't need to restores without a separate function.

This would be handled with parameter validations. We have a switch like -IsDistributed or something and if that is passed in then you'd do parameter validation and make sure the unsupported parameters are not included. If they are fail out the function call.

[wsmelton]

Alternative is creating a command Invoke-DbaDistributedAg and have that simply call the adjusted New-DbaAvailabilityGroup as many times with the appropriate parameters for each operation.

And logic will need to be added to grant service accounts access to the endpoints across both ags to the endpoints.

We already have commands for managing endpoints.

Then we need to probably create and Failover function for the distributed AGs as the steps are slightly different there as well, and would keep it less confusing I believe as separate functions.

We don't have failover function for AGs, it is difficult to validate the failover processing with AGs to safely do a failover.

$Name # for the Distributed AG Name or add an Alias for $DagName

😁 we can just keep it as Name and update help to show it works for either one.

Other variables needed in New-DbaAvailabilityGroup when it gets called the second time is: #Dag [string] $PrimaryListener, [string] $ForwarderListener, [switch] $Distributed = $false #set this up as $false by default set it to $true when setting up distributed ags

I would say we add an advanced parameter where they pass in a hashtable of these configurations. Being that Distributed AGs are an advanced configuration anyway. Then your Invoke command can handle whatever list of parameters you want/need. This will prevent us from having to extend the current parameter list out to wildly. We have some other functions that do this type of thing I recall. DistributedConfig or something of that name then simply pass in a hash, and document in help what you require that to look like.

[tboggiano]

There is Invoke-DbaAgFailover so we could edit it for the failover the code in there as far more simpler than the above function. I've got queries you run to validate you can do the failover for the distributed ag.

[tboggiano]

So if you have 4 servers, 2 on each side, and each has a different services account, how does assigning the endpoint permissions happen. I'm not seeing how you would end up with different accounts with access, or does that have to be a separate step. Especially with two separate calls to the same procedure.

[tboggiano]

I'm back to looking at this here is code that would setup a distributed ag with the last one being in theory and somehow needing to retrieve backups from AG1 to restore to AG2.

Create endpoints

New-DbaEndpoint -SqlInstance Server1, Server2, Server3, Server4 -Type DatabaseMirroring

Grant permissions for all sql server accounts from all servers in distributed AG, note there maybee more server and logins if you

have more than two nores in the AGs

Grant-DbaAgPermissions -SqlInstance Server1, Server2, Server3, Server4 -Login Login1, Login2, Login3, Login4 -Type Endpoint

Create First AG if not already exist

New-DbaAvailabilityGroup -Primary Server1 -Secondary Server2 -Name AG1 -ClusterType Wsfc -IPAddress 10.10.10.10 -Database db1 -UseLastBackup

#Create Listener Add-DbaAgListener -SqlInstance Server1 -AvailabilityGroup AG1 -IPAddress 10.0.20.20 -Listener1

Create Second AG

New-DbaAvailabilityGroup -Primary Server3 -Secondary Server4 -Name AG2 -PrimaryAGServer Server1 -ClusterType Wsfc -IPAddress "10.10.10.10" -SubnetMask "255.255.255.0" -Database db1 -UseLastBackup

#Create Listener Add-DbaAgListener -SqlInstance Server3 -AvailabilityGroup AG2 -IPAddress 10.0.20.21 -Name Listener2

#Create Distrbuted AG New-DbaAvailabilityGroup -Forwarder Server3 -PrimaryListener Listener1 -ForwarnderListener Listener2 -IsDistrbuted

So new parameters for New-DbaAvailabilityGroup would be in the last command, nut anything that is required outside of those we need to look at as not being required.

Comments thoughts appreciated on whether we think those two steps still can be handled in the the same function.

[github-actions[bot]]

🚧🚨 This issue is being marked as stale due to 90 days of inactivity. If you would like this issue to remain open:

• Verify the issue/bug is reproduced in the latest version of the module
• Verify the environmental info provided is still accurate
• Add any additional steps you followed to reproduce if necessary 🚨🚧 ⌛️ This issue will be closed in 30 days ⌛️

[tboggiano]

I'm going to start actively working on this. @andreasjordan I've been told you are to ask questions on AGs.

[andreasjordan]

Yes, I would be happy to help.

[0x7FFFFFFFFFFFFFFF]

I'm going to start actively working on this. @andreasjordan I've been told you are to ask questions on AGs.

Any updates on this one?

[andreasjordan]

No updates.

Please do not add new functionality to the existing AG commands. They are already complex and don't need more if-else logic.

Build the new commands from scratch and test them intensively. We can have a look at them later and see if it makes sense to combine them.