The next order of business is to hand out presents. As per Chris’s request, my present is for myself and for the community at large. For my contribution to this month’s T-SQL Tuesday, I am going to talk about free SSIS/job servers for Availability Groups. I have in fact proposed this to the product team, and I hope that one day my dream will come true.One of the pain points of working with Availability Groups is how to handle SQL jobs and SSIS packages that need to run against databases in an AG (applies equally to database mirroring, log shipping, or replication, but for the sake of brevity, let’s stick to the context of an AG). I’ve set up processes in the past that detected failover and enabled/disabled jobs as needed. I have also set up jobs that did nothing but check to see if a database was online on that server, and if so, then execute the real job. There’s a better way though.
My preferred way of handling this is to use a dedicated job server that hosts that database engine (for executed SQL jobs) and SSIS (duh! for SSIS packages) and nothing else. SSIS packages or executables that need to connect to a database in an AG simply needs to use the Listener for connecting to the AG. The job doesn’t care and doesn’t need to know which node is active. T-SQL job steps are a little trickier because you don’t have the option to specify a server to run against, but that is easily dealt with by changing the step to a PowerShell step and using invoke-sqlcmd to run the T-SQL queries and commands against a remote AG.
It works very well so why isn’t everybody already using it? Because it is expensive. You have an additional SQL Server that needs to be licensed. It doesn’t have to be nearly as many cores as the server where the queries actually run, but it’s still not free. It has to be licensed for at least 4 cores (more if your workload needs more).
My proposal to the SQL product team was to allow us to have a dedicated job server running only SQL jobs and SSIS that would be covered by the licenses of the AG servers to which they connect. In other words, as long as they only run SSIS and SQL jobs, and they only connect to fully licensed servers, they do not need additional licenses.
There would have to be very strict rules for when the server qualifies for no additional licensing, but I believe it would really make adoption of Availability Groups much easier by curing one of the big pain points.