Restore a Copy_Only backup taken in an Azure SQL Managed Instance onto another Azure SQL Managed Instance

-> I am trying to restore a Copy_Only database backup taken on Azure SQL Managed Instance JBMI-Pub to another Azure SQL Managed instance JBMI-Sub.

-> Please check this article on how to perform Copy_Only backup on an Azure SQL Managed Instance.

-> The copy_only backup for Managed Instance JBMI-Pub is placed on a storage account. Lets try a restore on Managed Instance JBMI-Sub as below,

-> Sign into the Azure portal after clicking Add,

TITLE: Microsoft SQL Server Management Studio

Restore of database ‘JBDB’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot find server asymmetric key with thumbprint ‘0xB48BED6BFEC0D4E9E8B1E9542A50F5225B6F6045’. (Microsoft.SqlServer.SmoExtended)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.41011.9+(SqlManagementObjects-master-APPLOCAL)&LinkId=20476

BUTTONS:
OK

-> Tried executing the restore command as a TSQL and got same error as below,

Msg 33111, Level 16, State 4, Line 2
Cannot find server asymmetric key with thumbprint ‘0xB48BED6BFEC0D4E9E8B1E9542A50F5225B6F6045’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

-> The restore failure is due to the fact that Transparent Data Encryption (TDE) is enabled on Managed Instances JBMI-Pub and JBMI-Sub with different keys.

-> Lets change the TDE option for Managed Instance JBMI-Sub where we are restoring the backup from Service-Managed key to Customer-managed key and use the same key as Managed Instance JBMI-Pub from where copy_only backup was performed.

-> Make sure we select the same key vault, key and version for Managed Instance JBMI-Sub which is same as JBMI-Pub.

-> Click Save and start the restore again,

-> Restore completed fine this time.

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.

Database backup on an Azure SQL Managed Instance

-> Azure SQL Managed instance has in-built database backups called Automated backups. It is not required for users to schedule regular backups manually. Refer this article for more details.

-> Performing a manual database backup sometime becomes mandatory in Managed instance.

-> Lets first create a storage account so that we can perform a manual backup of a database in Managed instance.

-> Create a storage account using above details. Once storage account is created, add a container into that storage account.

-> Backup database from SQL Server management studio using below method,

-> Signin to the azure portal.

-> Click “OK” after the credentials are created.

-> Script out the backup script by clicking on Script and selecting “Script Action to New Query Window”.

-> Notice “COPY_ONLY” included in the script. Lets try removing COPY_ONLY and try a backup. it fails with below error,

Msg 41904, Level 16, State 1, Line 3
BACKUP DATABASE failed. SQL Database Managed Instance supports only COPY_ONLY full database backups which are initiated by user.

-> Error message clearly advises us to take a backup with COPY_ONLY option. Lets try performing the backup with COPY_ONLY and it fails with below error this time,

Msg 41922, Level 16, State 1, Line 3
The backup operation for a database with service-managed transparent data encryption is not supported on SQL Database Managed Instance.
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

-> This error is related to Transparent Data Encryption (TDE) that is enabled by default in Managed instance using Service-Managed key. TDE with Service-Managed key does not allow copy-only backups. Lets configure TDE to use a Customer-Managed key and then perform a backup.

PLEASE NOTE – I am performing this task on a TEST subscription with no active application connecting to the database. If you are planning this on a Production database, you need to plan and test things well in advance and then perform it on a production instance, as this will have major impact while implementation.

-> I will use Customer-Managed key and place the key in an Azure key vault,

-> Create the Azure key vault using above details.

-> Click “Save” and you are ready to go.

-> Lets try performing a backup with COPY_ONLY option on the database in Managed instance. This time it works fine.

-> We are able to see the file in the storage account also.

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.

The process could not connect to Subscriber

> Transactional Replication is configured between 2 managed instances.

-> More details on how to configure transactional replication between 2 managed instance can be understood from this article.

-> Distribution agent fails with below error,

The process could not connect to Subscriber 'JBSWIKI-MI-DR.37B2CA9FEE0A.DATABASE.WINDOWS.NET'.

Disconnected from Azure Storage '\\jbmirepldata.file.core.windows.net\jbmirepldata' with OS result code: 2250.

Starting agent.
Error messages:

The process could not connect to Subscriber 'JBSWIKI-MI-DR.37B2CA9FEE0A.DATABASE.WINDOWS.NET'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20084)
Get help: http://help/MSSQL_REPL20084

Windows logins are not supported in this version of SQL Server. (Source: MSSQLServer, Error number: 40607)
Get help: http://help/40607
Date		8/13/2020 7:58:29 PM
Log		Job History (jbmi-publisher.37b2ca-JBREPL_Sub-TranProducts-JBSWIKI-MI-DR.37B2CA9-3)

Step ID		2
Server		JBMI-PUBLISHER.37B2CA9FEE0A.DATABASE.WINDOWS.NET
Job Name		jbmi-publisher.37b2ca-JBREPL_Sub-TranProducts-JBSWIKI-MI-DR.37B2CA9-3
Step Name		Run agent.
Duration		00:10:52
Sql Severity	0
Sql Message ID	0
Operator Emailed	
Operator Net sent	
Operator Paged	
Retries Attempted	0

Message
2020-08-13 14:39:13.505 Copyright (c) 2016 Microsoft Corporation
2020-08-13 14:39:13.505 Microsoft SQL Server Replication Agent: distrib
2020-08-13 14:39:13.505 
2020-08-13 14:39:13.505 The timestamps prepended to the output lines are expressed in terms of UTC time.
2020-08-13 14:39:13.505 User-specified agent parameter values:
			-Subscriber JBSWIKI-MI-DR.37B2CA9FEE0A.DATABASE.WINDOWS.NET
			-SubscriberDB JBREPL_Sub
			-Publisher jbmi-publisher.37b2ca9fee0a.database.windows.net
			-Distributor jbmi-publisher.37b2ca9fee0a.database.windows.net
			-DistributorSecurityMode 1
			-Publication TranProducts
			-PublisherDB JBREPL_Sub
			-Continuous
			-XJOBID 0x1F311561B8AC2A498D5487323D49323D
			-XJOBNAME jbmi-publisher.37b2ca-JBREPL_Sub-TranProducts-JBSWIKI-MI-DR.37B2CA9-3
			-XSTEPID 2
			-XSUBSYSTEM Distribution
			-XSERVER jbmi-publisher.37b2ca9fee0a.database.windows.net
			-XCMDLINE 1
			-XDB
			-XHOST np:\\.\pipe\DB4C.0-A9190D899828\sql\query
			-XCancelEventHandle 0000000000001B8C
			-XParentProcessHandle 0000000000001B24
2020-08-13 14:39:13.505 Startup Delay: 5810 (msecs)
2020-08-13 14:39:19.354 Connecting to Distributor 'jbmi-publisher.37b2ca9fee0a.database.windows.net'
2020-08-13 14:39:19.447 Obtained Azure Storage Connection String for jbmirepldata
2020-08-13 14:39:19.447 Connecting to Azure Files Storage '\\jbmirepldata.file.core.windows.net\jbmirepldata'
2020-08-13 14:39:21.872 Parameter values obtained from agent profile:
			-bcpbatchsize 0
			-commitbatchsize 100
			-commitbatchthreshold 1000
			-historyverboselevel 1
			-keepalivemessageinterval 300
			-logintimeout 15
			-maxbcpthreads 1
			-maxdeliveredtransactions 0
			-pollinginterval 5000
			-querytimeout 1800
			-skiperrors 
			-transactionsperhistory 100
2020-08-13 14:39:21.872 Connecting to Subscriber 'JBSWIKI-MI-DR.37B2CA9FEE0A.DATABASE.WINDOWS.NET'
2020-08-13 14:39:21.934 Disconnected from Azure Storage '\\jbmirepldata.file.core.windows.net\jbmirepldata' with OS result code: 0.
2020-08-13 14:39:21.950 Agent message code 20084. The process could not connect to Subscriber 'JBSWIKI-MI-DR.37B2CA9FEE0A.DATABASE.WINDOWS.NET'.
2020-08-13 14:39:21.981 Category:NULL
Source:  Microsoft OLE DB Driver for SQL Server
Number:  40607
Message: Windows logins are not supported in this version of SQL Server.

-> Azure SQL Managed Instance is connected using a SQL login. But the error states that windows logins are not supported. It seems like we have not keyed in the SQL logins properly while configuring the replication.

-> Open subscription properties by performing below actions,

-> Start the distribution agent and it will complete fine this time.

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.