bob_duffy's avatar
bob_duffy
New Member
1 year ago
Status:
Abandoned

maintain DatabaseID, Database Name and SqlEndPoint on a restore

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.

databases
Databases | SQL Database

10 Comments

  • BobDuffyIRL's avatar
    BobDuffyIRL
    Regular Visitor
    1 year ago

    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.

     

     

  • MDC70's avatar
    MDC70
    Icon for Advocate I rankAdvocate I
    1 year ago

    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's avatar
      StrahinjaRodic
      Icon for Microsoft Employee rankMicrosoft Employee
      1 year ago

      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's avatar
      MDC70
      Icon for Advocate I rankAdvocate I
      1 year ago

      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

  • StrahinjaRodic's avatar
    StrahinjaRodic
    Icon for Microsoft Employee rankMicrosoft Employee
    1 year ago

    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

  • akatesmith's avatar
    akatesmith
    Icon for Microsoft Employee rankMicrosoft Employee
    1 year ago
    Status changed:
    New
    to
    Need Clarification

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

Recent ideas