Planning for SQL Mirroring

  • XenDesktop requires SQL Server 2008 or 2012 (R2 as well)
  • Mirroring requires SQL Server Standard or Enterprise for the principal and mirror
  • The principal and mirror must run the same version and edition of SQL
  • The witness may be SQL Server 2008 or 2012 Express
  • Three instances of SQL server are required for mirroring with automatic failover
    • The Principal server – the server used when there is no problem
    • The Mirror – takes over automatically if the principal fails
    • The Witness – provides a quorum for automatic failover

For this example, we will assume SQL Server 2008 R2 Enterprise for the principal and the mirror, with SQL 2008 R2 Express for the witness.


Jump To:


SQL Server Installation

SNAG-0000SQL prompts you to install .NET.  Click OK.

SNAG-0001New Install.

SNAG-0002Click OK.

SNAG-0003Click Install.

SNAG-0004Next.

SNAG-0005Check Database Engine Services and Management Tools – Complete.  Next.

SNAG-0006Keep Defaults.  Next.

SNAG-0007Set SQL Server Agent to Manual.
Click Use the same account for all SQL Server services.
Insert the Citrix Service account credentials.
Click OK.

SNAG-0008Click use Windows authentication mode.
Click Add Current User (if you are logged in as Citrix service account administrator).
Else, click Add and add your Citrix service account.

SNAG-0009Select Install.

SNAG-0010After successful completion, click Close.


SQL Server Configuration

ON BOTH SQL SERVERS…

SNAG-0011Open SQL Server Configuration Manager.

SNAG-0012Click on Protocols for your instance.  Right Click TCP/IP.  Enable.

SNAG-0013Click OK.

SNAG-0014Click SQL Server Services.

SNAG-0015Right Click SQL Server Browser.  Properties.

SNAG-0016Select the Service tab.  Change Start Mode to Automatic.  OK.


Configuring Mirroring

ON THE PRINCIPAL SERVER….

SNAG-0017Right click Databases.  New Database…

SNAG-0018Name your database.
Write this down.
You’ll need it when configuring XenDesktop

SNAG-0019Select Latin1_General_CI_AS_KS.
Recovery model to Full.
OK.

SNAG-0020Right click new database.  Tasks > Back Up.

SNAG-0021Set Backup type to Full.  Add location for backup.

Save as a .bak!

SNAG-0022Right click new database.  Tasks > Back Up.  Again…

SNAG-0023Select Backup type to Transaction Log.  Add location for backup.

Save as a .trn!

Copy both .bak and .trn to the secondary server  C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup


 ON THE MIRROR SERVER…

SNAG-0024Right click Databases.  Select Restore Database.

SNAG-0025Click the button to the right of From Device and select the local folder you just copied .bak and .trn to

SNAG-0026Add.

SNAG-0027Select the location of your .bak file.

SNAG-0028Click OK.

SNAG-0029

Verify full backup populates.
DO NOT CHECK OK!

SNAG-0030Click the Options tab.  Select RESTORE WITH NO RECOVERY.
Click OK.

SNAG-0031You will see Restoring…
Perform these steps again with the transactional backup to complete mirror.

SNAG-0024Right click Databases.  Select Restore Database.

SNAG-0025Click the button to the right of From Device and select the local folder you just copied .bak and .trn to

SNAG-0026Add.

SNAG-0027Select the location of your .trn file.

SNAG-0028Click OK.

SNAG-0029

Verify full backup populates.
DO NOT CHECK OK!

SNAG-0030Click the Options tab.  Select RESTORE WITH NO RECOVERY.
Click OK.

You have successfully mirrored when database reads Mirror, Synchronized.


ON BOTH SERVERS…

SNAG-0032Make sure the SQL Server Agent service is running.


 ON THE PRINCIPAL SERVER…

SNAG-0033Right click.  Properties.

SNAG-0034Click the Mirroring tab.  Configure Security.

SNAG-0035

SNAG-0036Click Yes to configure a Witness.  Next.

SNAG-0037Next

SNAG-0038Keep Defaults.  Next.

SNAG-0039Enter the mirrored SQL instance (secondary).  Connect.

SNAG-0040Next.

SNAG-0041Enter the Witness instance name.  Connect.

SNAG-0042Next.

SNAG-0044Enter service accounts for all three instances.  Should be the same. Next.

SNAG-0045Click Finish.

SNAG-0046Verify all three succeeded.  Close.

SNAG-0047DO NOT START MIRRORING YET!
Click Do Not Start Mirroring.

SNAG-0048Select high safety with automatic failover.  Click Start Mirroring.

SNAG-0049Verify the status claims Synchronized.  Click OK.


Configuring XenDesktop 5.x with a mirrored SQL Database

SNAG-0050Install XenDesktop.  Make sure to un-click SQL Server Express.

SNAG-0051Select Desktop Deployment.

SNAG-0052Enter location of primary SQL Server.  Test Connection.

SNAG-0053Click OK.
When you create the database automatically,
mirroring is detected without user intervention.

SNAG-0054Click OK.  Continue configuring XenDesktop as normal.

SNAG-0055To verify mirroring after the wizard has completed, Launch Powershell.

SNAG-0056Run the PowerShell cmdlet get-configdbconnection and ensure that
the Failover Partner is set in the connection string to the mirror.


Configuring XenDesktop 7.x with a mirrored SQL Database

installing-and-configuring-xendesktop-71-009Click Get Started!  Create a Site.

installing-and-configuring-xendesktop-71-010Enter the name of your Site.

installing-and-configuring-xendesktop-71-011Enter the location of your primary SQL Server.

installing-and-configuring-xendesktop-71-012Click OK.
When you create the database automatically,
mirroring is detected without user intervention.

installing-and-configuring-xendesktop-71-013Click OK.  Continue configuring XenDesktop as normal.

SNAG-0060

To verify mirroring after the wizard has completed, Launch Powershell.

SNAG-0056Run the PowerShell cmdlet get-configdbconnection and ensure that
the Failover Partner is set in the connection string to the mirror.


Configuring PVS 6.x with a Mirrored SQL Database

SNAG-0057Open the Provisioning Services Configuration Wizard.  Specify both the
primary and the mirror SQL instances.  Click next.  Configure as usual.


Configuring PVS 7.x with a Mirrored SQL Database

SNAG-0059Open the Provisioning Services Configuration Wizard.  Specify both the
primary and the mirror SQL instances.  Click next.  Configure as usual.


Configuring XenApp with a Mirrored SQL Database

See this document for more information.  Page 13.

(Visited 6,985 times, 7 visits today)