This post has already been read 44433 times!
Create 3 Xenapp Databases
You have migrated your Xenapp database and now you want to separate your database in to Site, Monitoring and Logging databases and introduce high availability. Let’s get started! You are logged on to Xenapp Studio with an account that has sysadmin rights on SQL . Open up Xenapp Studio console.
Highlight Logging and on the right click Change Database.
Enter new database and location.
Click OK so Studio can create the new databases automatically.
Go through the same process for the Monitoring DB.
Voila! You now have 3 separate Databases for your Xenapp environment.
In Xenapp Studio Management under configuration you can see the databases.
SQL Studio Management on your primary SQL will show 3 databases.
Change recovery Model of all 3 DB’s to Full
Next we get on with introducing HA in your database environment. In this example we will use SQL mirroring and configure the Delivery controllers to be aware of the primary and Failover SQL partner.
All databases need to be backed up and restored with “No Recovery” option to the mirrored SQL partner. Before this is done the Recovery model should be changed to FULL on all the databases. Right Click database/Options
Change the Recovery Model to FULL.
Make a full backup of all 3 DB’s
Right click DB/Tasks/Back up.
Select the location for your backup.
Once confirmed click OK to backup the DB.
Make a transaction log backup of all 3 DB’s
Right click DB/Tasks/Back up/Options Backup Type: Transaction LogClick OK
Backup the Transaction logs to the existing media set. Do this for the Site, Monitoring and Logging databases.
Copy all backups to a local drive on the server acting as the SQL mirror.
Create the Controller logins on the SQL server acting as mirror
New Query Create login [Your Domain\DDC Machine account$] from windowsClick !Execute
Restore the databases with the “NO RECOVERY” option
Do this on the SQL server acting as the mirror.
Choose back up DB copied locally on SQL mirror.
You will see the full and transaction logs appear as they were appended to the same backup set.
Before you commit and press OK for the restore make sure you are restoring with the “No Recovery” option. On the right go to Options and choose RESTORE WITH NO RECOVERY.Click OK and you will see a message confirming your database restored successfully.
You can now view the database in “Restoring” state in SQL studio. Repeat the restore procedure for the remaining databases.
Create the mirror from the Principal SQL server
Choose database and right click. Tasks/Mirror/
Click Configure Security tab
The Mirroring Security Wizard appears. Click Next.
In this example I am not configuring a witness server. You can use SQL express for this role if a witness is required. Using a witness will provide automatic failover should you have issues with your principal SQL server and is best practice.
Choose your Principal SQL.
Next choose the Mirror Server Instance. You must click Connect and authenticate to the server. Click Connect
Click Next
Enter your credentials (Usually the administrative account you are logged in with).
Review and click Finish.
Once you click Close this pop up should appear. Click Start Mirroring.The status will confirm successful synchronization.
The Databases on the Principal should now look like the below:
The databases on the Mirror should look like the below:
Note: If you come across the following error when trying to mirror your Xenapp site database:You will need to set Auto Close to OFF on the database.
This is achieved by running a New Query on the primary SQL server and executing the query: ALTER DATABASE YourXenappDB SET AUTO_CLOSE OFF
Failover and test permissions
Initiate failover from the principal database and check permissions on the Controller machine account. Right click database and choose TASKS/MIRROR.
Click Failover.
The database on the original SQL server you initiated FAILOVER on should now show the following status:
Do this on all 3 databases.
Now you should check your permissions on the SQL you failed over to. Check permissions on the Controller accounts for the databases. They should match the following:
Logging Database Permissions
Monitoring Database Permissions
Site Database Permissions
If all looks good initiate failover once again from the database that shows the principal role so all the databases are on the original SQL server that Xenapp was connected to.
TEST, NULL and SET Connections on your Delivery Controllers
The following actions will need to be performed on all your Delivery Controllers so they point to the new SQL setup.
Test Connections
We now need to test connections on both SQL servers to check if there are any issues. This can be achieved by the following .ps1 script. Remember to put YOUR SQL primary server and YOUR failover SQL partner.
$cs = "Data Source=SQL01; Failover Partner=SQL02; Initial Catalog=CITXENSITE; Integrated Security=True; Network=dbmssocn"
$controllers = Get-BrokerController | %{$_.DNSName}
foreach ($controller in $controllers)
{
Write-Host "Testing controller $controller ..."
Test-ConfigDBConnection -DBConnection $cs -AdminAddress $Controller
Test-AcctDBConnection -DBConnection $cs -AdminAddress $Controller
Test-HypDBConnection -DBConnection $cs -AdminAddress $Controller
Test-ProvDBConnection -DBConnection $cs -AdminAddress $Controller
Test-BrokerDBConnection -DBConnection $cs -AdminAddress $Controller
Test-EnvTestDBConnection -DBConnection $cs -AdminAddress $Controller
Test-SfDBConnection -DBConnection $cs -AdminAddress $Controller
Test-MonitorDBConnection -DBConnection $cs -AdminAddress $Controller
Test-MonitorDBConnection -DataStore Monitor -DBConnection $cs -AdminAddress $Controller
Test-AdminDBConnection -DBConnection $cs -AdminAddress $Controller
Test-LogDBConnection -DBConnection $cs -AdminAddress $Controller
Test-LogDBConnection -Datastore Logging -DBConnection $cs -AdminAddress $Controller
}
Null connections
Connections to the principal SQL server need to be nulled. This can be achieved by the following ps1 script.
Set-LogSite -State Disabled Set-LogDBConnection -DataStore Logging -DBConnection $null Set-MonitorDBConnection -DataStore Monitor -DBConnection $null Set-MonitorDBConnection -DBConnection $null Set-AcctDBConnection -DBConnection $null Set-ProvDBConnection -DBConnection $null Set-BrokerDBConnection -DBConnection $null Set-EnvTestDBConnection -DBConnection $null Set-SfDBConnection -DBConnection $null Set-HypDBConnection -DBConnection $null Set-ConfigDBConnection –DBConnection $null -Force Set-LogDBConnection -DBConnection $null –Force Set-AdminDBConnection -DBConnection $null –Force
Screen shot highlighting results of script.
SET CONNECTIONS
Set the connections so the Delivery Controllers are aware of both SQL servers. Connections to the SQL servers (Principal and Mirror) need to be set. This can be achieved by the following ps1 script. Remember to put YOUR SQL primary server and YOUR failover SQL partner.
$cs = "Server=SQL01; Initial Catalog=CitXenSite;Integrated Security=True;Failover Partner=SQL02" $cl = "Server=SQL01;Initial Catalog=CitXenLogDB;Integrated Security=True;Failover Partner=SQL02" $cm = "Server=SQL01;Initial Catalog=CitXenMonDB;Integrated Security=True;Failover Partner=SQL02" Set-ConfigDBConnection -DBConnection $cs Set-AdminDBConnection -DBConnection $cs Set-LogDBConnection -DBConnection $cs Set-AcctDBConnection -DBConnection $cs Set-BrokerDBConnection -DBConnection $cs Set-EnvTestDBConnection -DBConnection $cs Set-HypDBConnection -DBConnection $cs Set-MonitorDBConnection -DBConnection $cs Set-ProvDBConnection -DBConnection $cs Set-sfDBConnection –DBConnection $cs Set-LogDBConnection -DataStore Logging -DBConnection $cl Set-MonitorDBConnection -DataStore Monitor -DBConnection $cm Set-LogSite -State Enabled
Screen shot of results of script.
Confirm and test connections to both SQL servers
Confirm that the Delivery Controller has connections to the principal and mirror SQL servers.
Get-BrokerDBConnection Get-LogDBConnection Get-MonitorDBConnection
The result within Studio should show connections to the SQL server address and the Mirror server address.
Final test is to initiate failover from principal databases and run these commands again: Get-BrokerDBConnection Get-LogDBConnection Get-MonitorDBConnection Finally open up Xenapp Studio Console.
Final Word
So in this 3 part series we have shown you the following: Migrate SQL express to SQL production. Create 3 separate databases for Xenapp. Introduce resiliency by mirroring. Hope you enjoy! Remember to do everything in TEST FIRST!