Cannot open database ‘JBDB’ version 869. Upgrade the database to the latest version. (Microsoft SQL Server, Error: 946)

Environment

-> Database Server JBSAG1 and JBSAG2 hosts SQL Server 2017. The requirement was to perform an In-Place upgrade to SQL Server 2019 on JBSAG1 and JBSAG2.

-> Database Server JBSAG1 is secondary replica and JBSAG2 is primary replica.

-> In-place upgrade from SQL Server 2017 to SQL Server 2019 was started on JBSAG1 as it is the secondary replica. It completed fine without any issues.

-> After the upgrade, connected to JBSAG1 using SSMS and performed a health check. The database JBDB on SQL Server instance JBSAG1 showed as Synchronized / In Recovery as show in below screenshot,

-> Checked SQL Server error log and found below message,

2020-09-10 15:05:27.020 spid53 Error: 946, Severity: 14, State: 1.
2020-09-10 15:05:27.020 spid53 Cannot open database ‘JBDB’ version 869. Upgrade the database to the latest version.

Cannot open database ‘JBDB’ version 869. Upgrade the database to the latest version. (Microsoft SQL Server, Error: 946)

-> The message is very clear. After the In-place upgrade, the database should be upgraded to latest version which is SQLS erver 2019, since the database is in Always on secondary, setup process was not able to upgrade it during the upgrade process.

-> Checking the Always On availability group dashboard from primary replica JBSAG2,

-> There are no errors and Always On availability group is in healthy state. So it is safe to failover Availability group from JBSAG2 to JBSAG1. Once after the failover, the database will get upgraded.

-> After the failover database JBDB in JBSAG1 is online and synchronized,

-> Checking the Always On dashboard,

-> JBSAG2 which is the secondary replica now and shows database JBDB as “Not Synchronizing / In Recovery”. On JBSAG2, check the database JBDB status under Availability databases and you will see that the database is paused.

-> We are seeing this in paused state because after failover the current Primary JBSAG1 is in SQL Server 2019 and the secondary JBSAG2 is in SQL Server 2017. Now it is time to perform an In-Place upgrade to SQL Server 2019 on JBSAG2 and the database JBDB in “Not Synchronizing / In Recovery” state in JBSAG2 is expected.

<TESTING ONLY – DONOT PERFORM ON LIVE SERVERS>

-> I will try resuming the database JBDB on JBSAG2 without
performing an In-Place upgrade from SQL Server 2017 to SQL
Server 2019.

-> Checking SQL Server error log. Below message can be found,


2020-09-10 15:20:21.490 spid71 ALTER DB param option: RESUME
2020-09-10 15:20:21.500 spid71 Always On Availability Groups data
movement for database ‘JBDB’ has been resumed. This is an
informational message only. No user action is required.
2020-09-10 15:20:21.500 spid77s Always On Availability Groups
connection with primary database established for secondary
database ‘JBDB’ on the availability replica ‘JBSAG1’ with Replica ID:
{850de16e-2c2b-43b1-bf52-6d8c5e2046ff}. This is an informational
message only. No user action is required.


2020-09-10 15:20:21.500 spid77s Error: 948, Severity: 20, State:
102.
2020-09-10 15:20:21.500 spid77s The database ‘JBDB’ cannot be
opened because it is version 904. This server supports version
869 and earlier. A downgrade path is not supported.


2020-09-10 15:20:21.500 spid77s Always On Availability Groups
data movement for database ‘JBDB’ has been suspended for
the following reason: “system” (Source ID 7; Source string:
‘SUSPEND_FROM_REVALIDATION’). To resume data
movement on the database, you will need to resume the
database manually. For information about how to resume an
availability database, see SQL Server Books Online.

-> The message clearly tells that the database should be upgraded
before resuming the data movement. Hence In-Place upgrade of
JBSAG2 should be performed.

<TESTING ONLY – DONOT PERFORM ON LIVE SERVERS>

-> Performed In-Place upgrade from SQL Server 2017 to SQL Server 2019 on database server JBSAG2.

-> I checked the Always On dashboard. The database JBDB was still “Not Synchronizing / In Recovery” and was suspended.

-> I resumed the data movement for database JBDB on database server JBSAG2 and it worked fine this time. Below is the Always On dashobaord,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Agent message code 21021. The subscription does not exist.

Environment

-> I worked on a disaster recovery drill. As part of the DR, I failed over all database servers from Primary Datacentre to Secondary Datacentre.

-> We had an environment which is same as above environment. Remote distributor, Publisher and subscriber servers were configured in Always ON availability group.

-> After the failover, distribution agent failed with below error,

Error messages:
Agent 'JBPUB-PRIMARY-JBREPL-TranProducts-JBREPLSUB-1' is retrying after an error. 8 retries attempted. See agent job history in the Jobs folder for more details.

-> I then checked the distribution agent job in SQL Server Agent and found below error,

Date		9/4/2020 11:41:07 AM
Log		Job History (JBPUB-PRIMARY-JBREPL-TranProducts-JBREPLSUB-1)

Step ID		2
Server		JBDIST-DR
Job Name	JBPUB-PRIMARY-JBREPL-TranProducts-JBREPLSUB-1
Step Name		Run agent.
Duration		00:11:51
Sql Severity	0
Sql Message ID	0
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
2020-09-04 11:52:54.399 Copyright (c) 2016 Microsoft Corporation
2020-09-04 11:52:54.399 Microsoft SQL Server Replication Agent: distrib
2020-09-04 11:52:54.399 
2020-09-04 11:52:54.399 The timestamps prepended to the output lines are expressed in terms of UTC time.
2020-09-04 11:52:54.399 User-specified agent parameter values:
			-Subscriber JBREPLSUB
			-SubscriberDB JBREPL_SUB
			-Publisher JBPUB-PRIMARY
			-Distributor JBDistributor
			-DistributorSecurityMode 1
			-Publication TranProducts
			-PublisherDB JBREPL
			-Continuous
			-XJOBID 0x3B3FD20E052A694491BD0660C526FA21
			-XJOBNAME JBPUB-PRIMARY-JBREPL-TranProducts-JBREPLSUB-1
			-XSTEPID 2
			-XSUBSYSTEM Distribution
			-XSERVER JBDIST-DR
			-XCMDLINE 0
			-XCancelEventHandle 0000000000001BA8
			-XParentProcessHandle 0000000000000358
2020-09-04 11:52:54.399 Startup Delay: 4172 (msecs)
2020-09-04 11:52:58.581 Connecting to Distributor 'JBDistributor'
2020-09-04 11:52:58.612 Parameter values obtained from agent profile:
			-bcpbatchsize 2147473647
			-commitbatchsize 100
			-commitbatchthreshold 1000
			-historyverboselevel 1
			-keepalivemessageinterval 300
			-logintimeout 15
			-maxbcpthreads 1
			-maxdeliveredtransactions 0
			-pollinginterval 5000
			-querytimeout 1800
			-skiperrors 
			-transactionsperhistory 100
2020-09-04 11:52:58.612 Connecting to Subscriber 'JBREPLSUB'
2020-09-04 11:52:58.659 Initializing
2020-09-04 11:52:58.706 Agent message code 21021. The subscription does not exist.

-> From the replication monitor, Right Click Distribution agent and click on “Agent Profiles”,

-> It seems like there is a custom distribution agent profile created and used in Database Server in Primary Datacentre (JBDIST-PRIMARY) that is not available in Database Server at Secondary Datacentre (JBDIST-DR).

-> Now that we failed over from Primary Datacentre to Secondary Datacentre, this missing “Agent Profile” is causing issue.

-> Execute below query on distribution database in Database Server JBDIST-PRIMARY which is currently a secondary replica after the failover and check what agent profile was used by the distribution agent previously,

select a.id, a.name, a.publisher_database_id,a.publisher_db, a.subscriber_db,
b.profile_id , b.profile_name from MSdistribution_agents a 
INNER JOIN msdb..MSagent_profiles b on a.profile_id = b.profile_id

-> In my case the the “Agent profile” missing is JBS-Load.

-> Now I will create this missing distribution agent profile in replication monitor. Right click Distribution agent and click on “Agent profile” and Click “New”.

-> I could find what were the parameters used as part of Agent profile “JBS-Load” and I created it as below,

-> Stop and start the distribution agent and it should work fine.

-> You can select “Default agent profile” if you are not aware of the custom profile created. But please understand it may not have the setting same as custom profile, which means there are possibilities of issues.

-> It is always a best practice to compare the agent profiles in replication monitor as part of DR planning if you dont want to run into issues like this.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Please create a master key in the database or open the master key in the session before performing this operation

Environment

-> Always ON Availability group configuration below,

-> JBDB Database is already created and added to Always ON Availability group.

-> Object Table1 contains column Customer_ID and Customer_Password. Column Customer_Password will be encrypted to secure the password.

-> Column level encryption will be enabled on object Table1 in Database JBDB using below scripts,

USE JBDB;
GO
                 
CREATE TABLE Table1
(Col1        INT IDENTITY (1,1) PRIMARY KEY, 
Customer_ID     NVARCHAR(30) NOT NULL, 
Customer_Password NVARCHAR(100) NOT NULL
)
GO

Insert into Table1 values ('ABC123','Password')
Insert into Table1 values ('ABC456','Password1')
Insert into Table1 values ('ABC789','Password2')
SELECT * FROM TABLE1

-> Create a database master key

USE JBDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Passw0rd';

-> Execute below query to check the master key,

-> Create a self-signed certificate for SQL Server

USE JBDB;
GO
CREATE CERTIFICATE Table1_Certificate WITH SUBJECT = 'Table1 Column Level Encryption';
GO

-> Configure a symmetric key for encryption

CREATE SYMMETRIC KEY Table1_Sym_Key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Table1_Certificate;

-> Encrypt Column Customer_PWD

ALTER TABLE Table1
 ADD Customer_PWD varbinary(MAX)

OPEN SYMMETRIC KEY Table1_Sym_Key
 DECRYPTION BY CERTIFICATE Table1_Certificate;

UPDATE Table1
 SET Customer_PWD = EncryptByKey (Key_GUID('Table1_Sym_Key'), Customer_Password)
 FROM Table1;

CLOSE SYMMETRIC KEY Table1_Sym_Key;

select * from  Table1

-> Remove column Customer Password,

ALTER TABLE TABLE1 DROP COLUMN Customer_Password;
GO
SELECT * FROM TABLE1

-> Below query can be used to Decrypt the encrypted column,

OPEN SYMMETRIC KEY Table1_Sym_Key DECRYPTION BY CERTIFICATE Table1_Certificate;
SELECT Customer_ID,Customer_PWD AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(Customer_PWD)) AS 'Decrypted Customer Password'
 FROM Table1;

CLOSE SYMMETRIC KEY Table1_Sym_Key;
GO

-> This requires quiet a planning when performing on a production environment that has many rows. The demo above just has 3 rows, so everything completed fine.

-> Everything works fine till now without any issues. A failover happens wherein the Availability group fails over from JBSAG1 to JBSAG2.

-> JBSAG2 is PRIMARY now. Executing below query to fetch the decrypted data,

OPEN SYMMETRIC KEY Table1_Sym_Key DECRYPTION BY CERTIFICATE Table1_Certificate;
SELECT Customer_ID,Customer_PWD AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(Customer_PWD)) AS 'Decrypted Customer Password'
 FROM Table1;

CLOSE SYMMETRIC KEY Table1_Sym_Key;
GO

-> Below error is encountered,

Msg 15581, Level 16, State 7, Line 1
Please create a master key in the database or open the master key in the session before performing this operation.


Msg 15315, Level 16, State 1, Line 4
The key 'Table1_Sym_Key' is not open. Please open the key before using it.

-> To solve this problem execute below query on JBSAG2,

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Passw0rd' -- Password should be same as what was used with query "CREATE MASTER KEY ENCRYPTION BY PASSWORD"
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

-> Execute below query to fetch the decrypted data,

OPEN SYMMETRIC KEY Table1_Sym_Key DECRYPTION BY CERTIFICATE Table1_Certificate;
SELECT Customer_ID,Customer_PWD AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(Customer_PWD)) AS 'Decrypted Customer Password'
 FROM Table1;
CLOSE SYMMETRIC KEY Table1_Sym_Key;
GO

-> This issue will reoccur when Availability group fails over from JBSAG2 to JBSAG1 and vice versa. Executing below query on current primary replica solves the issue,

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Passw0rd' -- Password should be same as what was used with query "CREATE MASTER KEY ENCRYPTION BY PASSWORD"
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

-> But the problem is, this is a manual step and it will be very difficult to do it manually during every failover. Hence below action should be performed.

-> Execute below query to backup the Service Master Key on primary replica. In my case it is JBSAG1,

BACKUP SERVICE MASTER KEY
 TO FILE = 'C:\temp\JBSAG1_Service_master_key'
ENCRYPTION BY PASSWORD = 'Passw0rd';

-> Copy the backup of Service Master Key from JBSAG1 to JBSAG2 in location C:\temp.

-> Manually failover availability group from JBSAG1 to JBSAG2 and execute below query,

RESTORE SERVICE MASTER KEY
FROM FILE = 'C:\temp\JBSAG1_Service_master_key'
DECRYPTION BY PASSWORD = 'Passw0rd';
GO

-> Once above tasks are completed. Any further failover and failback will not have any issues querying the encrypted data.

-> Check this article if you are encountering this issue while restoring a database backup on a different SQL Server instance.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.