As you can see below we are using SQL express. That just cannot do for production!
To check what database is being used for your Site, Logging and Monitoring check in PowerShell. Run asnp Citrix* in administrative PowerShell.
Run the following highlighted commands: Get-BrokerDBConnection Get-LogDBConnection Get-MonitorDBConnection
Backup your SQL DB
Now we need to install SQL Management Studio on to the Delivery Controller to manage and backup the Xenapp Database.
Once SQL Management Studio is downloaded launch the executable and run through the wizard.
The screen shot below is highlighting the incorrect choice. You should choose the “Perform a new installation of SQL Server 2012”.
If you continue as above, you will eventually come across this error.
So lets resume choosing the correct option and wiz through this bit.
Choose Management Tools – Basic (That is all you need).
Now launch the installed SQL Studio Management.
Now you can see your Xenapp 7.x database.
Right click your DB/Tasks/Backup.
Choose backup type FULL. Choose location for .bak backup media set. Click OK.
Copy the backup file to a local drive on your SQL primary.
Create Delivery Controller machine account Login within SQL Management Studio
Within SQL Management Studio on the Primary SQL server, highlight New Query and type the following to create your Delivery Controller Login: Create login [DOMAIN\DDCNAME] from windows
Highlight text and click !Execute.
A message will appear stating the command completed.
You may need to refresh SQL Studio to see the Delivery Controller machine account.
Restore your Xenapp Single Site DB to the new SQL server
Now we are ready to restore the backed up database from the local SQL drive. Right click Database/Restore Database
Choose Device and the radio buttons and click Add and browse to your .bak (sql backup) file.
Check permissions on the DB
Next, we will check the permissions on the Delivery Controller machine account. Right click Delivery Controller account within Security/Logins and go to Properties.
Make sure the machine account is mapped to the Xenapp database. The database role membership for the Xenapp site should match the below screen shots.
There are also updates in article CTX140319 for the role memberships. ADIdentitySchema_ROLE = 7.0 Onwards Analytics_ROLE = 7.8 Onwards AppLibrarySchema_ROLE = 7.8 Onwards chr_Broker = 7.0 Onwards chr_Controller = 7.0 Onwards ConfigLoggingSchema_ROLE = 7.0 Onwards ConfigLoggingSiteSchema_ROLE = 7.0 Onwards ConfigurationSchema_ROLE = 7.0 Onwards DAS_ROLE = 7.0 Onwards DesktopUpdateManagerSchema_ROLE = 7.0 Onwards EnvTestServiceSchema_ROLE = 7.0 Onwards HostingUnitServiceSchema_ROLE = 7.0 Onwards Monitor_ROLE = 7.0 Onwards MonitorData_ROLE = 7.0 Onwards OrchestrationSchema_ROLE = 7.11 Onwards StorefrontSchema_ROLE = 7.8 Onwards TrustSchema_ROLE = 7.11 Onwards
Test, Null and Set Connections on Delivery Controller
Now we get to the part where we TEST, NULL and SET connections on the Delivery Controller.
In terms of what connections to TEST, NULL and SET depending on your Xenapp version there is this table of reference.
AcctServiceStatus AdminServiceStatus AnalyticsServiceStatus # 7.6 and newer AppLibServiceStatus # 7.8 and newer BrokerServiceStatus ConfigServiceStatus EnvTestServiceStatus LogServiceStatus MonitorServiceStatus OrchServiceStatus # 7.11 and newer TrustServiceStatus # 7.11 and newer ProvServiceStatus SfServiceStatus
Amend the scripts accordingly or include all of the above but you may get error responses within your .ps1.
The following .ps1 scripts were used for a Xenapp 7.6 environment. Testconnection.ps1 NullConnection.ps1 SetConnection.ps1 Remember to amend ServerName and SiteDBName to your environment in the scripts!
Test Connections from your Delivery CONTROLLER
We need to test connections to the migrated SQL DB using a .ps1 script. This is done within PowerShell from the Delivery Controller. Create a TESTCONNECTION.ps1 script using the below information. $ServerName=”YourSQLServer” $SiteDBName=”YourXenapp Site”
Open PowerShell and type Asnp Citrix*
Navigate to test connection script in PowerShell and run .ps1. All looks good below!
Null SQL connections from your delivery Controller
Once confirmed we need to null connections on the Delivery controllers using a .ps1 script. Create a NULLCONNECTION.ps1 script using the below.
Navigate to your .ps1 script within PowerShell to null DB connections from your delivery controller. All looks good once more.
Set the connections on your delivery Controller to the new SQL Server
Now we need to set connections so they point at the new SQL server. Create a SETCONNECTION.ps1 script using the information below.
We get prompts all the connections have been SET. The DBUnconfigured is shown as some of the commands in the .ps1 script are going to NULL connections and then SET them. Still looking good!
Restart the Citrix Broker Service within services.msc on the delivery controller.
Open up Studio.
Run the following commands to confirm. Get-BrokerDBConnection Get-LogDBConnection Get-MonitorDBConnection
BOOM!! Successful SQL Express to SQL 2012R2 Migration. Now let’s crack on with SQL HA and creating separate databases (Site,Logging and Monitoring) in PART 3 of this series.