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?
Best Answer
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.