Month: February 2017

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 Log



Click 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 windows



Click !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!