Carl Webster Accessibility Statement

Carl Webster is committed to facilitating the accessibility and usability of its website, carlwebster.com, for everyone. Carl Webster aims to comply with all applicable standards, including the World Wide Web Consortium’s Web Content Accessibility Guidelines 2.0 up to Level AA (WCAG 2.0 AA). Carl Webster is proud of the efforts that we have completed and that are in-progress to ensure that our website is accessible to everyone.

If you experience any difficulty in accessing any part of this website, please feel free to email us at info@carlwebster.com and we will work with you to provide the information or service you seek through an alternate communication method that is accessible for you consistent with applicable law (for example, through telephone support).

  • 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 runs 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.

    Figure 1
    Figure 1

    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.

    Figure 2
    Figure 2

    The members of the SQLServers security group.

    Figure 3
    Figure 3

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

    Figure 4
    Figure 4

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

    Figure 5
    Figure 5

    The first thing that must be done is to change the database’s Recovery model that PVS created automatically.  PVS created the database using the Simple Recovery model, but mirroring requires 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 with 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.

    Figure 6
    Figure 6

    Change the Recovery model to Full and click OK.

    Figure 7
    Figure 7

    The 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.

    Figure 8
    Figure 8

    Leave the Backup type as Full and click OK.

    Figure 9
    Figure 9

    Click OK.

    Figure 10
    Figure 10

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

    Figure 11
    Figure 11

    Click OK.

    Figure 12
    Figure 12

    Change the Backup type to Transaction Log and click OK.

    Figure 13
    Figure 13

    Click OK.

    Figure 14
    Figure 14

    The backup on the primary SQL server needs to be copied to the same location on 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.

    Figure 15
    Figure 15

    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. 🙂

    Figure 16
    Figure 16

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

    Figure 17
    Figure 17

    Change Source to Device and click the browse button.

    Figure 18
    Figure 18

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

    Figure 19
    Figure 19

    Click OK.

    Figure 20
    Figure 20

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

    Figure 21
    Figure 21

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

    Figure 22
    Figure 22

    Click OK.

    Figure 23
    Figure 23

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

    Figure 24
    Figure 24

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

    Figure 25
    Figure 25

    Click Configure Security.

    Figure 26
    Figure 26

    Click Next.

    Figure 27
    Figure 27

    Verify Yes is selected and click Next.

    Figure 28
    Figure 28

    Verify Witness server instance is selected and click Next.

    Figure 29
    Figure 29

    Click Next

    Figure 30
    Figure 30

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

    Figure 31
    Figure 31

    Click Connect to authenticate and connect to the mirror server.

    Figure 32
    Figure 32

    Click Next.

    Figure 33
    Figure 33

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

    Figure 34
    Figure 34

    Click Connect to authenticate and connect to the witness server.

    Figure 35
    Figure 35

    Click Next.

    Figure 36
    Figure 36

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

    Figure 37
    Figure 37

    Click Finish.

    Figure 38
    Figure 38

    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.

    Figure 39
    Figure 39

    Click Start Mirroring (and pray that everything works).

    Figure 40
    Figure 40

    WHEW, it worked.  Click OK.

    Figure 41
    Figure 41

    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…).

    Figure 42
    Figure 42

    Now the PVS servers need to be reconfigured.

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

    Click Next.

    Figure 43
    Figure 43

    Select the option appropriate for your environment and click Next.

    Figure 44
    Figure 44

    Select the option appropriate for your environment and click Next.

    Figure 45
    Figure 45

    Select Join existing farm and click Next.

    Figure 46
    Figure 46

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

    Figure 47
    Figure 47

    Select your PVS Farm and click Next.

    Figure 48
    Figure 48

    Select your Site option and click Next.

    Figure 49
    Figure 49

    Select your Store option and click Next.

    Figure 50
    Figure 50

    Enter the credentials for your environment and click Next.

    Figure 51
    Figure 51

    Select the password option appropriate for your environment and click Next.

    Figure 52
    Figure 52

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

    Figure 53
    Figure 53

    Select the option appropriate for your environment and click Next.

    Figure 54
    Figure 54

    Select the settings appropriate for your environment and click Next.

    Figure 55
    Figure 55

    Click Finish.

    Figure 56
    Figure 56

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

    Figure 57
    Figure 57

    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.

    Figure 58
    Figure 58

    Time to test mirror failover to ensure 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.

    Figure 59
    Figure 59

    Click the Failover button.

    Figure 60
    Figure 60

    Click Yes.

    Figure 61
    Figure 61

    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 shows (Principal, Synchronized).

    Figure 62
    Figure 62

    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.

    Figure 63
    Figure 63

    Target device after.

    Figure 64
    Figure 64

    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 are working.

    Figure 65
    Figure 65

    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:
      August 25, 2016 at 3:09 am

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

      Reply

    2. Sara Says:
      March 21, 2015 at 11:53 am

      Good document. Thank you..

      Reply

    3. Pete Says:
      July 14, 2014 at 10:48 am

      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:
      July 7, 2014 at 8:25 pm

      Carl

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

      Reply

    5. Jose Restrepo Says:
      May 5, 2014 at 4:03 pm

      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