• 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