This message was deleted.
# citrix-vad
s
This message was deleted.
t
We did. Probably 3-4 years ago now. It wasn't that bad. Had to do a command to repoint the DB, and upgrades sucked until recently (AAGs not supported).
Ok cool Tim. Something I will have to tackle soon. Need to read up and make sure I understand it.
t
Yes, that was it. Make sure you have a solid backup. We did it during a maintenance window, and I'm fortunate that my Citrix engineer was a DBA at his previous job, so he was able to handle it pretty easily.
r
ok cool man. Yea I have some DB experience. We will see 🙂
👍 1
d
Did this about 3 years ago as well. Then changed the DB structure to distributed AG which basically required the process to run through again. We actually chose not to keep the log and monitor DBs during the first move as we decided that less downtime was more important than the data in those. I don't remember exactly how it was done, but with PowerShell commands all pre-written and prepared, it wasn't a terrible cut over as I recall. The issues we had actually came later, after the first cut over when we had our first fail over event that was unplanned. We should have planned the fail over as a test, during a maintenance window, but that didn't happen. The major issue was that the security rights from the primary SQL DB didn't get assigned to the secondary and thus, the Delivery Controllers couldn't contact the DB on the new server. A quick fail back resolved the issue until we could figure out the problem. I now have some scripts written that ensure the permissions across all of the SQL servers (4 servers currently in total in a multiple DC configuration) have the same permissions. I would be willing to share if interested/needed. The current scripts currently rely on tools from https://dbatools.io/ which make managing the SQL servers much easier. Highly recommend them for PowerShell scripting SQL tasks.
r
Thanks @Dennis Parker much appreciated. I am interested in the scripts please. Thank you.
d
Here is the Distributed AG script I use currently. This is a modified version of a script written by someone else, as noted at the top of the script. I run the script once per day. Our environment doesn't change that much.
<#
Script : SyncLoginsToDistributedAG.ps1
Author : Andreas Schubert (http://www.linkedin.com/in/schubertandreas)
Purpose: Sync logins between all replicas in an Availability Group automatically.
--------------------------------------------------------------------------------------------
The script will connect to the listener name of the Availability Group
and read all replica instances to determine the current primary replica and all secondaries.
It will then connect directly to the current primary, query all Logins and create them on each
secondary.
Attention:
The script is provided so that no action is actually executed against the secondaries (switch -WhatIf).
Change that line according to your logic, you might want to exclude other logins or decide to not drop
any existing ones.
--------------------------------------------------------------------------------------------
Usage: Save the script in your file system, change the name of the AG Listener (AGListenerName in this template)
and schedule it to run at your prefered schedule. I usually sync logins once per hour, although
on more volatile environments it may run as often as every minute
#>
param (
# define the AG name
[Parameter(Mandatory=$true)][string]$AvailabilityGroupName,
[string]$ClientName = 'AG Login Sync helper'
)
# internal variables
#$ClientName = 'AG Login Sync helper'
$primaryInstance = $null
$secondaryInstances = @{}
[array]$PrimaryAGSecondaryInstances = @{}
[array]$SecondaryAGsecondaryInstances = @{}
$AvailabilityGroupListener = $AvailabilityGroupName + "-L"
try {
# connect to the Distributed AG listener, get the name of the primary and secondary AGs
$DistAG = Get-DbaAgReplica -SqlInstance $AvailabilityGroupListener -AvailabilityGroup $AvailabilityGroupName
$primaryInstance = $DistAG | Where Role -eq Primary | select -ExpandProperty name
$primaryInstanceListener = $primaryInstance + "-L"
$PrimaryAG = Get-DbaAgReplica -SqlInstance $primaryInstanceListener -AvailabilityGroup $primaryInstance
$PrimaryAGPrimaryInstance = $PrimaryAG | Where Role -eq Primary | select -ExpandProperty name
$PrimaryAGSecondaryInstances = $PrimaryAG | Where Role -ne Primary | select -ExpandProperty name
$SecondaryAGname = $DistAG | Where Role -ne Primary | select -ExpandProperty name
$SecondaryAGlistener = $SecondaryAGname + "-L"
#All Secondary AG servers are, by definition, secondary instances
$SecondaryAGsecondaryInstances = Get-DbaAgReplica -SqlInstance $SecondaryAGlistener -AvailabilityGroup $SecondaryAGname | select -ExpandProperty name
$secondaryInstances = $PrimaryAGSecondaryInstances + $SecondaryAGsecondaryInstances
# create a connection object to the primary
$primaryInstanceConnection = Connect-DbaInstance $PrimaryAGPrimaryInstance -ClientName $ClientName
# loop through each secondary replica and sync the logins
$secondaryInstances | ForEach-Object {
$secondaryInstanceConnection = Connect-DbaInstance $_ -ClientName $ClientName
Get-DbaLogin -SqlInstance $primaryInstanceConnection -ExcludeSystemLogin -Type SQL -ExcludeFilter '##*' | where { $_.DateLastModified -gt ((Get-Date).AddDays(-1)) } | % { Copy-DbaLogin -Source $primaryInstanceConnection -Destination $secondaryInstanceConnection  -Login $_.Name -Force  }
Copy-DbaLogin -Source $primaryInstanceConnection -Destination $secondaryInstanceConnection -ExcludeSystemLogins
}
}
catch {
$msg = $_.Exception.Message
Write-Error "Error while syncing logins for Availability Group '$($AvailabilityGroupName): $msg'"
}
👍 1
c
r
Super nice
Thank you very much guys.