Sync Server Objects on AG

  • RonMexico

    Hall of Fame

    Points: 3340

    After doing some research, I discovered that using Sync-DbaAvailabilityGroup provided by https://dbatools.io/is a popular way to keep jobs, logins, linked servers, configurations, etc synced between nodes in an AG. I must say that I really like the script but the issue I'm having is when it comes to logins or jobs being updated or deleted that these changes are skipped. It will only pick up the new objects. Does anyone have a solution to remove or update objects on the replicas as they happen on the primary?

  • SQL_Hacker

    SSCommitted

    Points: 1648

    Have you read the dbatools documentation for that process? It's here: https://docs.dbatools.io/#Sync-DbaAvailabilityGroup. I've found they give you so many options and have documented it so well that sometimes it's easy to overlook what you're looking for.

    With just a quick scan of the documentation, there are specific "excludes" and the typical "force" that can be used, but I didn't dig into it to find out what they do exactly.

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA

    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • RonMexico

    Hall of Fame

    Points: 3340

    I looked into -force and, in my testing, found that it handles updates within a job so it is an acceptable solution for the update portion. I'm still hoping for something to handle deletes.

  • SQL_Hacker

    SSCommitted

    Points: 1648

    Do you mean deletes within a job, or deleting the whole job. If it's the whole job, I would think -force would work as it drops all objects in the target destination and recreates them. Come to think of it, even if it's a deletion within the job it should work too.

    Perhaps I'm missing what you're referring to when you say "something to handle deletes"?

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA

    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • RonMexico

    Hall of Fame

    Points: 3340

    It seems as though -force still looks to match and, if a match is found, then it drops and re-creates on the destination. It does not delete everything on the secondary ahead of time.

  • email 73321

    SSC Enthusiast

    Points: 104

    The trouble with a delete operation is that if you think in terms of removal, how would one expect to know that an object can be dropped? This can be forced if we know which one is primary and do it manually, but when we use automation, the system doesn't know the item doesn't exist on the primary because the AG just failed over, or due to some other reason (open transaction, inaccessible, etc). Personally, we just set up jobs to notify us when an item doesn't exist on the secondaries and does on the primary so it can be reviewed manually. Otherwise, it's a trivial matter to utilize a couple of the other scripts in the dbtools set to remove items.

Viewing 6 posts - 1 through 6 (of 6 total)

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