Running SharePoint Service Applications in Read Only Mode for Disaster Recovery Farms

  • [アーティクル]

As mentioned before, there’s good reason to have a disaster recovery (DR) farm setup for SharePoint if you need near 100% uptime. As also mentioned, you may need to synchronise some service-applications between farms too for data consistency; taxonomies, user-profiles etc often go hand-in-hand so having unique service-applications on each farm just isn’t an option.

So what do we do? Simple in principal; synchronise service-applications between farms as well as content, with the passive SharePoint farm using the service-app databases in read-only mode. This is how we run SharePoint Online and it’s fully supported by the product-team with one key caveat: only one farm can ever have full read/write access to all mirrored SharePoint databases at any time, ever. Break this golden rule and, well, it’ll be ugly for everyone involved in the aftermath. Don’t say you weren’t warned. Don’t!

So first things first; you’ve got your SharePoint content & now service-application databases synchronised between two SQL Servers in AlwaysOn for example, and now we need to add the service-applications on the DR/passive farm. This is where it gets a bit complicated; SharePoint will happily add a content database that’s read-only but not really for service-apps.

Restoring Service Applications

In my examples, I have two SQL Server nodes – one for each farm:

  • “sql-n3\sp15” <-- this is primary, read/write node normally.
  • “sql-n2\sp15” <-- this is the read-only secondary node.

User Profile Application

If you add a user profile application with this command to add from the read-only secondary SQL node:

New-SPProfileServiceApplication -Name "User Profiles" -ApplicationPool (Get-SPServiceApplicationPool -Identity "UPA") -ProfileDBName "UPA_Profiles" -ProfileDBServer "sql-2\sp15" -SocialDBName "UPA_Social" -SocialDBServer "sql-n2\sp15" -ProfileSyncDBName "UPA_Sync" -ProfileSyncDBServer "sql-n2\sp15"

The cmdlet fails with this error:

New-SPProfileServiceApplication : Failed to update database "UPA_Profiles" because the database is read-only.

If you bother to look at the call-stack in the ULS file, you may notice this revealing entry:

System.Data.SqlClient.SqlException (0x80131904): Failed to update database "UPA_Profiles" because the database is read-only.

…(snip)…

at Microsoft.Office.Server.Administration.UserProfileApplication.ClearProfileDatabaseLogs()

at Microsoft.Office.Server.Administration.UserProfileApplication.Provision()

What SharePoint is doing in this case is resetting the profile event-logs after $now because supposedly it’s a new application so anything happened after $now shouldn’t have happened.

Whatever; long-story-short: we need read/write access if we’re going to mount this service-application.

So we need to failover SQL to add the instance:

clip_image002

Important: bear in mind of course that this will leave our primary site in read-only mode, effectively making it the passive farm – you’ll want to do this in a maintenance window.

Now the command to create the service-application should work:

clip_image004

Hurrah; we now have synchronised user profiles on the secondary farm. Aren’t we awesome.

Managed Metadata

Same thing goes for managed-metadata. Trying to add a new managed metadata service-app while the databases are in read-only mode give this error:

System.Data.SqlClient.SqlException: Failed to update database "Managed_Metadata" because the database is read-only

…(snip)…

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at Microsoft.Office.Server.Data.SqlSession.ExecuteNonQuery(SqlCommand command)

at Microsoft.Office.Server.Data.SqlDatabaseManager.AddRoleMember(String role, String user)

at Microsoft.SharePoint.Taxonomy.MetadataWebServiceDatabase.EnsureApplicationAccess(…)

at Microsoft.SharePoint.Taxonomy.MetadataWebServiceApplication.OnProcessIdentityChanged(…)

at Microsoft.SharePoint.Administration.SPIisWebServiceApplication.Provision()

This time SharePoint threw its’ toys out the pram for a different reason though. Adding it with normal read/write access is the only way you’ll get this mounted.

Other Service Applications

Requiring read/write access when mounting service-applications seems to be true of pretty much all service-apps; SharePoint assumes it has full access to each service-app database when you create said app. So in short, you’ll need to give your DR/passive/secondary farm full access to the databases when you add them.

Again, this of course means too that the primary farm will need to be in read-only mode while you do this, but it’s a necessary stage to get a secondary farm running with the same service-application databases.

But I Don’t Want to Set My Primary Farm to Read-Only!

If the temporary switch of production SQL Server to read-only is unpalatable, then there is a 2nd option assuming you don’t have the service-application database already created on your secondary SQL Server:

  1. Create service apps in DR using the same DB names a production.
    • This is just to get the service-applications created in the DR farm.
  2. Drop SQL Databases from SQL on secondary/DR site.
    • SharePoint will wonder what’s happening to it at this point and cry quite a lot.
  3. Replicate Production databases to secondary SQL, as read-only replicas.
    • SharePoint will be relieved it’s databases are back. Only this time, they’re exact copies of Production databases and it’ll all just work. Magic!

This works because at no point are there databases IDs stored in the configuration databases; only names. It’s a nice way of avoiding a failover to the secondary site (thanks Neil Hodgkinson for the tip).

Secure Store Service – Post Restore Tasks

Most applications just need mounting and that’s it, with one key exception – secure store service.

You’ll need to generate a new master-key on the secondary farm with the same key as you used to generate the key on the primary farm. Without this, your secure store database is basically useless by design, so generate a new key from the service-application using the same pass phrase as you did on the 1st farm:

clip_image005

This will re-encrypt the database but with the same encryption as the primary farm used, so both farms will still be able to read the contents. Without this step being executed exactly the same as on the 1st farm, the secure store database won’t be readable on the secondary farm so anything that relies on it will break.

If you don’t enter the passphrase exactly as was (and SharePoint won’t complain if you enter a different one), I don’t want to imagine the outcome but it’ll be along the lines of pain & doom for your secure-store applications without a doubt.

Testing Service App Functionality

So in my test setup, I want to make sure both farms are using the service-apps properly. We assume that all the services have been started on the secondary farm of course; without that nothing will work.

A nice test is an external SQL list I have which I have setup. This uses Business Data Connectivity + Secure Store to connect to the SQL Server in combination. If both my primary & secondary farms can read back the data without configuring anything on the secondary farm especially, then that’s a pretty good sign everything is working & synced up. After all, the external content-type & secure store application was configured on the primary farm – the secondary should just read the same configuration.

Here’s the target table in SQL Server:

clip_image006

On the primary site (which is still in read-only mode as we’re technically still configuring the secondary farm service-apps) we see:

clip_image008

Marvellous. Even in read-only mode, SharePoint external lists works just fine.

On the secondary site (still in read/write mode):

clip_image010

That’s even better; no extra configuration needed for my second SharePoint farm; it all just works.

 

Congratulations awesome SPAdmin; you just levelled-up!

 

Cheers,

// Sam Betts