AD RMS: Upgrade Migration with a Named SQL Instance

  • Article

The Scenario:

You read my previous article but it does not cover how to do an upgrade migration if you are moving to a new named instance in SQL.  Although it is very similar to the Version 2, there are some important call-outs if you want to have a pain free upgrade experience.  I have tested these in my lab using AD RMS on Windows Server 2012 R2 and a new named instance of SQL Server 2012 SP3 called ADRMS running on a static port of 1414.

Upgrading AD RMS (Version 3):

  1. If a CNAME does not yet exist for SQL, create a new CNAME in DNS to use for the AD RMS SQL server
  2. Restore the AD RMS databases to the new SQL server named instance
  3. Open SQL Server Managment Studio on the new SQL server and connect to the new named instance
    1. Expand Security
    2. Right-click on Logins and select New Login...
    3.  Click Search...
    4. Change the Locations... to Entire Directory
    5. Enter the name of your AD RMS Service Account and click Check Names then OK
    6. Click OK to complete adding the service account
    7. Expand Databases
    8. Expand the Configruation Database (Should be similar to DRMS_Config_adrms_contoso_com_443)
    9. Expand Tables
    10. Right-click on dbo.DRMS_ClusterPolicies and select Edit Top 200 Rows
    11. Modify the PolicyData corresponding to the following entries in the PolicyName column removing the existing SQL Server and adding the CNAME created for the new SQL server plus "\<InstanceName>" (In my case it would be SQL.CONTOSO.COM\ADRMS)
      • LoggingDatabaseServer
      • CertificationUserKeyStorageConnectionString
      • DirectoryServicesCacheDatabase
    12. Close Sql Server Management Studio
  4. Open SQL Server Configuration Manager (this is only if you are setting a static port for the named instance)
    1. Expand SQL Server Network Configuration
    2. Click on Protocols for <InstanceName>
    3. Ensure TCP has a status of Enabled
    4. Right-click on TCP and select Properties
    5. Select the IP Addresses tab
    6. Scroll down to the IPAll section, enter your port number, then click Apply and OK.
    7. Click on SQL Server Services
    8. Verify that the SQL Server Browser Service is running and is set to a Start Mode of Automatic
    9. Right-click on SQL Server (<InstanceName>), and select Restart
  5. Open a command prompt on the SQL server and run Netstat -ano > %Temp%\netstat.txt
  6. Type Start %Temp%\netstat.txt and verify that you see an entry like "TCP    0.0.0.0:1414           0.0.0.0:0              LISTENING       <PID>"
  7.  Verify that the Windows Firewall and any firewalls between the SQL Server and the new AD RMS server allow your new port over TCP and the SQL Server Browser Service (1434) over UDP
  8. On the new AD RMS server, import the SSL Certificate to the Local Computer>Personal store
  9. Open an administrative Notepad.exe and browse to C:\Windows\System32\drivers\etc and open the hosts file
    1. Create an entry in the hosts file redirecting the CNAME for AD RMS to the local server (Actual IP, not loopback)
    2. Create an entry in the hosts file redirecting the CNAME for the SQL server to the new SQL server (Actual IP, not loopback)
      e.g.
      adrms.contoso.com       10.0.0.25
      sqladrms.contoso.com  10.0.0.26
  10. Install the AD RMS Role on the new 2012 R2 server
  11. After installation completes, select the link to Perform additional configuration
    1. The server will detect the SCP in Active Directory and default to Join an existing AD RMS cluster.  There is a warning that it cannot contact the root cluster.  This is because the SCP is being redirected to this server via the hosts file.  Leave this as is and click next. 
    2. On the Select Configuration Database page, enter the CNAME you specified in the hosts file for the new SQL server (FQDN)
    3. Click List on the second line and select the DefaultInstance from the drop-down
    4. Drop down the Configuration Database Name selection box and select your configuration database
    5. Enter the private key password corresponding to the old database
    6. Enter credentials for the original AD RMS service account
    7. Select Default Website
    8. On the Choose a Server Authentication Certificate page, select the imported SSL certificate and click Next
    9. Click Install to begin the installation
    10. After configuration completes, you will need to log off and back on to administer AD RMS
  12. If you are using primarily Kerberos based authentication, you may need to follow the instructions at https://technet.microsoft.com/en-us/library/dd759186(v=ws.11).aspx to modify your application pool and set Service Principal Names.
  13. Modify the hosts file on several clients to point to the new AD RMS server
  14. Test consuming existing content and protecting new content against the new server
  15. After you are satisfied that the 2012 R2 AD RMS server is doing what you want it to do, you can add any additional nodes to the 2012 R2 AD RMS cluster you now have
  16. Open SQL Server Managment Studio on the new SQL server and navigate to the configuration Database
    1. Expand Tables
    2. Right-click on dbo.DRMS_ClusterServers and select Edit Top 200 Rows
    3. Remove any old servers from the table by deleting each row
  17. Finally, switch over the CNAMEs in DNS and all your clients will start talking to the new servers
  18. When you are comfortable with the new environment, you can gracefully shut down and reallocate the old resources.

Wrapping Up:

As you can see, there is a reason I didn't dive into all of that in the first article.  There are a lot of little things that you have to touch when moving to a new SQL Server Named Instance and static port.  Please let me know if you notice anything I have missed.  Like I said above, I tested this in my lab environment several times so it should work without any issues, but I know production environments can sometimes be more challenging.

Kevin McKinnerney
PREMIER FIELD ENGINEER – PLATFORMS/SECURITY