1

This is my first question, hope I do it right!

I am working on creating a process that will sync server objects between our AG nodes. We are using SQL Server 2016 with 4 nodes. Two nodes in one data center and two nodes in a different data center. I am currently using PowerShell to copy my logins, jobs, SSIS catalog, operators, and linked servers. I want to automate deploying changes made to these objects across all four nodes. Is there a tool made for this already?

Improve this question
3
  • One question I'd have is, are the jobs different on the primary vs secondary? From your question I'd assume not, but it's not uncommon to have certain tasks running on one node and others on another. i.e. backups. Also, depending on if the secondary is writable, some tasks couldn't run
    – S3S
    Commented Oct 30, 2018 at 20:47
  • Secondaries are read only. Jobs that have to server specific will have to be excluded and updated manually, I just can not picture a way around that. We have a lot of data developers and analysts making new jobs and changing old jobs. This is for user jobs only, system jobs will/should remain fairly static. Thank you for asking.
    – BDA the DBA
    Commented Oct 30, 2018 at 20:55
  • 1
    As a rule of thumb, make all the jobs identical on all nodes. As for backups and database maintenance, use Ola Hallengren's scripts, they'll automatically determine which node to run log/full/differential backups on.
    – Daniel Hutmacher
    Commented Oct 30, 2018 at 20:58

1 Answer 1

Reset to default
3

If you're already comfortable with Powershell, check out dbatools.io, which I think provides many of the functionalities you're looking for.

Some time ago, I wrote a hack that synchronizes logins using linked servers, which you may find helpful.

Regardless of what tool you choose, I would create an Agent job that runs on all nodes and checks if it's running on a primary or secondary. If it's on the secondary, it can pull the latest version from the primary.

Improve this answer
1
  • I use the DBATOOLS.IO alot for these manual copies, those modules are wonderful. Thank you for the hack link as well. I have a job that runs on all secondary nodes currently that will turn jobs off and on based upon whether the corresponding db's ag is primary or not. Works pretty slick. I need something that write the changes made to the primary objects. I.e. It wont matter if the ag fails over and the job kicks off like it is supposed to, alas the SSIS packages is not updated! Thank you Daniel for you're help
    – BDA the DBA
    Commented Oct 30, 2018 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.