maintain DatabaseID, Database Name and SqlEndPoint on a restore

[bob_duffy]

When doing restore we always get a new database ID, Physical Name and SqlEndPoint, even if the database logical name is the same (eg restore over the top).

This causes a massive increase in RTO as afer the restore all end points broke and configuration management had to be updated and re-tested.

When doing a restore we need to have the ability to keep database exactly as it was, rather than always creating a clone.

In a recent DR event it was 5 minutes to restore, but the 2 days before we stopped getting issues on end points and config change impact.

[StrahinjaRodic]

Hey, Bob,

Thanks for the feedback!

Does this mean you'd like to revert the already existing database to some older point-in-time in the history and lose the data after that point, or you have something else on your mind? Please add a bit more details, or feel free to reach out to me via email.

Thanks,

Strahinja Rodic - PM on Backup & Restore area on SQL database in Microsoft Fabric

[BobDuffyIRL]

When we restore a Fabric SQLDB it changes the database names which break the ETL and all customers connecting to it. 
We then have to ge everyone globally to change the connection string, which means a restore has customer downtime of days.

Can you fix this so that a restore keeps the original database name and doesnt break the connection string ?

Ideally a restore shoudl restore "as was" unless we choose to restore to a different Artefact Name or workspace.

[akatesmith]

As per Strahinja's comment below - clarification would be helpful.

[MDC70]

Please enable the "Overwrite the existing database WITH REPLACE" functionality, as we know it from SQL Server. When a restore is performed into a different database, all references in the ETL might have to be adjusted - which is something nobody wants to deal with. Therefore, having the WITH REPLACE option would be very useful

[StrahinjaRodic]

Hi, @MDC70, Thanks for the feedback!

We'd like to understand better this feedback and answering these questions would help us a lot:

1. When you say "All references in the ETL" - do you need to restore over the existing database with the same name to preserve downstream ETL references or for something else?
2. If restoring to a new database and renaming it was automated and seamless, would that meet your needs? If not, why do you specifically require true in-place overwrite?
3. Would you be okay with permanently losing all data changes made after the restore point?
4. What are your expectations around downtime and automation for this feature? Would you plan a maintenance window and manually restart ETL jobs, or are you aiming for a near-zero-downtime restore that seamlessly continues ETL without reconfiguring connections?

Thank you,

Strahinja Rodic - PM on Backup & Restore area on SQL database in Microsoft Fabric

[MDC70]

Hi Strahinja I'm happy to provide feedback on your questions and hope this makes things clearer for you.

1. Correct! I was specifically thinking of the references in a data pipeline. After the restore, if a new database appears in the workspace with a new name, we end up with two databases -the original and the restored one. If the ETL is supposed to run on the restored database, then every activity that references the database must be updated accordingly.
   
   
2. I need to answer this a bit more descriptively, from the perspective of an experienced DBA. When performing a restore, I want to decide for myself whether the restore should create a new database or overwrite the existing one - essentially just like it's possible with any standard database.
   
   
3. Yes, that would be okay. We assume that we have a daily processing routine - for example, an ETL job that runs for 4 hours. After that, the data is available for querying. The source data can always be reloaded, so we wouldn’t experience any data loss. Here too, the system shouldn’t make any assumptions regarding how the process is handled.
   
   
4. A short downtime would of course be desirable. We would naturally perform this during a maintenance window and monitor the ETL process afterwards.

Kind Regards Marco