Share via

Migrating the AD RMS Database from Windows Internal Database (WYukon) to SQL Server 2005 SP2

  • Article

Overview

When an organization chooses to migrate its Active Directory Rights Management Services (AD RMS) databases from Windows Internal Database (WYukon) on the AD RMS server to a separate SQL server, the migration process can be complex and challenging. This article steps you through the process of migrating these databases from WYukon to SQL Server 2005 SP2. It describes the actions that you need to take to prepare for the migration, the steps of the migration itself, and help for troubleshooting SQL connection problems.

This article presents a high-level description of the tasks required to perform the migration. It is assumed that you are familiar with the various tools and methods required to administer computers running SQL Server and AD RMS.

Preparing for the migration

Before you begin the actual migration, you should first prepare your network infrastructure by performing the following tasks:

  • Prepare the server that will receive the AD RMS databases by installing Microsoft Windows Server 2008 or Windows Server 2008 R2 and SQL Server 2005 with Service Pack 2 (SP2) and joining it to the same domain as the AD RMS server.
  • Make sure that these servers are assigned static IP addresses.
  • If necessary, set a firewall exception for TCP ports 445 (for SQL Server Named Pipes) and 1433 (or the TCP port that the SQL Servers are configured to listen on) on each new SQL Server cluster. For more information about firewall settings for AD RMS and SQL Server, see AD RMS Firewall Considerations in the Microsoft TechNet Library.
  • Ensure that you can access all computers and that you have administrator privileges on them.
  • Create a login for the AD RMS service account on the new SQL Server.
  • Plan which SQL Server backup jobs and integrity checks will be enabled on the new SQL Server after the migration is complete.
  • Make sure that you have prepared a writable destination for databases that you will copy and that the destination can be accessed from the new SQL Server.
  • Download the Microsoft SQL Server Native Client and Microsoft SQL Server 2005 Command Line Query Utility from http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en and install them on the AD RMS server.

Although not required to perform the migration, the following are best practices that you should consider implementing as part of the migration:

  • Ensure that a CNAME record is registered in DNS for the AD RMS cluster and that it works for the new SQL Server computer.
  • Ensure that a CNAME record is registered in DNS for the new SQL Server computer and that it works for the AD RMS server.
    In SQL Server 2008 and later, it might be necessary to disable strict name checking by setting the following registry value to 1 and then resetting SQL Server:
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters
    DWORD: DisableStrictNameChecking

Performing the migration

Perform the following procedures in the indicated order to complete the migration.

Prepare the AD RMS server

Log on to the AD RMS server and perform all of these steps.

  1. To provide for failsafe recovery in case of a problem with the migration, export the Trusted Publishing Domain (TPD) to a safe location:
    1. Open the Active Directory Rights Management Services console, and then expand the AD RMS cluster.
    2. In the console tree, expand Trust Policies and then click Trusted Publishing Domains.
    3. In the results pane, select the certificate for the domain you want to export.
    4. In the Actions pane, click Export Trusted Publishing Domain.
    5. In the Publishing domain file box, type the name of the publishing domain file you are exporting or click Save As to export it to a special location. Make sure you specify the .xml file name extension.
    6. In the Password and Confirm password boxes, type a strong password that will be used to encrypt the trusted publishing domain file. You need this password to import this file into another AD RMS cluster.
    7. Click Finish to create the trusted publishing domain file.
  2. Stop IIS services:
    1. Open a command prompt with Administrator privileges.
    2. Type the following command, and then press Enter: 
      iisreset /stop
  3. Confirm that the message queue is empty:
    1. Start Server Manager.
    2. Expand Features, Message Queuing, Private Queues, DRMS_Logging_<cluster>_<port>.
    3. Click Queue Messages.
    4. If the queue is not empty, wait until it is.
  4. Stop the AD RMS Logging service:
    1. Open a command prompt with Administrator privileges.
    2. Type the following command, and then press Enter:
      net stop adrmsloggingservice
  5. Detach the DRMS databases:
    1. Open a command prompt with Administrator privileges.
    2. Type the following command, and then press Enter:
      cd %programfiles%\Microsoft SQL Server\90\Tools\binn
    3. Type the following command, and then press Enter:
      sqlcmd –S \.\pipe\mssql$microsoft##ssee\sql\query –E
      The command prompt changes to 1>.
    4. Type the following commands, pressing Enter after each one:
      EXEC sp_detach_db DRMS_Config_*<cluster_port>
      *GO
    5. Type the following commands, pressing Enter after each one:
      EXEC sp_detach_db DRMS_DirectoryServices_*<cluster_port>
      *GO
    6. Type the following commands, pressing Enter after each one:
      EXEC sp_detach_db DRMS_Logging_*<cluster_port>
      *GO
    7. Type the following command, and then press Enter:
      Exit
    8. Type the following command, and then press Enter:
      cd %systemroot%\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data)
    9. Type the following command, and then press Enter:
      copy DRMS_*.?df <shared_folder>
      Where <shared_folder> is a shared folder that can be accessed from the destination SQL Server.

Migrate the databases to the destination SQL Server

Log on to the target SQL Server and perform the following steps.

  1. Copy the .mdf and .ldf files from the shared directory to the SQL data directory on the SQL Server (usually %programfiles%\Microsoft SQL Server\MSSQL.1\MSSQL\Data).
  2. Attach the AD RMS databases in the destination SQL Servers:
    1. Start SQL Service Management Studio with Administrator privileges.
    2. Right-click Databases and then click Attach.
    3. Under Databases to add, click Add.
    4. Click one of the .mdf files that you copied in Step 1, and then click OK.
    5. Repeat Steps 3 and 4 for the remaining .mdf files that you copied in Step 1.
  3. Edit the ClusterPolicies table for the new SQL Server name (or its CNAME alias)
    1. If necessary, start SQL Service Management Studio with Administrator privileges.
    2. Expand the following nodes:
      Databases
      **    DRMS_Config_<cluster>_<port>
      **        Tables
    3. Right-click dbo.DRMS_ClusterPolicies, and then click Open Table.
    4. Find the PolicyName and the PolicyData columns in the table.
    5. Under the PolicyName column, locate LoggingDatabaseServer, and then replace the corresponding value in the PolicyData column with the SQL Server name or alias.
    6. Under the PolicyName column, locate CertificationUserKeyStorageConnectionString, and then replace the server identifier between data source= and the next semicolon (;)with the SQL Server name or alias.
    7. Under the PolicyName column, locate DirectoryServicesCacheDatabase, and then replace the server identifier between data source= and the next semicolon (;) with the SQL Server name or alias.

Configure the AD RMS server to use the new SQL Server

Log on to the AD RMS server and perform the following steps.

  1. Start Registry editor (click Start, type regedit.exe, and then press Enter).
  2. Expand the following nodes:
    HKEY_LOCALMACHINE
    **    SOFTWARE
            Microsoft
                DRMS
                    [2.0]**
  3. Click ConnectionString, double-click ConfigDatabaseConnectionString, and then replace the server name following datasource= with the SQL Server name or alias.
  4. Expand the following nodes:
    HKEY_LOCALMACHINE
    **    SYSTEM
            ControlSet001
                Services
                    AdRmsLoggingService**
  5. If it exists, click Params and do the following:
    1. Double-click ConnectionString, replace the server identifier between data source= and the next semicolon (;) with the SQL Server name or alias, and then click OK.
    2. Double-click LoggingDatabaseServer, and then replace the server name with the SQL Server name or alias, and then click OK.
  6. Expand the following nodes:
    HKEY_LOCALMACHINE
    **    SYSTEM
            ControlSet002
                Services
                    AdRmsLoggingService**
  7. If it exists, click Params and do the following:
    1. Double-click ConnectionString, replace the server name following datasource= with the SQL Server name or alias, and then click OK.
    2. Double-click LoggingDatabaseServer, and then replace the server name with the SQL Server name or alias, and then click OK.
  8. Restart the AD RMS Logging service:
    1. Open a command prompt with Administrator privileges.
    2. Type the following command, and then press Enter:
      net start adrmsloggingservice
    3. To confirm that the service is running, type the following command, and then press Enter:
      sc query adrmsloggingservice
  9. Reset IIS:
    1. Open a command prompt with Administrator privileges.
    2. Type the following command, and then press Enter:
      iisreset
  10. Verify that the AD RMS server can connect to the SQL Server by opening the Active Directory Rights Management Services console. If an error message appears, see “Troubleshooting SQL Server connection errors,” at the end of this article.

Troubleshooting SQL Server connection errors

Do the following if this error message appears when you open the AD RMS console: “Cannot open database … requested by the login. The login failed. Login failed for user….”

  1. Log on to the SQL Server and start SQL Server Management Studio with Administrator privileges.
  2. Expand the following nodes:
    Security
    **    Logins**
  3. Double-click the AD RMS service account.
  4. In the Login Properties dialog box, click User Mapping.
  5. Ensure that the checkboxes next to the three DRMS databases are selected.
  6. If selecting one or more of these checkboxes fails, delete the AD RMS service account from the list of users for each DRMS database (under Databases\<DRMS_database>\Security\Users) and then select the checkbox in the AD RMS service account user mapping table again.

 

See Also