Post reply

Availability Groups - Object Sync

  • July 13, 2021 at 11:35 am

    #3907336

    Hi,

    I'm in the process of doing a comparison of all the options available:

    Scripts

    Integration Services

    3rd Party Tools

    We will have hundreds of instances and thousands of databases so need something scalable!

    Thanks

  • July 14, 2021 at 12:10 pm

    #3907777

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • July 14, 2021 at 12:41 pm

    #3907811

    What is your question?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • July 14, 2021 at 3:11 pm

    #3907875

    lanky_doodle wrote:

    Hi,

    I'm in the process of doing a comparison of all the options available:

    Scripts Integration Services 3rd Party Tools

    We will have hundreds of instances and thousands of databases so need something scalable!

    Thanks

    "All the options available" for doing what specifically??  Don't forget that we don't know anything about your data nor your requirements, thus you must tell us.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • August 12, 2021 at 3:17 pm

    #3919281

    Hi,

    Thanks for the replies and sorry for the delay!

    Basically we are moving to Availability Groups as the default HA option for SQL, however this comes with the downside of having to handle non-database object sync like Linked Servers, Agent Jobs, Logins etc.

    My question is what is the best option for not only monitoring drift of the these object between replicas, but also automating keeping them in (real-time) sync.

    Thanks

  • August 12, 2021 at 5:16 pm

    #3919346

    If you are connecting to the AG with the linked server, then you simply connect to the listener. If the AG is connecting to other servers, then you set up all of the linked server on each node in the AG.  There is nothing special.

    Agent Jobs are also set up on all nodes.  The simple way to determine if the job needs to execute is to query sys.dm_hadr_availability_group_states, and if the server is the primary, execute the job.

    Logins need to be created on all nodes.  Permissions to each database are granted on the primary, they get replicated to the databases on the secondary.

    You can create a central management server, and create a group for all of the servers in the AG, and execute any scripts against all of them at the same time.

    As for monitoring, the question is what do you want to monitor?  You can certainly purchase an third party monitoring tool, some are better than others to monitor AG's. There are plenty of scripts that a google search will turn up. From MS:

    https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-availability-groups-transact-sql?view=sql-server-ver15

    SQL Agent Alerts can be used to send an email when an event happens.

     

     

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • August 12, 2021 at 6:21 pm

    #3919362

    No point reinventing the wheel.

    Use powershell and dbatools sync-dbaavailabilitygroup

    https://docs.dbatools.io/Sync-DbaAvailabilityGroup

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

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