Disaster Recovery with SharePoint Server 2013 and SharePoint Server 2016 Access Services

This blog is a preview to a Microsoft TechNet article that will appear in the near future: The blog will be updated with the link nearer the time.
The goal of this article is to fill a gap in the current documentation of Access Services in SharePoint Server 2013 and SharePoint Server 2016 by providing guidance that explains how to successfully implement a disaster recovery (DR) strategy for Access Services service applications.
TIP   The same architecture that was used in SharePoint Server 2013 implementations of Access Services is used in SharePoint Server 2016 and the method of setup and deployment remains the same.

 

Access Services in brief

SharePoint 2010 introduced the concept of Access Services as an integrated service application (https://technet.microsoft.com/en-us/library/ee748634(v=office.14).aspx). The data was held in SharePoint lists and could be accessed via a browser or via the Microsoft Access 2010 Client.

In SharePoint 2013 the architecture of Access Services changed, introducing the concept of constrained databases, moving the data out of SharePoint lists and instead into a SQL Server 2012 Application Database.

 

Basic Steps to be aware of

As mentioned a more comprehensive article is in production but for now let us review the key elements

1 Setting up SharePoint Server for Disaster Recovery

There are a number of ways to configure your SharePoint Server farm for disaster recovery. The method you choose will depend entirely on your requirements for allowed data loss and minimum downtime in your organization. Microsoft has documented a number of approaches here: https://technet.microsoft.com/en-us/library/ff628971.aspx?f=255&MSPPError=-2147217396

Regardless of your choice of technologies there are a few requirements and best-practices for configuring a disaster recovery farm to support Access Services. These are detailed below.

Use the same Authentication Realm

Setting a new authentication realm blocks access for all SharePoint apps that use access tokens. Access Services applications rely on the app infrastructure. With this it mind in makes sense for a disaster recovery farm to use the same authentication realm as the primary farm. You should set the authentication realm as one of the initial setup steps when deploying the DR farm.

You can get the AuthenticationRealm of the primary farm by using Windows PowerShell:

 Get-SPAuthenticationRealm

 4a2cc8f8-51ab-4367-8a76-ab629c882a68

And set it on the secondary farm by using Windows PowerShell:

 Set-SPAuthenticationRealm -Realm 4a2cc8f8-51ab-4367-8a76-ab629c882a68

 Restart-Service sptimerv4

 Restart-Service spadminv4

NOTE Restarting the SharePoint Timer service and SharePoint Admin service is recommended after changing the Authentication Realm. You may need to schedule time during which you can IISReset (SharePoint sites will be unavailable until the successful end of an IISReset).

Use the same Database Server ReferenceID

Access Services 2013 and 2016 use a SQL Server database server to host the individual databases that support Access-based Apps. Internally, these database servers aren’t referenced by name, but by a ReferenceID. It’s critical to the success of your disaster recovery strategy that the database servers in the secondary datacentre be registered as application server hosts using the exact same ReferenceID as their primary partner. This can only be done by registering the database servers by using Windows PowerShell.

First register the primary farm’s Access Services database server

 $serverGroupName = 'DEFAULT'

 $ASapp = Get-SPAccessServicesApplication

 $app = $Null

 if ($ASapp.length -ne $Null) { $app = $ASapp[0] } else { $app = $ASapp }

 $context = [Microsoft.SharePoint.SPServiceContext]::GetContext($app.ServiceApplicationProxyGroup, [Microsoft.SharePoint.SPSiteSubscriptionIdentifier]::Default)

 $ServerRefID = [System.Guid]::NewGuid().toString()

 $newdbserver = New-SPAccessServicesDatabaseServer -ServiceContext $context -DatabaseServerName "<PrimaryDatabaseServerName>" -DatabaseServerGroup $serverGroupName -ServerReferenceId $ServerRefID -AvailableForCreate $true

#Write the ServerRefID to the screen for use when registering the secondary farm Access Services Database Server

 $ServerRefID

 

Next, register the secondary farm’s Access Services database server

 $serverGroupName = 'DEFAULT'

 $DatabaseServerName = "<Secondary Access Database Server>"

 $ASapp = Get-SPAccessServicesApplication

 $app = $Null

 if ($ASapp.length -ne $Null) { $app = $ASapp[0] } else { $app = $ASapp }

 $context = [Microsoft.SharePoint.SPServiceContext]::GetContext($app.ServiceApplicationProxyGroup, [Microsoft.SharePoint.SPSiteSubscriptionIdentifier]::Default)

 $newdbserver = New-SPAccessServicesDatabaseServer -ServiceContext $context -DatabaseServerName "SecondaryDatabaseServerName" -DatabaseServerGroup $serverGroupName -ServerReferenceId "<PrimaryServerRefID>" -AvailableForCreate $true

 #<PrimaryServerRefID> in the above script represents the same ServerRefID from the primary farm registration

You can reference as many Access Services Application Database Servers as you need. In this simple scenario we only have one. If you have many, make sure you track the registrations and ensure that in recovery the databases are recovered correctly to the matched server in the DR site.

Know the databases that support the Access Services Service Application

Access Services 2013 and 2016 doesn’t have its own service application database but does have a tight dependency on multiple other databases in a SharePoint farm.

These databases need to be managed as a part of your Disaster Recovery Strategy.

1. 1. App Management Database - contains Access app registrations and app principals.
   2. Subscription Settings Database - manages the unique identities provided to Access apps to create the URL for the Access Application.
   3. Secure Store Database - the Secure Store service can be leveraged to provide alternate authentication methods for Access Apps. The guide referred to earlier doesn’t cover doing this, but we will add the Secure Store Database to our strategy for completeness.
   4. SharePoint Content Database - these databases contain the site collections into which Access Apps have been deployed.
   5. Access Services Application Databases - The databases containing the actual data you need to preserve for the Access Services application to function.

As mentioned already, the chosen disaster recovery approach depends on your Recovery Time Objective (RTO) and Recovery Point Objective (RPO), how long can you be offline, and how much data can you afford to lose in the event of a disaster. Regardless of the selection, the recovery process for Access Services remains the same.

2 Recovery After Failover

After failing-over to the secondary datacenter you need to use the five different database types to regenerate the Access Services App Infrastructure on the disaster recovery farm.

NOTE This article only deals with the five mentioned database types. To successfully recover a full SharePoint Server farm after a datacenter failover, additional steps are needed and the reader is directed here to review those steps here https://technet.microsoft.com/en-us/library/cc263031.aspx.

In the test infrastructure here this means the databases are recovered from the Primary SQL Server SQL01 to the Secondary SQL Server SQL02 in the DR site.

1. 1. App Management Database
   2. Subscription Settings Database
   3. Secure Store Database
   4. SharePoint Content Database
   5. Access Services App Databases

We can use the techniques described here to recover these service applications and attach the content database

 

First, recreate the service applications from the restored/recovered databases

Use these Windows PowerShell commands:

1. Application Management database and proxy:

 $AppPool = Get-SPServiceApplicationPool -Identity "<Services Application Pool Name> "

 $AppDatabasename = "<restored App Management database name>"

 $appman = New-SPAppManagementServiceApplication -Name "App Management" -DatabaseServer "<SecondaryDatabaseServerName>" -DatabaseName $AppDatabaseName -ApplicationPool $AppPool

 $appmanproxy = New-SPAppManagementServiceApplicationProxy -Name "App Management Proxy" -ServiceApplication $appman

 

2. Subscription Settings database and proxy:

 $SubDatabasename = "<restored Subscription Settings database name>"

 $subset = New-SPSubscriptionSettingsServiceApplication -Name "Subscription Settings" -DatabaseServer "<SecondaryDatabaseServerName>" -DatabaseName $SubDatabaseName -ApplicationPool $AppPool

 $subsetproxy = New-SPSubscriptionSettingsServiceApplicationProxy -Name "Sub Settings Proxy" -ServiceApplication $subset

 

3. Secure Store database and proxy:

 $SecDatabasename = "<restored Secure Store database name>"

 $secstore = New-SPSecureStoreServiceApplication -DatabaseServer "<SecondaryDatabaseServerName>" -DatabaseName $SecDatabaseName -ApplicationPool $AppPool

 $secstoreproxy = New-SPSecureStoreServiceApplicationProxy -Name "Secure Store Proxy" -ServiceApplication $secstore

 

Also note that if you are using the secure store in the secondary farm you will need to generate a new secure store encryption key before you can leverage any Applications registered there.

Next, attach the content database(s)

Mount the failover content databases can to the appropriate web application on the DR farm by using Windows PowerShell:

 

 Mount-SPContentDatabase -WebApplication "<https://DRWebApp>"  -Name “<Database name>" -DatabaseServer "<SecondaryDatabaseServerName>"

 

Lastly recover the Access Services App Databases

Again as with the other databases your choice of technique depends on the RTO and RPO. However, to carry out the recovery all you need to do is restore, or recover, the databases to the secondary server that has been correctly registered in Access Services using the ServerReferenceID of the primary database server. This is detailed in Use the same database server ReferenceID, above.

 

3 Configuration Actions Post Failover

At this point we have almost everything we need to support Access Services in Disaster Recovery conditions. The last two items we need to do are:

- Set the app domain URLs.

- Ensure the Access Services application database logins have been carried over from the Primary Site to the Secondary.

Set up Apps Domains in the secondary site

The key elements to consider here are the domains you had specified in the primary site and the domains you intend to use in the secondary DR site. If you plan to use the same domains, repoint the CNAME record for the SP Apps domain to the secondary SharePoint server, for example repoint *.contosoapps.com to the secondary SharePoint Server.
Also, make sure you setup the App Urls in Central Admin on the DR site. Open Central Admin, select Apps and choose Configure App Urls. Recovering the App Management Database does not preserve the App Domain even though it does preserve the App Prefix.

Failing to set the App Domain will result in a DNS lookup failure and a site not found error in the browser.

Set up Access Database Logins for the secondary site

Access Services requires the Contained Databases feature of SQL Server, which supports contained database logins. However, Access Services in SharePoint 2013 and 2016 only partially leverages this feature, and so the database logins are actually stored in the Master DB, just like any other login. The downside to this is that on failover we need to regenerate any missing logins and ensure we set the same password for the account.

Fortunately, Microsoft has produced an easy way to do this documented right here (and we’ll be using this article in step 1, below) https://support.microsoft.com/en-us/kb/918992.

The process has three key steps:

1. 1. Use the script in this article to generate two new stored procedures in the primary Access Services Database Server Master Database https://support.microsoft.com/en-us/kb/918992
   2. Execute the Stored Procedure to generate a TSQL script that can be copied to the target secondary server, for example:
      -- Login: db_ _dbo
      CREATE LOGIN [db_63eb8501_29b0_401a_becd_9931ae72ea3d _dbo] WITH PASSWORD = *********** HASHED, SID = 0x0C3431F92F162D4EA913E07E1DAB3979 , DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF -- Login: db_63eb8501_29b0_401a_becd_9931ae72ea3d_custom
      CREATE LOGIN [db_63eb8501_29b0_401a_becd_9931ae72ea3d_custom] WITH PASSWORD = ***********   HASHED, SID = 0x8B68A3A203D6D14E88F13B504420BD7E, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
   3. Execute the TSQL on the target secondary database server to generate the logins

After completing these actions, the Secondary Disaster Recovery Farm will be able to render the Access Services Apps from the Primary farm after failover.

 

Summary

SharePoint Server 2013 has been tested in a disaster recovery scenario using SQL Server 2012 and SQL Server 2014  as the Access Application Database Server Platforms.

SharePoint Server 2016 has been tested in a disaster recovery scenario using SQL Server 2014 and SQL Server 2016 as the Access Application Database Server Platforms.

In all scenarios we were able to successfully recover the Access Applications on the Secondary SharePoint farm and perform all CRUD operations post failover, after following the guidance in this document.

The key elements are : Ensure both server farms are setup with matching Authentication Realms. Ensure Access Services database servers are referenced with the same ServerReferenceID. Transfer SQL Logins from Production to DR SQL Servers.