Recommended methods for login and job sync with AlwaysOn

  • Hey all,

    I've seen a handful of posts, and even a couple built and ready to go SSIS packages; however curious what you all here have been doing to keep your logins and jobs in sync between your replicas? Working through some options and wasn't sure if there was a go to method out there there days.

    Thanks

    (Perry, I'm sure you'll be my response, so thanks in advance!)

  • Adam Bean (3/30/2015)


    (Perry, I'm sure you'll be my response, so thanks in advance!)

    😉

    Adam Bean (3/30/2015)


    Hey all,

    I've seen a handful of posts, and even a couple built and ready to go SSIS packages; however curious what you all here have been doing to keep your logins and jobs in sync between your replicas? Working through some options and wasn't sure if there was a go to method out there there days.

    Thanks

    Sure you can use the SSIS transfer logins task but I find it a bit clunky, it tends to fail for varying different reasons. I prefer to dump the logins out to a file which is then imported using my own logic into the target system. Like all things there's more than one way to achieve this, pick the best that works for you

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Any recommendations for anything prebuilt that handles jobs and security? Time is not on my side to develop and test vs. implementing something already tried and true.

    Thanks

  • Came across SQLSkill's SSMS plugin (https://www.sqlskills.com/blogs/jonathan/synchronize-availability-group-logins-and-jobs/), sadly it only works in 2012 and is ad-hoc only. Great idea nonetheless.

    Still looking for something at this time.

    Thanks!

  • Scripting the logins is an easy task there are unlimited scripts around. You can also script any jobs from msdb too, you'll likely find many scripts for this too.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (4/3/2015)


    Scripting the logins is an easy task there are unlimited scripts around. You can also script any jobs from msdb too, you'll likely find many scripts for this too.

    Agreed, this is definitely the easier of the two and I've done it quite a few times. The jobs on the other hand, that I haven't toyed with yet.

  • I've gone through this recently. You would think that the SQL team would have developed an easy and automated way to do this. Unfortunately, that hasn't happened yet.

    I use SSIS to do all of this. It just requires a lot of testing. I had to create a manual script for stored procedures. The big thing to remember is that you don't want any transferred jobs to run. This means you can do one of a few things; you can disable the secondary node's agent (which I don't like); you can script them out and disable them on the secondary node through the job transfer feature in ssis (which I also don't like); or you can add an initial step to every job that checks to see which node it's on before it's run. The last one is the one that I do, but I'm not a huge fan of that one either.

    I'm hoping for more robust AG settings, which does all of this for you at some point.

  • You can sync logins across all secondary replicas in real time. Please check this post

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply