Add Multiserver Job support to DbaAgentJob commands

OsirisDBA commented 1 year ago

Summarize Functionality

Multiserver job administration has existed in SQL Server since at least v7. It's still included in the SMO ( see ApplyToTargetServer and SQL 2022 documentation ( see sp_add_jobserver

Having a multiserver job setup is useful for on-premise environments with many instances. For example, they can be used for centralized management of jobs having one of the instances act as the master (MSX) instance and the others as target (TSX) instances. Updates are deployed to the MSX instance and automatically copied to the targets. No need to use Copy-DBAAgentJob or update the job on all instances.

There are some downsides to multiserver jobs.

The MSX server cannot deploy jobs to itself. This precludes having one side of an Always-On environment be the MSX server and another be the TSX server because the jobs would need to be duplicated anyway ( local and multiserver )
In order to use proxies, a registry value needs changed to allow matching proxies to be used.
The owner of the job must be a sysadmin on the msx server. The matching login on the target server does not have to be a sysadmin.

Multiserver jobs are not supported on Azure Managed Instance.

Links:

Is there a command that is similiar or close to what you are looking for?

No

Technical Details

This will require several new commands and modifications to existing commands. Not everything would need to be done at once though.

Microsoft uses both master/target and MSX/TSX nomenclature. I'm using the latter for hopefully obvious reasons.

Updates to existing commands:

New-DbaAgentJob - New optional parameter TargetServers containing a list of TSX servers to deploy this job on. Currently this is explicitly set to (local) which should be the new default.
Remove-DbaAgentJob - Will error if run against a multiserver job on an environment except the originating server. May want to catch this
Set-DbaAgentJob - No changes. Use Add/Remove-DbaAgentTsxServer from below to modify targets
Copy-DBAAgentJob - This requires a decision. Either it needs to refuse to copy jobs that have been deployed to the instance, it needs to contact the MSX server and add set the job to deploy to the new instance (if it's an existing target server also), or it could just copy the job as a local job.

New Commands:

Add-DbaAgentTsxServer - Adds a target to an existing MSX environment.
New-DbaAgentMsxServer - Promotes an agent server to a MSX server.
Get-DbaAgentMSXTargets - Returns the set of targets for the MSX server.
Add-DbaAgentJobTarget - Adds a target server to an existing job
Remove-DbaAgentJobTarget - Removes a target server from an existing job

wsmelton commented 1 year ago

We do not see a push from community at this time to implement support. If that comes around with more desire we can look at adding it. The complexity and "troubles" around the use of it in various configurations is generally why I never see it used in customer environments.

It would be a significant amount of code to maintain.

OsirisDBA commented 1 year ago

Would you consider it if I submitted the PRs?

wsmelton commented 1 year ago

If only one user wants them? That's a lot of code for us to maintain.

This feature is also something we likely can't automatically run tests for either in Appeyor.

mike-hodgson commented 1 year ago

Would you consider it if I submitted the PRs?

You could always fork the repo and write your own additions to your fork of it. That way you get the feature(s) you want, you write the code yourself (which you were offering to do anyway), and Shawn & the dbatools team don't have to maintain the code.

OsirisDBA commented 1 year ago

Agreed, it's a little annoying to get a flat no with no discussion though within a couple hours of submission. At least he added some reasoning to his edited comment after I'd already responded.

On Wed, Aug 16, 2023, 6:16 PM Mike Hodgson @.***> wrote:

Would you consider it if I submitted the PRs?

You could always fork the repo and write your own additions to your fork of it. That way you get the feature(s) you want, you write the code yourself (which you were offering to do anyway), and Shawn & the dbatools team don't have to maintain the code.

— Reply to this email directly, view it on GitHub https://github.com/dataplat/dbatools/issues/9061#issuecomment-1681385983, or unsubscribe https://github.com/notifications/unsubscribe-auth/AGCQKJAYQ33BAI27O22CFPTXVVIC3ANCNFSM6AAAAAA3S3JXTE . You are receiving this because you authored the thread.Message ID: @.***>

wsmelton commented 1 year ago

Please do not make up statements that I never made.

As mentioned already, if the community sees interest in this feature then anyone is more than welcome to contribute. The final say of whether we want to include it with little to no interest is up to the owner of the project.

In my view, unless more users come to need/want this in their automation and we have contributors that will fully agree to maintain it going forward then I'm okay. We have had code contributed to this project before for SSIS. Those contributors left and stopped providing support. Users suffered because no one was around to provide support and fix bugs. We had to remove the commands because we as maintainers do not use every feature or component in SQL Server. The size and user base this project has acquired requires that we be cautious and make every consideration of whether commands should be added or not.

dataplat / dbatools