Changing a Production Provisioning Services SQL Database to Use Mirroring

February 9, 2014

PVS

I received an email from a reader asking me if I had ever changed a production Provisioning Services (PVS) database from a single database to use mirroring. I had not, so I thought this was a good time to try it out and document the process.

My lab is running two PVS 7.1 servers on Microsoft Windows Server 2012 R2 and three Microsoft SQL Server 2012 SP1 servers also running on Server 2012 R2.

Note: The Witness SQL server can run SQL Server Express but I already had full SQL Server installed on three servers so I decided to use full SQL Server and not SQL Server Express for the Witness server.

Because I am using SQL Server 2012 SP1, I had to download the Native Client for SQL Server 2012 SP1 (ENU\x64\sqlncli.msi) and install it on all PVS servers.

I have a service account created in Active Directory for the PVS database.

Citrix Database Service Account

Citrix Database Service Account

I also created a security group named SQLServers that has all three SQL Servers as members.  This security group will be used when mirroring is configured later using a wizard in SQL Server.

SQLServer security group

SQLServer security group

The members of the SQLServers security group.

SQLServers members

SQLServers members

The PVS Configuration Wizard configured the PVS database for the service account.

PVS database configured for service account

PVS database configured for service account

The Farm properties as shown in the PVS console show I am not configured for database failover.

Farm Properties

Farm Properties

The first thing that must be done is to change the Recovery model of the database that PVS created automatically.  PVS created the database using the Simple Recovery model but mirroring requires the use of the Full Recovery model.  Please see Prerequisites, Restrictions, and Recommendations for Database Mirroring.

“The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database.”

On the SQL Server that has the PVS database, open the SQL Server Management Studio, expand Databases, right-click the PVS database, click Properties and click Options in the left pane.

Simple Recovery Model

Simple Recovery Model

Change the Recovery model to Full and click OK.

Change database to Full Recovery model

Change database to Full Recovery model

Next step is to do two backups of the PVS database.  One for the database and one for the transaction logs.

Still in the Databases node of the SQL Server Management Studio, right-click the PVS database, click Tasks and click Back Up.

Database Back Up menu

Database Back Up menu

Leave the Backup type as Full and click OK.

Backup type Full

Backup type Full

Click OK.

Full backup completed

Full backup completed

Repeat the process for the second backup.  Right-click the PVS database, click Tasks and click Back Up.

Database Back Up menu

Database Back Up menu

Change the Backup type to Transaction Log and click OK.

Transaction Log Backup

Transaction Log Backup

Click OK.

Transaction Log backup completed

Transaction Log backup completed

The backup on the primary SQL server needs to be copied to the same location on what will be the mirror SQL server.  For my lab, XD71SQL1 is the primary and XD71SQL2 is the mirror.

What I did is on SQL1 open up Windows explorer to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup and also open up an Explorer window to \\xd71sql2\c$\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup.  I then copied and pasted the backup file from SQL1 to SQL2.

Backup file copied from SQL1 to SQL2

Backup file copied from SQL1 to SQL2

Exit both explorer windows.

Either go to SQL2 or add SQL2 to the SQL Server Management Studio on SQL1 (which is what I did).

Note: Please make sure your SQL Servers are running the same version or you will run into issues.  Don’t ask me how I found that out. 🙂

SQL2 Server added to Management Studio

SQL2 Server added to Management Studio

For the mirror server connection (SQL2 for me), right-click Databases and click Restore Database.

Restore Database on mirror server

Restore Database on mirror server

Change Source to Device and click the browse button.

Restore step 1

Restore step 1

Browse to the backup file copied from the primary SQL server, click on the backup file and click OK.

Restore step 2

Restore step 2

Click OK.

Restore step 3

Restore step 3

Make sure the Database is your PVS database and both Backup sets to restore are selected.  DO NOT click OK at this time.

Restore step 4

Restore step 4

Click Options in the left pane, change the Recovery state to RESTORE WITH NORECOVERY and click OK.

Restore step 5

Restore step 5

Click OK.

Restore step 6

Restore step 6

Expand Databases on the mirror server.  The PVS database should say “(Restoring…)“.

Mirror database "Restoring..."

Mirror database “Restoring…”

On the principal server, expand Databases, right-click the PVS database, click Tasks and click Mirror.

Mirror step 1

Mirror step 1

Click Configure Security.

Mirror step 2

Mirror step 2

Click Next.

Mirror step 3

Mirror step 3

Verify Yes is selected and click Next.

Mirror step 4

Mirror step 4

Verify Witness server instance is selected and click Next.

Mirror step 5

Mirror step 5

Click Next

Mirror step 6

Mirror step 6

Select the mirror server from the Mirror server instance dropdown and click Connect.

Mirror step 7

Mirror step 7

Click Connect to authenticate and connect to the mirror server.

Mirror step 8

Mirror step 8

Click Next.

Mirror step 9

Mirror step 9

Select the witness server from the Witness server instance dropdown and click Connect.

Mirror step 10

Mirror step 10

Click Connect to authenticate and connect to the witness server.

Mirror step 11

Mirror step 11

Click Next.

Mirror step 12

Mirror step 12

Enter the SQLServers security account for all three instances and click Next.

Mirror step 13

Mirror step 13

Click Finish.

Mirror step 14

Mirror step 14

If all Action columns have a status of success, click Close.  Otherwise, view the report generated, resolve the issues reported and rerun the Configure Security wizard.

Mirror step 15

Mirror step 15

Click Start Mirroring (and pray that everything works).

Mirror step 16

Mirror step 16

WHEW, it worked.  Click OK.

Mirror step 17

Mirror step 17

In the SQL Server Management Studio, refresh the primary and mirror servers.  Expand Databases on both servers.  The principal server should show the PVS database as (Principal, Synchronized) and the mirror server should show the PVS database as (Mirror, Synchronized / Restoring…).

PVS database status after starting Mirroring

PVS database status after starting Mirroring

Now the PVS servers need to be reconfigured.

On each PVS server, run the Provisioning Services Configuration Wizard.

Click Next.

PVS Configuration Wizard 1

PVS Configuration Wizard 1

Select the option appropriate for your environment and click Next.

PVS Configuration Wizard 2

PVS Configuration Wizard 2

Select the option appropriate for your environment and click Next.

PVS Configuration Wizard 3

PVS Configuration Wizard 3

Select Join existing farm and click Next.

PVS Configuration Wizard 4

PVS Configuration Wizard 4

Enter the principal and mirror SQL server names and click Next.

PVS Configuration Wizard 5

PVS Configuration Wizard 5

Select your PVS Farm and click Next.

PVS Configuration Wizard 6

PVS Configuration Wizard 6

Select your option and click Next.

PVS Configuration Wizard 7

PVS Configuration Wizard 7

Select your option and click Next.

PVS Configuration Wizard 8

PVS Configuration Wizard 8

Enter the credentials for your environment and click Next.

PVS Configuration Wizard 9

PVS Configuration Wizard 9

Select the option appropriate for your environment and click Next.

PVS Configuration Wizard 10

PVS Configuration Wizard 10

Select the options and ports appropriate for your environment and click Next.

PVS Configuration Wizard 11

PVS Configuration Wizard 11

Select the option appropriate for your environment and click Next.

PVS Configuration Wizard 12

PVS Configuration Wizard 12

Select the settings appropriate for your environment and click Next.

PVS Configuration Wizard 13

PVS Configuration Wizard 13

Click Finish.

PVS Configuration Wizard 14

PVS Configuration Wizard 14

Click Done.  If there are errors, view the log file generated, resolve any errors and rerun the Configuration Wizard.

PVS Configuration Wizard 15

PVS Configuration Wizard 15

Repeat this process for any other PVS servers.

Open the PVS Console, right-click the Farm, click Properties and click the Status tab.  The status should now show the failover partner configured.

Failover partner configured

Failover partner configured

Time to test mirror failover to make sure the PVS console still works and changes can be made to the PVS Farm.

Go back to the SQL Server Management Studio.  On the principal server, expand Databases, right-click the PVS database, click Tasks and click Mirror.

Test mirror 1

Test mirror 1

Click the Failover button.

Test mirror 2

Test mirror 2

Click Yes.

Test mirror 3

Test mirror 3

Refresh both the principal and mirror servers, expand Databases and look at the database status.

The principal server now shows (Mirror, Synchronized / Restoring…) and the mirror server now shows (Principal, Synchronized).

Test Mirror 4

Test Mirror 4

Now go to the PVS Console and test making a small change.  For me, I am going to change a target device to add a description.  This will test whether a change can be saved to the SQL database.

Target device before.

Target Device Before

Target Device Before

Target device after.

Target Device After

Target Device After

Now go to another PVS server, open the PVS console and see if the change made is seen on that server.

And the change is there.  We know the SQL Database mirroring and failover is working.

Target Device from another server

Target Device from another server

Go back to the SQL Server Management Studio, click on the mirror server’s PVS database and failover the database back to the principal server.

I hope this article helps you understand the mirror setup process necessary to implement SQL mirroring with failover for PVS.

Thanks

Webster

,

About Carl Webster

Webster is a Sr. Solutions Architect for Choice Solutions, LLC and specializes in Citrix, Active Directory and Technical Documentation. Webster has been working with Citrix products for many years starting with Multi-User OS/2 in 1990.

View all posts by Carl Webster

8 Responses to “Changing a Production Provisioning Services SQL Database to Use Mirroring”

  1. Jim Says:

    Thanks for a Detailed article Carl,
    How can we make it work for SQL Always On Availability Group?
    -J

    Reply

  2. Sara Says:

    Good document. Thank you..

    Reply

  3. Pete Says:

    Excellent and very helpful!! Thanks a lot!

    One question:
    Do I have to stop the running target devices before switching to the database(s)?

    Reply

  4. Will Says:

    Carl

    Excellent detailed blog! Much appreciated and keep up the hard work!

    Reply

  5. Jose Restrepo Says:

    Great site and i wish you good luck in the future.

    2 questions:
    1.how does this defer from using the 2 sql server option in PVS. Is this better in any way other than letting sql manage the mirror and not PVS?
    2. Is a witness server really needed? can i do this with just 2 servers?

    Thank you!!

    Reply

Leave a Reply