December 19, 2019 at 7:58 pm
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?
December 19, 2019 at 9:07 pm
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/
December 20, 2019 at 6:09 pm
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.
December 20, 2019 at 6:44 pm
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/
December 20, 2019 at 7:12 pm
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.
December 26, 2019 at 10:33 pm
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.
July 20, 2020 at 10:24 am
to delete a sqllogin/job that is not on primary replica but still no secondary replica. The trick is to check if logins/jobs from secondary replica exist in primary, if they do not, they will be deleted. I've created a daily job on both replica and it worked like a chime. ( I am using dba-tools module btw)
On each replica, you need to change value for $source and $dest. Also need to add the first step to check if it is under primary node. This job will only run if it is under primary node.
cls
$source = "PrimaryReplica"
$dest = "SecondaryReplica"
$logins = Get-DbaLogin -SqlInstance $dest |select name
foreach ($login in $logins)
{
$result = Get-DbaLogin -SqlInstance $source -login $login.Name
if (!$result.Name ){Remove-DbaLogin -SqlInstance $dest -Login $login.name -Confirm:$false}
}
$sqljobs = Get-DbaAgentJob -SqlInstance $dest |select name
foreach ($sqljob in $sqljobs)
{
$result= Get-DbaAgentJob -SqlInstance $source -Job $sqljob.Name
if (!$result.Name) {Remove-dbaAgentjob -sqlinstance $dest -job $sqljob.name -Confirm:$false }
}
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply