Disaster Recovery best practices for SharePoint Server and Access Services

SharePoint Server 2016
 

Applies to: Access Services, SharePoint Server 2013, SharePoint Server 2016

Topic Last Modified: 2017-04-11

Summary:Learn how to apply a disaster recovery strategy for Access Services in SharePoint Server 2013 and SharePoint Server 2016.

The article explains how to successfully implement a disaster recovery (DR) strategy for  Access Services service applications for SharePoint Server 2013 and SharePoint Server 2016 .

Thanks to Neil Hodgkinson, Microsoft Senior Program Manager for testing this disaster recovery strategy and providing the content for this article.

SharePoint 2010 introduced the concept of Access Services as an integrated service application in Introduction to Access Services. The data was held in SharePoint lists and could be accessed with a browser or the Microsoft Access 2010 Client. In SharePoint 2013 the Access Services architecture changed, and introduced the concept of Contained databases, moving the data out of SharePoint lists and instead into a SQL Server 2012 Application database. This architecture remains in place for the SharePoint Server 2016 release. 

There are multiple 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 various approaches in Choose a disaster recovery strategy.

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.

ImportantImportant:
Before you can use any of the Windows PowerShell cmdlets detailed in the steps below, verify that you meet all of the requirements in Permissions.

The goal of this step is to create a smoother disaster recovery experience by removing potential points of failure. By matching Authentication Realms, and Database Server ReferenceIDs so they are the same in the disaster recovery farm as in the primary farm, you will be prepared for recovery. Likewise, it is essential to know which databases must be managed in order to recover successfully.

Let's drill into the details, below.

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 in mind it 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.

To get the authentication realm of the primary farm use thisWindows PowerShell command:


 Get-SPAuthenticationRealm

Example: A sample authentication realm is a GUID, so the returned value might look like this: 4a2cc8f8-51ab-4367-8a76-ab629c882a68.


Using that GUID as our example, set it on the secondary farm using these Windows PowerShell commands:


Set-SPAuthenticationRealm -Realm 4a2cc8f8-51ab-4367-8a76-ab629c882a68
Restart-Service sptimerv4
Restart-Service spadminv4
ImportantImportant:
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 perform an IISReset (SharePoint sites will be unavailable until the successful end of an IISReset).

Access Services in SharePoint Server 2013 and SharePoint Server 2016 use a SQL Server to host the individual databases that support Access-based Apps. Internally, these database servers aren’t referenced by name, but by a ReferenceID.

ImportantImportant:
It’s critical to the success of your disaster recovery strategy that the database servers in the secondary data center 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.


$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


$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.

Rather than having their own service application databases, Access Services in SharePoint Server 2013 and SharePoint Server 2016 have tight dependencies on multiple databases in a SharePoint farm.

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

 

Database Description

App Management database

Contains Access app registrations and app principals.

Subscription Settings database

Manages the unique identities provided to Access apps to create the URL for the Access application.

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.

SharePoint Content database

These databases contain the site collections into which Access apps have been deployed.

Access Services Application databases

The databases containing the actual data you need to preserve for the Access Services application to function.

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.

After failing-over to the secondary datacenter, you need to use the five different databases listed in Step 1 to regenerate the Access Services app infrastructure on the disaster recovery farm.

NoteNote:
This article only deals with the five database types listed in the table above. To successfully recover a full SharePoint Server farm after a data center failover, additional steps are needed and the reader is directed to review the steps in Plan for high availability and disaster recovery.

For the test environment we discuss in this article, that means the following databases are recovered from the Primary SQL Server SQL01 to the Secondary SQL Server SQL02 in the DR site.

  • App Management database

  • Subscription Settings database

  • Secure Store database

  • SharePoint Content database

  • Access Services App databases

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

Use the following 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.

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

Before you can use any of the Windows PowerShell cmdlets, verify that you meet all of the requirements in Permissions.


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

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.

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

  • Set the app domain URLs

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

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.


Make sure you setup the App Urls in Central Administration on the DR site.

  1. Open Central Administration, select Apps.

  2. Select Configure App URLs.

Recovering the App Management Database does not preserve the App Domain even though it does preserve the App Prefix.

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

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) How to transfer logins and passwords between instances of SQL Server.

The process has three key steps:

  1. Use the script in How to transfer logins and passwords between instances of SQL Server to generate two new stored procedures in the primary Access Services Database Server Master Database.

  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.

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

SharePoint Server 2016 has been tested in a disaster recovery scenario using 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.

Show: