Test Lab Guide: Configuring AD RMS with SQL Mirroring in Windows Server 2012

  • Article

The purpose of this test lab guidance is to enable you to understand how to setup SQL mirroring within an Active Directory Rights Management Services (AD RMS) infrastructure in a test environment.

This process assumes familiarity with the base AD RMS lab setup that is described in the Test Lab Guide: Deploying an AD RMS Cluster and that in turn leverages the base Active Directory domain setup from the Windows Server 2012 Base Configuration Test Lab Guide.

To complete this lab setup you will need the same four VMs that are described in the AD RMS base TLG (DC1, APP1, SQL1, CLIENT1). To those four VMs, you will add a fifth VM, SQL2, which will be used to accomplish the SQL mirroring of the AD RMS databases. You will also need to create an additional domain user account, SQLSVC, to be used as the SQL Server service account.

Although the configuration details will be similar to those described in these previously referenced TLGs, the following modified software and hardware requirements are necessary for both SQL server installations (SQL1 and SQL2) in order to accomplish the SQL mirroring configuration as described in this TLG:

  • For SQL database mirroring, a minimum of the Standard edition of Microsoft SQL Server is required. SQL Server Express edition, as shown in the AD RMS base TLG, does not support SQL mirroring. In addition, the following guidance was originally tried using mirrors created on both SQL Server 2008 R2 SP1 and SQL Server 2012 SP1. The process should follow the same steps for either version.
  • Both server computers that are used to host the principal (SQL1) and mirror (SQL2) instances should be built using identical hardware and software choices (i.e. same make and model of computer, same versions and editions of Windows Server and SQL Server).
  • When mirroring SQL Server databases for AD RMS it is recommended that you use the high performance operating mode which uses asynchronous data transfer and does not require a witness server.

Steps to accomplish SQL Mirroring

  1. Install DC1 using the network and naming properties provided in the Windows Server 2012 Base Configuration Test Lab Guide. If you follow the guidance, you will have created a new forest with the domain of corp.contoso.com and an IPv4 address of 10.0.0.1.

  2. Install APP1 using the network and naming properties provided in the Windows Server 2012 Base Configuration Test Lab Guide. APP1 will be a new member server in the corp.contoso.com domain with an IPv4 address of 10.0.0.3.

  3. Install SQL1 (principal) and SQL2 (mirror) as new member servers in the corp.contoso.com domain with IPv4 addresses of 10.0.0.5 and 10.0.0.6 respectively. When creating both SQL1 and SQL 2, repeat the following steps for each server VM:

    1. Install Windows Server 2012.

    2. Install SQL Server Enterprise Edition.

      • Select Database Engine Services and Management Tools – Basic.
      • Specify SQLSVC for the service account of the SQL Server Database Engine.
      • Set SQL Browser Service to Automatic.
      • Select Windows Authentication.
      • Add the current user account that is being used to install SQL Server as Sys Admin. You will also want to add the ADRMSADMIN account as Sys Admin. (This is the installer account used in the AD RMS TLG.)

    3. Configure firewall exceptions (TCP 1433, UDP 1434).

    4. Register the SPNs for both SQL1 and SQL2 to the SQLSVC account.

      // To register the service account for the FQDN of the SQL servers.

      Setspn –A MSSQLsvc/<SQL1 FQDN> <domain>\SQLSVC

      Setspn –A MSSQLsvc/<SQL2 FQDN> <domain>\SQLSVC

      Setspn –A MSSQLsvc/<SQL1 common name> <domain>\ SQLSVC

      Setspn –A MSSQLsvc/<SQL2 common name> <domain>\ SQLSVC

       Troubleshooting Tips:

      • If there is a “duplicate” error, run the Setspn command on DC1 to register the account.
      • If there is an SSPI error when trying to connect to the SQL, run gpupdate to refresh policy.

  4. Install the AD RMS server role on APP1 using SQL1 for the SQL Server / AD RMS database server instance.

  5. Verify the AD RMS configuration.

    To verify the AD RMS configuration you can follow the process described in Step 4: Verify the AD RMS Configuration after you complete the configuration in the AD RMS base TLG.

  6. Backup and restore the SQL databases.

    1. On the mirror server, SQL2, create a login for ADRMSSVC. (This will be the AD RMS service user account if you re following the procedure that was provided in the AD RMS base TLG. Note that AD RMS installation will create this login on SQL1, but it needs to be created manually for SQL2.)

    2. On the principal server, SQL1, set the Recovery model to Full for each of the following AD RMS databases. (This is located under Database Properties / Options for each SQL database in SQL Server Management Studio).

      DRMS_Config_<cluster>_<port>

      DRMS_DirectoryServices_<cluster>_<port>

      DRMS_Logging_<cluster>_<port>.

    3. On the principal server, SQL1, create Full backups (using Full Recovery Model) for each of the three DRMS databases listed previously.

      • Launch SQL Server Management Studio.
      • Select Database / Tasks / Back Up.
      • For Recovery model, select Full.
      • For Backup type, select Full.
      • For Backup component, select Database.
      • Note the backup set name and destination.

    4. On the principal server, SQL1, create Transaction Log backups (using Full Recovery Model) of the three DRMS databases listed previously.

      • Launch SQL Server Management Studio.
      • Select Database / Tasks / Back Up.
      • For Recovery model, select Full.
      • For Backup type, select Transaction Log.
      • For Backup component, select Database.
      • Note the backup set name and destination.
      • Confirm Options are set to append to existing set.

    5. On the mirror server, SQL2, copy the three backup files from the principal server.

    6. On the mirror server, SQL2, restore each of the three backup files by repeating the following for each file:

      • Launch SQL Server Management Studio.
      • Select Databases / Restore Database.
      • Select From device and browse to backup location.
      • Select to restore Full (only) backup set.
      • Select the database name.
      • On Options, select RESTORE WITH NORECOVERY.
      • Click OK.
      • Select Databases / Restore Database.
      • Select From device and browse to backup location.
      • Select to restore Transaction Log backup set.
      • Select the database name.
      • On Options, select RESTORE WITH NORECOVERY.
      • Click OK.

    7. On the mirror server, SQL2, confirm all three DRMS databases are marked “Restoring...“.

  7. Confirm server trust.

    (The servers involved in a database mirroring session must trust each other.  This means that each SQL Server instance login must have rights to connect to the other mirroring server—to the endpoints of the other SQL server instance. This is done by creating a login on each server for the mirroring server’s login.  Because SQL1 and SQL2 are using the same service account for their logins, this step was skipped.)

  8. Establish database mirroring endpoints for all three DRMS databases.

    1. Turn off the firewall on both SQL1 and SQL2.

    2. On the principal server, SQL1, launch SQL Server Management Studio.

    3. Select Database / Tasks / Mirror.

    4. Click Configure Security to launch the wizard.

    5. Select No to not include a witness.

    6. Accept the default port (5022) and name (Mirroring) for SQL1, principal server.

    7. Click Connect and enter the FQDN of SQL2. (Note: Port and endpoint name are auto-populated.)

    8. Enter SQLSVC as the service account for both the principal and the mirror.

    9. Confirm the choices and click Finish.

    10. Click Start Mirroring.

    11. On the Database properties page, select High performance (asynchronous) mode.

    12. Click OK.

    13. Confirm the principal database shows Principal, Synchronized and that the mirroring is still set to Asynchronous mode.

      Troubleshooting:

      • If there is a connection error, run this and then retry the previous steps:

        GRANT CONNECT ON ENDPOINT::Mirroring TO [<domain>\SQLSVC]

        GO

      • To check if endpoints are created/started, run this command on principal and mirror:

        SELECT * FROM sys.database_mirroring_endpoints

        GO

         

  9. Edit the AD RMS registry to reflect mirroring.

    1. Open this registry node on the AD RMS server: 

      [HKLM\SOFTWARE\Microsoft\DRMS\ConnectionString]

    2. Add the failover partner to this value:

      "ConfigDatabaseConnectionString"="Data Source=SQL1; Failover Partner=SQL2; Integrated Security=SSPI;Persist Security Info=False;Packet Size=4096;Database=DRMS_Config_<cluster>_<port>;"

       

  10. Edit policy values in the DRMS_Config database to reflect mirroring.

         Execute query on the principal to update three policy values:

    • CertificationUserKeyStorageConnectionString
    • DirectoryServicesCacheDatabase
    • LoggingDatabaseServer

    (Insert real names for brackets:  cluster, port, SQL1, SQL2)

    USE DRMS_Config_<cluster>_<port>

    GO

    UPDATE dbo.DRMS_ClusterPolicies

    SET PolicyData='data source=<SQL1>;failover partner=<SQL2>;integrated security=SSPI;persist security info=False;packet size=4096;database=DRMS_Config_<cluster>_<port>'

    WHERE PolicyName='CertificationUserKeyStorageConnectionString'

    GO

    UPDATE dbo.DRMS_ClusterPolicies

    SET PolicyData='data source=<SQL1>;failover partner=<SQL2>;integrated security=SSPI;persist security info=False;packet size=4096;database=DRMS_Config_<cluster>_<port>'

    WHERE PolicyName='DirectoryServicesCacheDatabase'

    GO

    UPDATE dbo.DRMS_ClusterPolicies

    SET PolicyData='<SQL1>;failover partner=<SQL2>;initial catalog=DRMS_Logging_<cluster>_<port>'

    WHERE PolicyName='LoggingDatabaseServer'

    GO

     

  11. Verify the AD RMS configuration again.

    To verify the AD RMS configuration you can repeat the process that was used in previous steps (as described in Step 4 in the AD RMS base TLG).

  12. Do a force service failover from the principal server (SQL1) to the mirror server (SQL2).

    1. On the principal server, SQL1, stop the SQL Server service:  net stop mssqlserver

    2. On the mirror server, SQL2, execute this query for each database (editing cluster name and port number as indicated in brackets): 

      ALTER DATABASE DRMS_Config_<cluster>_<port> SET PARTNER
FORCE_SERVICE_ALLOW_DATA_LOSS
GO
ALTER DATABASE DRMS_DirectoryServices_<cluster>_<port> SET PARTNER
FORCE_SERVICE_ALLOW_DATA_LOSS
GO
ALTER DATABASE DRMS_Logging_<cluster>_<port> SET PARTNER
FORCE_SERVICE_ALLOW_DATA_LOSS
GO

     

    1. On the mirror server, SQL2, after a few seconds wait, confirm that it is now the principal server. To confirm, note the following:
      • Database nodes should say Principal, Disconnected.
      • Database properties (Mirror tab) should list SQL2 as Principal.

  13. Verify the AD RMS configuration again.

    To verify the AD RMS configuration, repeat the same process that was used in previous steps.