Bugs in Azure Portal With PITR Restore of a CDC-Enabled Database Between SQL Managed Instances

sguidos 10

When using the Azure Portal to the a Point In Time Restore (PITR) of a database on SQL Managed Instance:

The restored database does not have the source database’s CDC Capture's Retention period in its [msdb].[dbo].[cdc_jobs] record (it contains the default Retention period of 3 days)
The CDC Capture and Cleanup Jobs are always created, even if they do not exist in the source database's Managed Instance
The CDC Cleanup Job is always enabled, even if it is disabled in the source database's Managed Instance
The CDC Cleanup Job runs immediately, and not at the time next scheduled at the source database's Managed Instance

The cumulative impact of the above issues is that when you restore a CDC-enabled database, all CDC data older than the default Retention of 3 days is immediately deleted from the restored database, and there is nothing you can do to stop this :-(

In addition, the Job Names for the destination SQL Server Agent Capture and Cleanup jobs are created using the [sys].[databases].physical_database_name] and not the [dbName]. Since the Azure UI for the PITR Restore requires that the destination database name does not exist on the destination SQL Managed Instance, those new jobs should be named using the destination database name, which is what happens when you enable CDC on a new database. Using the physical database name is confusing and requires the user to perform additional work to determine which SQL Server Agent Jobs belong to which restored database.

Mahesh Kurva 2,430 Reputation points Microsoft Vendor

2025-01-18T00:20:13.6166667+00:00

Hi @sguidos,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

As I understand, you're encountering several issues with the Point-in-Time Restore (PITR) process for CDC-enabled databases on SQL Managed Instance.

Here are some key points and potential steps to address them:

The restored database does not have the source database’s CDC Capture's Retention period in its [msdb].[dbo].[cdc_jobs] record (it contains the default Retention period of 3 days)

After the restore, manually update the retention period in the [msdb].[dbo].[cdc_jobs] table to match the source database.

The CDC Capture and Cleanup Jobs are always created, even if they do not exist in the source database's Managed Instance

Manually disable or delete these jobs if they are not needed.

The CDC Cleanup Job runs immediately, and not at the time next scheduled at the source database's Managed Instance

Immediately after the restore, disable the CDC Cleanup Job to prevent it from running and deleting data.

The job names are created using the physical database name, which can be confusing.

Rename the jobs to use the destination database name for clarity.

If the above steps do not resolve the issue, it might be best to create a support ticket. If you have a support plan, could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan, please let us know here.

I hope this information helps. Please do let us know if you have any further queries.
sguidos 10 Reputation points

2025-01-19T21:37:44.4133333+00:00

The "Bug" is that there is no way to do a complete and accurate PITR Restore on a CDC-Enabled database in a SQL Server Managed Instance.

You are correct that you can manually configure the destination database's Retention period and SQL Server Agent Jobs to mirror the source database after the PITR is completed, but you cannot prevent the new Capture Job from immediately deleting the restored CDC data in the restored / destination database.

As soon as the PITR Restore is complete, the newly-created but incorrectly configured Capture Job on the destination SQL Mananged Instance immediately starts to delete all CDC data older than the default Retention period of 3 days. You cannot fully restore a CDC-enabled database with all of its CDC data, because you cannot prevent the new Capture Job on the destination server from deleting CDC data older than the default Retention period.

Are you saying that I need to open a Support Ticket before Microsoft will consider this to be a valid Bug?
Mahesh Kurva 2,430 Reputation points Microsoft Vendor

2025-01-20T22:30:40.6733333+00:00

Hi @sguidos,

Thanks for the response.

Yes, if the issue persists and you believe it might be a bug, opening a support ticket would be the best course of action. Support ticket can investigate further and determine whether it’s a valid bug.
Mahesh Kurva 2,430 Reputation points Microsoft Vendor

2025-01-21T23:13:14.02+00:00

Hi @sguidos,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
sguidos 10 Reputation points

2025-01-22T13:44:44.83+00:00

I do not have a resolution. How can I do a complete PITR Restore of a database to a different SQL Managed Instance, including all CDC data?
Mahesh Kurva 2,430 Reputation points Microsoft Vendor

2025-01-22T21:53:37.2833333+00:00

Hi @sguidos,

We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

1 answer

Oury Ba-MSFT 20,106 Reputation points Microsoft Employee

2025-01-27T19:57:22.93+00:00
@sguidos Thank you for reaching out.

This is not a bug but a combination of design limitations and preventative measures from our side when restoring a CDC enabled database.

The CDC Capture and Cleanup Jobs are always created, even if they do not exist in the source database's Managed Instance. When restoring a CDC enabled database, we create and start capture and cleanup jobs. When a CDC database is restored, the restored database also has CDC enabled. If the capture job is not started, the transaction log will fill up over time and can result in log full and write unavailability on the database.

The restored database does not have the source database’s CDC Capture's Retention period in its [msdb].[dbo].[cdc_jobs] record (it contains the default Retention period of 3 days).

The CDC Cleanup Job is always enabled, even if it is disabled in the source database's Managed Instance.

**The CDC Cleanup Job runs immediately, and not at the time next scheduled at the source database's Managed Instance. **

These three issues are due to the fact that information (job enabled/disabled, retention, schedule), persisted in msdb and not within the context of the user database. This feedback is something we haven’t encountered previously and will require further internal discussion.

DBName / Physical DB Name in CDC job names – feedback taken

However, we can disable automatically running CDC jobs after a restore on customer’s instance. In that case, you must manually add and start the jobs in order to avoid the log full issue down the line. However, it will also allow you to configure CDC jobs before running them.

Hope that helps.

Regards,

Oury
Please sign in to rate this answer.
sguidos 10 Reputation points

2025-01-27T20:40:30.66+00:00

Oury, thank you for the information. Please understand that although the "Design limitations and preventative measures" may have been intentional, the impact is that you cannot fully perform a PITR Restore of a CDC-Enabled Database within an Azure SQL Managed Instance. I appreciate you taking this "feedback" into consideration.

First, note that the immediate problem is not caused by the Capture Job, but rather by the Cleanup Job, so that "transaction log size" is not an issue if you were to disable the Cleanup Job on the Destination instance.

You say that "we can disable automatically running CDC jobs after a restore on customer’s instance" but I have been unable to do that., can you provide additional guidance on how to do this? By the time the Azure UI reports that the PITR Restore is complete and I (quickly and manually) attempt to disable the new Cleanup Job in SSMS, that Cleanup Job has already started deleting the CDC data, since that Cleanup Job starts immediately once the PITR Restore operation is complete. Is there something I am missing here? How can I automatically(?) disable the (new) Cleanup Job after the PITR Restore but before it kicks off and starts deleting my restored CDC data?

Finally, I understand that the Retention period is persisted in the [msdb] database, but the PITR Restore process itself is creating that new [msdb] record and Retention value in the Destination instance. It seems to me that the easiest solution to this issue is to copy the Retention Period from the Source [msdb] record to the Destination; then the new Cleanup Job would not delete any CDC data (unless it would have been purged by the Source database anyway) and I would have a complete and accurately Restored database.

Thanks for your help!

Oury Ba-MSFT 20,106 Reputation points Microsoft Employee

2025-01-31T23:38:41.2633333+00:00

@sguidos Sorry for the delay in response to my question.

We do not currently have support to disable creating and running CDC jobs after the restore from customer's side.

Regarding the CDC job configuration – As you noticed, during the Restore process we create new CDC job configuration with default values. There is no support currently to source this information from the source instance’s CDC job configuration. We appreciate the feedback, and we will investigate options, but this is a long-term improvement.

We can offer a workaround. We can disable creating, configuring, and starting CDC jobs after the restore. This way, the restored database will have CDC enabled, but no configuration will be done regarding CDC jobs. This will allow customers to setup these jobs how they prefer and only then run them, as if they were setting up CDC for the first time.

Disabling this feature will require back-end work, so you will need to provide us with a list of instances and subscription IDs where you intend to restore CDC enabled databases.

Hope that helps.

Regards,

Oury
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Bugs in Azure Portal With PITR Restore of a CDC-Enabled Database Between SQL Managed Instances

1 answer

Your answer