Expand MSX/TSX Capabilities for HADR
Currently, SQL Server does not have proper built-in capabilities for synchronizing server-level objects, which should be something necessary in High Availability and Disaster Recovery architectures.
The MSX/TSX capability, for example, is theoretically capable of synchronizing SQL Agent jobs from one master server to other target servers. However, it's useless in a two-server architecture (primary-secondary) where BOTH servers should be able to run the same SQL Agent jobs based on the relevant database role, because a Master server cannot be also a Target server.
The issue gets even more complicated when additional server-level objects should also be synchronized:
SQL Agent Operators, Alerts, Logins, Server Triggers, Database Mail settings, and more.
An example workaround is to use DbaTools cmdlets such as Sync-DbaAvailabilityGroup:
Also, to control the status of which jobs should be enabled or disabled (based on the relevant database role), i.e. a job-level fail-over mechanism - a possible workaround is to use the solution available from Madeira Data Solutions:
The MSX/TSX capability seems like a reasonable step in the right direction, but it's only limited to SQL Agent Jobs, and it's useless unless you have an additional SQL Server dedicated to being a Master server... Which can be quite wasteful considering the licensing limitations (only Standard or Enterprise edition servers can be Master servers).
In short, my suggestion can be summarized as such:
- Remove the Edition limitation for Master servers in the MSX/TSX feature. Or:
- Allow a Master server to also be a Target server, so that it could also execute its MSX/TSX jobs as well.
- Expand the MSX/TSX capabilities to other server-level objects as well. See DbaTools' Sync-DbaAvailabilityGroup for reference.
- Implement a fail-over mechanism for SQL Agent jobs as well (based on Availability Group role). See Madeira Data Solution's mssql-jobs-hadr for reference.
Eitan Blumin commented
I think it's up to Microsoft to decide what would be the correct course of action for them.
I believe you'll agree with me that it doesn't really matter how such a feature would be called and whether it would be an expanded version of an existing feature or an entirely new feature.
What matters is that there should be a suitable solution for HA/DR scenarios.
Guy Glantser commented
I definitely think that it's about time Microsoft implements a built-in solution for synchronizing all instance-level objects between servers. There are so many use cases for that.
But I don't think it should be based on the MSX/TSX functionality. This is meant for a different use case, and it's not appropriate for the HA/DR use case. Of course, it is possible to change it and adjust it to fit the HA/DR use case, just like you wrote, but it would be like taking a fork and making adjustments to it so you can eat soup with it. A much better solution would be to invent the spoon.
Eitan Blumin commented
That's very similar to the solution that I developed here, Randy:
The idea behind this feature request, though, is that such a solution should be something important enough to warrant a built-in implementation from Microsoft.
Randy Pitkin commented
The only object that needs to know about the the status of the cluster is the job itself. Deployments need to ask the cluster where to make changes and the Jobs just need to know which node to run on. I have a SQL Agent job "HADRSync Kit" that disables/enables the schedules of jobs based on the status of the node in the cluster. The reason to disable the Schedule, is so that someone with less knowledge may feel the need to disable the job. This throws an alert that the job is configured differently within the cluster and DBA(Human) intervention is required.