Sql-server – SQL Server AlwaysOn database stuck in Not Synchronizing / In Recovery mode after upgrading. Error: Cannot open database ‘…’ version 782

availability-groupssql serversql-server-2016upgrade

While testing an upgrade from SQL Server 2014 SP1 (12.0.4422.0) to SQL Server 2016 CTP 3.2 (13.0.900.73) I was following the recommended update process and ran into an issue where the database would not start on the old primary after failing over to the updated secondary. Our setup is a primary replica and a single secondary replica, and the steps I completed were:

  1. Remove automatic failover on synchronous-commit secondary replica
  2. Upgrade secondary server instances to new version
  3. Manually fail over to the secondary replica
  4. Verify databases are online on new primary replica
  5. Upgrade previous-primary replica to new version

The upgrade of the secondary and failover to make it the primary worked exactly as expected. But after upgrading the previously-primary replica I noticed that the databases on it were listed in SSMS as Not Synchronizing / In Recovery. Also trying to access them would generate an error message:

The database … is not accessible. (ObjectExplorer)

Checking thru the SQL Server Logs I saw

Cannot open database '…' version 782. Upgrade the database to the latest version.

Querying the master..sysdatabases table showed that it was indeed an older version and had not been updated during the upgrade:

SSMS sysdatabases version

Unfortunately the logs did not indicate why it wasn't updated, and the Availability Groups Dashboard only gave a generic warning indicating the Data synchronization state of some availability database is not healthy with no reason why.

I tried using TSQL to detach the databases or set them offline to "kick" it into updating, but since they are part of the SQL AG those commands don't work.

How can I upgrade the database to the latest version when it is part of a SQL AG?

Best Answer

After poking around in SSMS for a while I noticed that on the secondary replica there was a pause icon next to the Availability Databases. The primary had shown both were "green", but there was an option on the secondary to Resume Data Movement. I resumed the first database, and immediately the In Recovery status message was removed. A minute later it changed from Not Synchronizing to Synchronized, and everything worked as expected.

Here is a screenshot of the AG Databases after I fixed "Patch", but before fixing the test database:

Resume Data Movement on SQL AG

Note you can also use TSQL on the secondary to resume replication on multiple database at the same time:

ALTER DATABASE [Patch] SET HADR RESUME;
ALTER DATABASE [test] SET HADR RESUME;
GO
Related Question