Following my presentation of “How to HADR Your SQL Server Jobs” at the HA/DR PASS Virtual Group yesterday, David Klee (@kleegeek) and I continued chatting for a bit longer after the recording was concluded. During which, the MSX/TSX feature of SQL Server came up, and David suggested that I’d utilize my newly earned MVP status to push a feature request to expand these capabilities.
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 issue gets even more complicated when additional server-level objects also need to 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 Master Control Job solution that I developed.
The MSX/TSX feature (Multiserver Environment) seems at first like a reasonable step in the right direction, but it’s only limited to SQL Agent Jobs, and it also has one glaring problem:
A Master server cannot also be a Target server. Which, in other words, means that while you can use one Master server to synchronize a list of jobs to other Target server(s), the same jobs cannot also be executed on the same Master server as well.
Which makes this feature utterly useless unless you have a SQL Server instance specifically dedicated to being a Master server… Which can be quite wasteful considering the licensing costs (only Standard or Enterprise Edition servers can be Master servers).
In short, my feature request can be summarized as such:
- Remove the Edition limitation for Master servers in the MSX/TSX feature (so that we could use free or cheaper editions for that). Or:
- Allow a Master server to also be a Target server, so that it could also execute its MSX/TSX jobs as well (IMO this is the better option).
- 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.
I considered suggesting the same idea for Database Mirroring as well, but decided to ignore that in my feature request because Database Mirroring is already deprecated.
Anyways, go vote now and share this around:
https://feedback.azure.com/d365community/idea/f340ed23-5025-ec11-b6e6-000d3a4f0da0
Thanks!