SQL Server Reporting Service Application 2012 DR Procedures

  • Article

I was tasked to find out and test the detailed steps to handle DR for SSRS 2012 service application on SharePoint 2010 and in this blog I will share the steps I took to move SSRS 2010 service application from “production” to DR environment.

Environment:

image

Prepare the environment:

  • DNS A record: drtestweb.contoso.com – 10.0.0.200
  • Kerberos settings (I will omit the FQDN part of the settings in the blog)
    • setspn –a MSSQLSvc/LAB-SQL2008R2 contoso\sp_sql
    • setspn –a HTTP/drtestweb contoso\sp_app
    • KCD: allow contoso\sp_c2wts to delegate SPNs on contoso\sp_sql for any protocol  (SSRS 2012 SA is claim aware and is similar to PPS and Excel Service – please refer to TechNet document how to configure Kerberos for PPS and Excel Service)
  • create production farm on SP2010DRTEST1
    1. configure C2WTS service to run under contoso\sp_c2wts account and also assign “act as part of OS” permission and make it local admin
    2. install SQL Server 2012 Reporting Service Application for SharePoint 2010 and create SSRS SA and also configure the unattended execution account
    3. create web application https://drtestweb.contoso.com using Team site for root web
    4. create a document library SSRS2012 and add 3 SSRS content types to this library
    5. create two data sources:
      1. dsUserCredential – this will access LAB-SQL2008R2 using end user’s credential (Kerberos)
      2. dsUnattendedAccount – this will access LAB-SQL2008R2 using unattended account
    6. create one report for each of the above data source (note – it seems like a bug that you could not use the data source that is configured using unattended account to create report but you can temporarily switch to use user credential to create the report and then switch back to unattended account after report created)
  • create DR farm on SP2010DRTEST2
    • make sure to use same service accounts as production farm (Kerberos will just work after switch)
    • configure C2WTS service to run under contoso\sp_c2wts account and also assign “act as part of OS” permission and make it local admin
    • install SQL Server 2012 Reporting Service Application for SharePoint 2010 – DO NOT create SSRS SA
    • create web application https://drtestweb.contoso.com and then delete the content database
  • TEST production farm
    • go to Win7client, make sure the two reports are working as expected

BCM steps:

  • from SSRS SA, backup the encryption key and copy this key to a safe place and note the password in a secret place
  • for SharePoint content database, you can either choose log shipping or async database mirroring to sync production database SQL to DR SQL server
  • for 3 SSRS databases, TechNet says log shipping and database mirroring is not supported so what you need to do for those 3 databases is to have a schedule for full backup and log backup and copy the backup files offsite for DR purpose. [UPDATE] we might change the word not support soon because when we say not supported we really meant in some cases the SQL agent job cannot automatically failover. so for DR purpose, we can use log shipping or async database mirroring to make the production database in sync with DR site.
  • In this test, I will use simple backup and restore for all 4 databases

In case of Disaster, here are the steps for Recovery (the following steps are in DR farm):

  1. assume we have the encryption key and all database backups
  2. restore content database and 3 SSRS database
  3. create SSRS SA and specify the name of the database to the database name you restored from the backup
  4. in the SSRS key management, restore the encryption key
  5. configure the unattended account – make sure using the same unattended account you used in production
  6. attach the content database to web application https://drtestweb.contoso.com

TEST DR

  1. shutdown SP2010DRTEST1 server (mimic Disaster)
  2. go to Win7Client you will not be able to access the site
  3. go to DC, change the DNS A record drtestwebapp to point to 10.0.201 (DR farm box)
  4. go back to Win7Client machine, issue ipconfig /flushdns, verify you can ping drtestwebapp to resolve 10.0.0.201 and then test both reports and it worked for me

Hope this will benefit someone else who are interested in how to handle SSRS 2010 SA DR. Happy DR!!!