Unfortunately once again the documentation provided by Citrix on moving databases from one SQL Server to another is incomplete. The documentation provided here: http://support.citrix.com/article/CTX140319 misses out the configuration of one of the services.
The listing misses out the configuration of the Citrix Analytics Service. In my environment this meant that after the reboot of the service, it tried to do a license server upgrade (I forgot to take a screenshot).
Secondly, the PowerShell that is supplied on the site removes the DB configuration for the MonitorDBConnection and LogDBConnection before removing the configuration from the DataStore of the respective services. This causes an error when running the commands in the order that they list.
My corrected version is below. This assumes that the database has already been moved to the new SQL server and the necessary logins created.
This script must be run elevated and needs to be run on all XenDesktop servers.
21 May 2017 – Updated script to support new services in XenDesktop 7.13, with backwards compatibility. Also prompts you to confirm that the new connection string is correct before continuing.
$OldSQLServer = "OLDSQLSERVER" $NewSQLServer = "NEWSQLSERVER" Write-Host "Adding Citrix SnapIns" -ForegroundColor Black -BackgroundColor Yellow Add-PSSnapin Citrix* -ErrorAction Stop # Test that all brokers are the same version if ((Get-BrokerController | Select-Object ControllerVersion -Unique | Measure).Count -ne 1) { Write-Error "Multiple delivery controller versions found. Recommended to have the same version throughout before upgrading" break } [int]$Version = (Get-BrokerController | Select-Object -ExpandProperty ControllerVersion -Unique).Split(".")[1] Write-Host "Stopping Logging" -ForegroundColor Black -BackgroundColor Yellow Set-LogSite -State "Disabled" Write-Host "Updating Connection String" -ForegroundColor Black -BackgroundColor Yellow $ConnStr = Get-ConfigDBConnection Write-Host "Old Connection String: $ConnStr" $ConnStr = $ConnStr.Replace($OldSQLServer,$NewSQLServer) Write-Host "New Connection String: $ConnStr" if ([String]::IsNullOrWhiteSpace($Connstr)) { Write-Error "New connection string appears to be empty. Unable to proceed" break } $PromptTitle = "Confirm New Connection String" $PromptMessage = "Is the new connection string shown above correct?" $PromptYes = New-Object System.Management.Automation.Host.ChoiceDescription "&Yes", "Change the Database Connection." $PromptNo = New-Object System.Management.Automation.Host.ChoiceDescription "&No", "Stop! This connection string is wrong!" $PromptOptions = [System.Management.Automation.Host.ChoiceDescription[]]($PromptNo, $PromptYes) $Result = $Host.UI.PromptForChoice($PromptTitle, $PromptMessage, $PromptOptions, 0) if ($Result -ne 1) { return } # Clear error history, we can then check it at the end. $Error.Clear() Write-Host "Clearing all current DB Connections" -ForegroundColor Black -BackgroundColor Yellow Set-ConfigDBConnection -DBConnection $null if ([int]$Version -ge 6) { Set-AnalyticsDBConnection -DBConnection $null } if ([int]$Version -ge 8) { Set-AppLibDBConnection -DBConnection $null } if ([int]$Version -ge 11) { Set-OrchDBConnection -DBConnection $null } if ([int]$Version -ge 11) { Set-TrustDBConnection -DBConnection $null } Set-HypDBConnection -DBConnection $null Set-ProvDBConnection -DBConnection $null Set-BrokerDBConnection -DBConnection $null Set-AcctDBConnection -DBConnection $null Set-EnvTestDBConnection -DBConnection $null Set-SfDBConnection -DBConnection $null Set-MonitorDBConnection -DataStore Monitor -DBConnection $null Set-MonitorDBConnection -DBConnection $null Set-LogDBConnection -DataStore Logging -DBConnection $null Set-LogDBConnection -DBConnection $null Set-AdminDBConnection -DBConnection $null Write-Host "Configuring new DB Connections" -ForegroundColor Black -BackgroundColor Yellow Set-AdminDBConnection -DBConnection $ConnStr Set-ConfigDBConnection -DBConnection $ConnStr Set-AcctDBConnection -DBConnection $ConnStr if ([int]$Version -ge 6) { Set-AnalyticsDBConnection -DBConnection $ConnStr } Set-HypDBConnection -DBConnection $ConnStr Set-ProvDBConnection -DBConnection $ConnStr if ([int]$Version -ge 8) { Set-AppLibDBConnection -DBConnection $ConnStr } if ([int]$Version -ge 11) { Set-OrchDBConnection -DBConnection $ConnStr } if ([int]$Version -ge 11) { Set-TrustDBConnection -DBConnection $ConnStr } Set-BrokerDBConnection -DBConnection $ConnStr Set-EnvTestDBConnection -DBConnection $ConnStr Set-SfDBConnection -DBConnection $ConnStr Set-LogDBConnection -DBConnection $ConnStr Set-LogDBConnection -DataStore Logging -DBConnection $ConnStr Set-MonitorDBConnection -DBConnection $ConnStr Set-MonitorDBConnection -DataStore Monitor -DBConnection $ConnStr Write-Host "Testing new DB Connections..." -ForegroundColor Black -BackgroundColor Yellow Test-AcctDBConnection -DBConnection $ConnStr Test-AdminDBConnection -DBConnection $ConnStr if ($Version -ge 6) { Test-AnalyticsDBConnection -DBConnection $ConnStr } if ($Version -ge 8) { Test-AppLibDBConnection -DBConnection $ConnStr } Test-BrokerDBConnection -DBConnection $ConnStr Test-ConfigDBConnection -DBConnection $ConnStr Test-EnvTestDBConnection -DBConnection $ConnStr Test-HypDBConnection -DBConnection $ConnStr Test-LogDBConnection -DBConnection $ConnStr Test-MonitorDBConnection -DBConnection $ConnStr if ($Version -ge 11) { Test-OrchDBConnection -DBConnection $ConnStr } Test-ProvDBConnection -DBConnection $ConnStr Test-SfDBConnection -DBConnection $ConnStr if ($Version -ge 11) { Test-TrustDBConnection -DBConnection $ConnStr } Write-Host "Re-enabling Logging" -ForegroundColor Black -BackgroundColor Yellow Set-MonitorConfiguration -DataCollectionEnabled $true Set-LogSite -State "Enabled" Write-Host "Get details of all new DB Connection strings" Get-ConfigDBConnection if ([int]$Version -ge 6) { Get-AnalyticsDBConnection } if ([int]$Version -ge 8) { Get-AppLibDBConnection } if ([int]$Version -ge 11) { Get-OrchDBConnection } if ([int]$Version -ge 11) { Get-TrustDBConnection } Get-HypDBConnection Get-ProvDBConnection Get-BrokerDBConnection Get-EnvTestDBConnection Get-SfDBConnection Get-MonitorDBConnection Get-MonitorDBConnection -DataStore Monitor Get-LogDBConnection -DataStore Logging Get-LogDBConnection Get-AdminDBConnection Get-AcctDBConnection if ($Error.Count -gt 0) { Write-Error "There have been issues changing the database connection. Please check the current connection strings and update any that are incorrect before restarting services" break } Write-Host "Restarting all Citrix Services..." -ForegroundColor Black -BackgroundColor Yellow Get-Service Citrix* | Stop-Service -Force Get-Service Citrix* | Start-Service
I have three databases (Site, Logging and Monitor). Will this script address this situation?
Yes, It should update all databases. Only the server component of the connection string is updated, so the database name remains the same. It does require that all databases are moved at the same time though.
It does’nt seem to work if the Server name is the same but the instance is different. Trying to run it with just an Instance name but it just reports the New Instance as the Old instance
Old Server= SQL01\CITRIX02
newServer=SQL01\CITRIX03
any ideas?
Sorry for delay. I have not been able to replicate this. Have set up a new instance and attempted to move and it completed the move and worked.
Can i do the migration while users are logged in?
Hi,
Yes, I did. However, whilst the database is offline no new users will be able to connect. So I would schedule out-of-hours or at the quietest possible time.
Craig
Hi Craig,
Can this be applied to move an existing database (standalone) to a clustered database. I have been trying this and get the error noted in the beginning of this article. I have tried using your script with no success
Regards
Ray
Yes it can be used to move from a standalone to a clustered instance. The underlying SQL architecture is irrelevant.
I have just updated to script to support all of the services in XenDesktop 7.13, as some new services were missing.
We have 3 DCs in our site. What process do you find works best. Do you power down the servers not being worked on during the conversion?
The process I used was:
1. Backup database, but leave online on old instance.
2. Restore to the new instance
3. Run script one server at a time, verifying that the new configuration works
4. Once all servers are updated, take the database offline on the old instance.
In my case I am migrating from a standalone SQL Server to Always On Availability Group
What steps and additional considerations are required for ensuring a smooth migration?
So far I have:
1. DDC Accounts on SQL (Need to Match)
2. When re-conecting use the Listener´s name.
Thanks
I haven’t actually done this yet. Something I am looking to do later this year as I am currently working on the SQL side. From the DDC point of view, it is just a different SQL instance, so what you have listed looks to be sufficient.
It would be great to hear back how you get on though and if you encounter anything else.
Worked like a charm using this guide: http://support.citrix.com/article/CTX140319
-point to listener name
-ensure all accounts are created and mappings done on destination identical to source SQL server
Great that the Citrix docs are now working.
Must I run this script on both Delivery Controllers
Yes, you do.
You are a lifesaver, I have spent a couple of days trying to dismount and remount a database to Studio and had failed constantly when running the scripts from Citrix sites and forums.
Ran your script and it worked first time, restored Studio connection and no silliness about an upgrade.
Hi, just out of curiousity; we came across the same issue, were you changing the DB links for 7.6 or a different version of XenDesktop?
Hi, yes, we were working on 7.6.
Craig, thanks for the information. The observation you made about the Citrix docs being both incomplete and in the wrong order was dead on.. Spent a considerable amount of time trying to muddle through before i ran across your post… Worked like a champ!
Thanks again
Fantastic, glad you got it sorted
Do we just need to run this powershell script ? Does it need any parameters ?
And one query I have is, here we define only the server names. where does it take database details from ?
$OldSQLServer = “OLDSERVER\OLDINSTANCE
$NewSQLServer = “NEWSERVER\NEWINSTANCE”
Hi,
You just run it after changing the server and instance names that you have highlighted. The script is not a function and does not take parameters.
This also assumes that the database name is going to remain the same.
Hope that clarifies what the script does.