Availability Groups - Object Sync

Question

Post reply

Availability Groups - Object Sync

lanky_doodle

SSCrazy

Points: 2378
More actions
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

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

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

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

Michael L John One Orange Chip Points: 27054 More actions · Answer 2

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/

ScottPletcher SSC Guru Points: 101095 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

lanky_doodle SSCrazy Points: 2378 More actions · Answer 4

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

Michael L John One Orange Chip Points: 27054 More actions · Answer 5

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.

This reply was modified 3 years, 3 months ago by Michael L John.

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/

Ant-Green SSC Guru Points: 113507 More actions · Answer 6

No point reinventing the wheel.

Use powershell and dbatools sync-dbaavailabilitygroup

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