Yes, you will need to create the jobs on any other replicas that you would want those specific jobs to run if they were the primary replica.
You will need to create your own logic for if/when each SQL Server Agent job will run. For instance, do you want to run a job only if the current instance is the primary replica of a particular Availability Group? You will need to put that into your job. It can't be blanketed automatically, because that would take away the flexibility of AlwaysOn AG. Whether you want them disabled on the secondary replica(s) is completely up to you, what those jobs do, and how/when/if you want them to run.
Remember, the secondary replica server isn't just a stand-by server waiting for failover. It could be a fully functional, accessible server. Because of this, having every job sitting by idle would be a huge disability.
So, yes, you will need to push our your jobs to other replicas and use some logic as to if the job should continue execution when it kicks off.
For instance, backup jobs can take advantage of the sys.fn_hadr_backup_is_preferred_replica function by determining whether the current replica is the preferred one for a particular database. This will derive how you have your Availability Group setup for backup preferences.
I don't know if this is still an issue for you, or if you've found a solution, but in case you haven't, a very powerful set of tools, and one specific to your issue, can be obtained from: https://dbatools.io. Specific to your issue, use "Copy-SqlLogin" and configure it as a scheduled task on each server (primary and all secondaries) in whatever configuration that will work for your needs. I used this solution at the last place I worked and scheduled it to run every hour, and never had an issue with logins after implementation.
If, for some reason, you cannot utilize dbatools, you will need to use sp_help_revlogin
, devise some method to pick out the logins you want to "replicate" (store in a table??) and then execute the IF NOT EXISTS...CREATE LOGIN...
on all relevant instances, which will have to be hard-coded. So, this second method is only slightly better than manual intervention. However, assuming DBAs are the only ones who can create logins, it would be easy enough to make a procedure for all DBAs to always add a new login to this process whenever a new one is created. One thing to keep in mind, wherever the login is obtained from, the User level permissions will be carried over to all replicas, so make sure the "source" login/user is configured correctly before implementing any sync solution.
IMHO, logins and SQL Agent jobs are the only thing lacking from AlwaysOn, and perhaps in a future release, Microsoft will add some automatic functionality--especially for logins, since it really breaks the whole purpose of automatic failover if the logins aren't sync'd.
Best Answer
I have to credit David Browne for this one (I learned from one of his recent answers), but one thing you can do is setup a Linked Server between the your Primary and Secondary Replicas. Then you can create a SQL Agent Job on the Primary Replica that generates the scripts for the Logins and other Jobs and executes them on the Secondary via the Linked Server. You can even have the Job synchronize itself to the other servers as well. It of course should first check if the objects you're synchronizing already exist and take the appropriate action when they do.
You might find this article Synchronize logins between Availability replicas in SQL Server Always On Availability Groups helpful, especially the Use stored procedure sp_help_revlogin and sp_hexadecimal (which references this Microsoft BOL) section which discusses a Microsoft stored procedure you can utilize to transfer Logins between servers as well. You probably can simplify the job above by having it leverage this procedure.
As far as synchronizing the other Jobs, you might be able to do so with the meta-data stored in the the
msdb
Jobs tables, e.g.sysjobs
, or this StackOverflow answer seems to have found a way via T-SQL. You might also find the Microsoft article Sync Logins and Jobs helpful as well.Finally, depending on how many Secondary Replicas you are planning to setup, or if you have an affinity to SSIS, you might find it cleaner and more manageable to create an SSIS Package that does all of this (which is also discussed in the first article above) as opposed to using Linked Servers.
Either way, this is a moderate difficulty task to setup, but definitely accomplishable once you start diving into it.