12

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?

Improve this question

1 Answer 1

Reset to default
14

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
Improve this answer
3
  • 1
    Do you know what caused the Replication to pause? and can I find out since when the replication has been paused?
    – JohnG
    Jun 8, 2016 at 13:01
  • In my case, after failover, one of the secondary nodes become not synchronized, I restarted the node and then resumed the db data movement. everything is fine now.
    – mostafa8026
    Sep 6, 2021 at 13:09
  • In my case we ran out of Disk Space for our tlogs mount. Once the space was added we resume the data movement and it resolved the issue.
    – Ali
    Apr 4 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.