Tuesday, March 24, 2015

ADRMS, SQL mirroring, and named instances

First and foremost, I need to thank my PSS colleagues for their assistance on this case. It was open for quite some time, but Samesh Panicker and Kaustubh Dwivedi were critical to my success in this configuration. Here's the scenario:
  • Two ADRMS servers running on Windows 2012 R2
  • Two SQL servers, SQL 2012 on Windows 2012 R2 with a SQL express witness
    • Since we colocated with another SQL instance, I had to use named instances (more on this soon)
  • An F5 Hardware load balancer (HLB) publishing an internal and external VIP using split brain DNS to resolve rms.domain.com to the correct VIP


The article that I followed was from the Technet Wiki here:
http://social.technet.microsoft.com/wiki/contents/articles/14977.test-lab-guide-configuring-ad-rms-with-sql-mirroring-in-windows-server-2012.aspx

This is required reading for the rest of the post, as I am only going to highlight key differences from that guide.


Step 3d 
There are a few things on using setspn. First, most people are not familiar with it. Even those who are, use it infrequently. I am OK running setspn.exe, but you need to know how to check and then delete any mistakes. I tend to use ADSIedit here. You are adding Service Principal Names (SPNs) to the SQL service account. So in ADSIiedt, you drill down to that account (not the SQL server computer objects) to set, edit, or delete SPNs.

Keep in mind, his example shows:
Setspn –A MSSQLsvc/[sql1 fqdn] [domain]\SQLSVC
Setspn –A MSSQLsvc/[sql2 fqdn] [domain]\SQLSVC
Setspn –A MSSQLsvc/[sql1] [domain]\SQLSVC
Setspn –A MSSQLsvc/[sql2] [domain]\SQLSVC

A few changes and clarifications here.
  • MSSQLSvc is a static field, that isn't your account name (his example using SQLSVC makes this confusing IMO)
  • If you are using a named instance for SQL, you need to specify it.
This is done as shown below.



So to correct the above paste from the wiki (needed only if specifying an instance name)


Setspn –A MSSQLsvc/[sql1 fqdn:INSTANCE] [domain]\ADRMSSQLSVC
Setspn –A MSSQLsvc/[sql2 fqdn:INSTANCE] [domain]\ADRMSSQLSVC
Setspn –A MSSQLsvc/[sql1:INSTANCE] [domain]\ADRMSSQLSVC
Setspn –A MSSQLsvc/[sql2:INSTANCE] [domain]\ADRMSSQLSVC

If you are running more than a single instance of SQL on a server or servers in my case, you cannot use the same mirroring port if another instance is using it. TCP/5022 is the default port - if another SQL instance is already using mirroring on that port, you will need to pick a new port. Be sure to do a netstat -ano and ensure its not in use first, then follow this guide to change your mirror port: http://www.macaalay.com/2012/10/10/altering-mirroring-endpoints-ports-on-sql-server/  

Step 10
First off, PSS recommends you not really edit SQL using transact. It's totally acceptable if you are strong in SQL, but they really prefer you use RMSConfigEditor.exe from the RMS toolkit . And yes that is from 2006 and doesn't say anything about 2012 or 2012 R2, but it still works.

Second, and I see Kaustubh has already updated the wiki, but the Logging Database SHOULD NOT have the "data source=" like the CertificationUserKeyStorageConnectionString and the DirectoryServicesCacheDatabase . When we specified it with that connection string, logging/reporting functions did not work in the RMS Admin console, and RMS was broken if the database was hosted on the SQL02 server.

Another thing to note, as it’s a simple mistake to make, but your Database= string, the [cluster] will always be host_domain_com, and NOT host.domain.com. If you are working on issues with RMS late at night, or after many hours at it, this can be pretty painful to mistake, so do check it!

No comments: