Patching SharePoint DR Farms with Replicated Service-Applications with SQL Server AlwaysOn

So now we’ve got an active/passive SharePoint farm solution setup, the next question that is how to patch both farms with as little drama as possible. We need to patch one farm while the other’s in use so that our beloved users don’t notice anything’s even happened to SharePoint – one of the key reasons to have passive/active farms in fact.

How to patch with no downtime with SQL Server AlwaysOn has been covered already, but not with service-applications in sync too. This is a complex subject so be prepared!

It helps greatly if content & service-applications are in different SQL Server Availability Groups, but that’s not always how the back-end is setup; quite often all SharePoint databases are on the same one. This post outlines the process for both cases.

Some key terms here:

• Primary farm = “farm1”.
• Secondary farm = “farm2”.
• SQL Server AlwaysOn Availability Group = “AG”.
• PSConfig = this command-line tool preferably, or the SharePoint Configuration Wizard.

Note: you can do this either way round; swap farm1 for farm2 if you like, and the processes are the same, just in reverse.

The Synchronised Farm Upgrade Challenge

First though, a bit about the challenge we’re up against. It’s quite epic if I don’t say so myself…

In short, we have two distinct farms that share databases in active/passive mode. We need to upgrade both farms without users suffering any downtime.

With a setup whereby service application databases are synchronised between two separate farms, you have an issue where you’ll need to upgrade the service application databases at some point – how to do so while also keeping them operational 100% of the time?

PSConfig will obviously have issues if it has to upgrade a read-only service-application database, so we can’t just run this on the inactive farm without some tricks & workarounds to side-step these roadblocks.

Finally, given we want to try and keep users in read/write mode in all SharePoint sites, the challenge becomes harder still. How to let users continue working normally but also locking databases for upgrades, especially given they’re synchronised between two (temporarily) disjointed SharePoint versions?

Synced Farm Upgrade Strategies

There’s a few strategies to attack this “how to upgrade synced farms” challenge:

1. All SharePoint functionality kept in read-only mode until both farms are fully upgraded.
2. Users have read/write access while upgrade is going on.

The first strategy is the easiest, but rarely acceptable for the business needs – users need to update, and if they can’t it shouldn’t be for long. SharePoint patching can potentially be a long process!

The second strategy is more commonplace, but with a couple of different approaches to getting there.

Two approaches to patching SharePoint while users can still edit content:

1. Disconnect service-applications from 1^st upgraded farm so we can keep all SharePoint databases in read-only mode during PSConfig.
2. Upgrade the service-app databases on 1^st farm to be upgraded; overwrite the 2^nd farms’ databases once all farms are fully upgraded.

The first approach basically involves upgrading each farm without anything attached & mounting the service-apps again with the yet-to-be-upgraded databases:

• Users & SharePoint databases active on farm1
  • Remove all synced service-apps & content DBs from farm2.
  • Upgrade farm2
  • Add all synced service-apps & content DBs to farm2 again.
• Switch users & databases to farm2
  • Remove all synced service-apps & content DBs from farm1.
  • Upgrade farm1
  • Add all synced service-apps & content DBs to farm1 again.
• Now finish farm upgrade with SPDatabase.Upgrade on everything previously disconnecte.

I don't think this is the best way so I’m focusing on the last option because it doesn’t involve recreating service-applications, which can be quite a big task admin task potentially. Also, it is (in my opinion) safer as there's more chance to react & rollback should you have any upgrade problems, even if both methods are valid.

How to Upgrade Synchronised Farms Without Deleting Service-Applications

As if it wasn't complicated enough already, how then to upgrade via my preferred approach also depends on if you’ve put all your SharePoint databases in a single SQL Server Availability Group or not (probably you have)…

This is the most common setup, but the most complex to update. For this configuration, we’ll patch the farm2 service-application DBs while users are still on farm1 (in full read/write mode for content databases). That does means we have to make sure farm1 doesn’t allow updates to (only) service-applications while we upgrade the same service-apps on farm2. This is easily done by making the service-apps read-only on farm1 during farm2s’ upgrade.

Short version:

1. Patch farm2 while users & DBs stay on farm1.
   • Install binaries.
   • Dismount content DBs on farm2.
   • Disconnect service-app DBs from availability group.
     • Make databases on farm1 read-only.
     • Allow read/write for databases on farm2.
   • Finish upgrade on farm2 with PSConfig - upgrade service-applications too.
   • Mount content-databases to farm2.
   • Switch users and databases to farm2.
2. Patch farm1 while users on farm2
   • Install binaries.
   • Dismount content DBs
   • Drop read-only service-app databases on farm1 SQL instance.
   • Add service-app DBs from farm2 SQL instance back to availability group.
   • Finish upgrade on farm1 with PSConfig now farm1 has already-upgraded service-app DBs.
3. Upgrade content databases in PowerShell. All done!

We need to make service-app DBs read-only on the active farm (farm1) because farm2 service-app databases will ultimately overwrite them, so any data we change on farm1 in any service-app during upgrade would be lost. Users can still edit content while farm2 is being patched, just not taxonomies, user profiles, etc. Small note: this doesn’t include search btw; that’s a whole different game.

So far so good. But the complication is that farm1 has read/write access to the AG while we patch farm2 so our beloved users can still keep working in normal read/write mode for their content & sites. Of course though farm1 having full read/write access to the synced databases means therefore farm2 only has read-only access to everything in the AG, which will break the PSConfig stage of the upgrade if we don’t workaround this.

So in this example, our users keep using the normal primary site & we’re patching the secondary (farm2) in preparation.

1. Install patch binaries on farm2; all servers at once. You may need to reboot afterwards.

In the meantime, we have farm1 to handle traffic and farm2 will have read-only access to everything not configuration.

1. Farm2 now has latest binaries; now needs to run PSConfig to update databases and other stuff.

• • Dismount content-DBs from farm2 to avoid PSConfig errors.

Next-up, how to deal with service-app upgrades.

As mentioned above, we now have the issue that read-only service app DBs aren’t upgradable in PSConfig, which is a problem as we’re currently upgrading a farm with everything in read-only.

We’re going to deal with this by actually upgrading our service-apps now. That’s a bit awkward given we can’t do so easily as we’re in one AG for all databases, so time to remove the service-app databases from the AG sow farm2 can upgrade them.

• Remove service-apps from the availability group.
  • Run this for each service-app database on the primary SQL AlwaysOn instance (change the database name as necessary):

ALTER AVAILABILITY GROUP [SP2013]

REMOVE DATABASE [App_Management];

GO

• • The database in question will no long be replicating between SQL instances as it's no long in the AG, with the secondary SQL instance having the database in a “restoring” state.

• Set farm1 databases as read-only & enable read/write for farm2 databases. Run on the primary SQL node:

ALTER DATABASE [App_Management] SET READ_ONLY WITH ROLLBACK IMMEDIATE

GO

• • The database on the primary AlwaysOn SQL instance will now be read-only.
    • Later, we’re going to overwrite the primary instance with the upgraded secondary instance one it’s upgraded & we’re ready.
  • Now we need to restore normal operation to the database on the secondary SQL instance so we can read & write to it. Run this on the secondary SQL node:

RESTORE DATABASE [App_Management]

GO

Make sure you repeat this process for all service-app DBs.

Now farm2 should have full read/write access to the service-application DBs & farm1 with read-only access to the same databases. Farm1 still has full read/write access to content databases.

This is how your farm1 SQL instance should look like. Nobody can edit these service-apps, which is good for the time-being. Content databases are in read/write mode at least, so unlikely to be a big deal.

• Run PSConfig on farm2 to finish upgrade, upgrading the now R/W service-apps too.

• Attach content DB to upgraded secondary farm (content DB still un-upgraded).

Congratulations, farm2 is all upgraded (minus the content DB).

With one down, there’s one to go. This is a bit simpler.

• Failover users + content databases to upgraded farm2.
  • Users now have full read/write (upgraded) service-apps as well as read/write content-databases too.
• Detach content-database from farm1.
• Install binaries on farm1 now nobody’s using it.

Once everything’s been installed & rebooted (if necessary), we need to run PSConfig. But this time we don’t need to upgrade the service-app databases as we did that already, so…

• Drop all read-only service-app databases on farm1 SQL instance.

DROP DATABASE [App_Management]

GO

• On farm2 SQL instance, add service-application databases back into availability group.

When this completes, farm 1 will also have the upgraded service-application databases, and they’ll be back in sync again.

• Run PSConfig to finish upgrade.
  • Here’s the clever bit: it won’t need to upgrade the service-application databases as they’ve already been upgraded (copies from farm2).

Congratulations! Farm1 is now also upgraded. Finish off the process with:

• Upgrade content-database with Upgrade-SPContentDatabase.

If you have your service-app DBs & content DBs in separate Availability Groups, then well done you for forward-thinking - it'll now pay off. This is the better setup as it gives greater failover dexterity – one SharePoint farm can have service-applications in read-only but content in full-read/write at the same time. Each group fails-over independently; very handy for patching & upgrading SharePoint databases!

Short version:

1. Patch farm2 while users & DBs stay on farm1.
   • Install binaries.
   • Dismount content DBs on farm2.
   • Failover “service-apps” AG to farm2 SQL instance.
     • No hacks needed here; it’ll just work.
   • Suspend data movement for all service-app DBs.
     • You’ll break farm1 if it receives the patched databases without having the equivalent binaries installed, which right now it doesn’t have.
   • Finish upgrade on farm2 with PSConfig. Service-apps upgraded because they’re read/write
   • Mount content-databases to farm2.
   • Switch users and content-databases to farm2.
2. Patch farm1 while users on farm2
   • Install binaries.
   • Dismount content DBs
   • Resume data movement in service-app AG so farm1 gets patch changes for service-app DBs.
   • Finish upgrade on farm1 with PSConfig.
3. Upgrade content database in PowerShell.

Ah, so much cleaner!

Wrap-Up

That’s it for now. If anything’s not clear, let me know in the comments below – it’s a complex subject so it’s possible.

Cheers,

Sam Betts

• Anonymous
  March 23, 2016
  Sam,So the Upgrade content database in the two situation will happen on farm 2 which will make the dbs read only during the upgrade, is this is correct ?!
  • Anonymous
    March 23, 2016
    Hi Mahmoud,The content-database upgrade has to happen on whatever farm has read/write access to the DB, after everything else is done. Can be either farm; whichever is the active one. During the upgrade I believe it does make it R/O temporarily yes.// Sam
• Anonymous
  November 11, 2016
  Sam, instead of removing the service databases from the AVG, why don't you just stop the data movement (+ read only in the primary node) ?
• Anonymous
  December 02, 2016
  Hi Sam,First of all, very interesting article !So, i have a question. When you Mount content-databases to farm 2 (farm already patched), there is a content database upgrade (minimum schema update) ?The technet Mount-SPContentDatabase indicate : https://technet.microsoft.com/en-us/library/ff607581.aspx The default behavior of this cmdlet causes an upgrade of the schema of the database and initiates upgraded builds for all site collections within the specified content database if required. To prevent initiation of upgraded builds of site collections, use the NoB2BSiteUpgrade parameter.So, if i use parameter NoB2BSiteUpgrade it will not triggered build-to-build database upgrade but it will upgrade schema of the database.What happens during this update schema ? Read-Only to database ? Thks.Damien