what is the penalty for NOT setting Disconnect users in the database when restoring backups?











up vote
2
down vote

favorite












setting up Log shipping between 2 sql server 2016 servers
I want my databases read-only standby on the secondary server.



there is a tick box saying Disconnect users in the database when restoring backups.



what if I don't tick this box?
The restore of the log would not come trough because it could not get exclusive lock on the database?



But then, what if someone is logged in for hours? Hours without restoring the logs?



enter image description here






sql-server-2016 restore transaction-log log-shipping session







share|improve this question


















  • 4




    I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
    – John Eisbrener
    3 hours ago






  • 3




    And then the restore will time out depending on how the restore is performed.
    – Randolph West
    3 hours ago















up vote
2
down vote

favorite












setting up Log shipping between 2 sql server 2016 servers
I want my databases read-only standby on the secondary server.



there is a tick box saying Disconnect users in the database when restoring backups.



what if I don't tick this box?
The restore of the log would not come trough because it could not get exclusive lock on the database?



But then, what if someone is logged in for hours? Hours without restoring the logs?



enter image description here






sql-server-2016 restore transaction-log log-shipping session







share|improve this question


















  • 4




    I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
    – John Eisbrener
    3 hours ago






  • 3




    And then the restore will time out depending on how the restore is performed.
    – Randolph West
    3 hours ago













up vote
2
down vote

favorite









up vote
2
down vote

favorite











setting up Log shipping between 2 sql server 2016 servers
I want my databases read-only standby on the secondary server.



there is a tick box saying Disconnect users in the database when restoring backups.



what if I don't tick this box?
The restore of the log would not come trough because it could not get exclusive lock on the database?



But then, what if someone is logged in for hours? Hours without restoring the logs?



enter image description here






sql-server-2016 restore transaction-log log-shipping session







share|improve this question













setting up Log shipping between 2 sql server 2016 servers
I want my databases read-only standby on the secondary server.



there is a tick box saying Disconnect users in the database when restoring backups.



what if I don't tick this box?
The restore of the log would not come trough because it could not get exclusive lock on the database?



But then, what if someone is logged in for hours? Hours without restoring the logs?



enter image description here






sql-server-2016 restore transaction-log log-shipping session




sql-server-2016 restore transaction-log log-shipping session






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked 3 hours ago









marcello miorelli

5,5541960128




5,5541960128








  • 4




    I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
    – John Eisbrener
    3 hours ago






  • 3




    And then the restore will time out depending on how the restore is performed.
    – Randolph West
    3 hours ago














  • 4




    I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
    – John Eisbrener
    3 hours ago






  • 3




    And then the restore will time out depending on how the restore is performed.
    – Randolph West
    3 hours ago








4




4




I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
3 hours ago




I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
3 hours ago




3




3




And then the restore will time out depending on how the restore is performed.
– Randolph West
3 hours ago




And then the restore will time out depending on how the restore is performed.
– Randolph West
3 hours ago










2 Answers
2






active

oldest

votes

















up vote
5
down vote



accepted










From this page in the MS documentation:




There are two options for configuration when you place the secondary
database in standby mode:



You can choose to have database users disconnected when transaction
log backups are being restored. If you choose this option, users will
be disconnected from the database each time the log shipping restore
job attempts to restore a transaction log to the secondary database.
Disconnection will happen on the schedule you set for the restore job.



You can choose not to disconnect users. In this case, the restore job
cannot restore transaction log backups to the secondary database if
there are users connected to that database. Transaction log backups
will accumulate until there are no user connections to the database.




So yes, it could be hours, days, weeks, or years without a restore if users are still connected to the secondary.






share|improve this answer




























    up vote
    2
    down vote














    what if I don't tick this box? The restore of the log would not come trough because it could not get exclusive lock on the database?




    Another reason that I can think of is -



    The .tuf file (transaction undo file) that gets generated when you configure logshipping in stand-by mode will keep increasing as no restore is happening due to users being connected to the stand-by server and eventually you will run out of disk space.



    Also, having configured logshipping in standby mode, you should be aware that the logs get restored slower when logshipping runs in standby mode --> Think about your RTO !






    share|improve this answer





















    • thanks Kin but what is RTO?
      – marcello miorelli
      2 hours ago






    • 1




      @marcellomiorelli - RTO stands for Recovery Time Objective - In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how quickly must the data be made accessible again.
      – Scott Hodgin
      2 hours ago











    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224918%2fwhat-is-the-penalty-for-not-setting-disconnect-users-in-the-database-when-restor%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    5
    down vote



    accepted










    From this page in the MS documentation:




    There are two options for configuration when you place the secondary
    database in standby mode:



    You can choose to have database users disconnected when transaction
    log backups are being restored. If you choose this option, users will
    be disconnected from the database each time the log shipping restore
    job attempts to restore a transaction log to the secondary database.
    Disconnection will happen on the schedule you set for the restore job.



    You can choose not to disconnect users. In this case, the restore job
    cannot restore transaction log backups to the secondary database if
    there are users connected to that database. Transaction log backups
    will accumulate until there are no user connections to the database.




    So yes, it could be hours, days, weeks, or years without a restore if users are still connected to the secondary.






    share|improve this answer

























      up vote
      5
      down vote



      accepted










      From this page in the MS documentation:




      There are two options for configuration when you place the secondary
      database in standby mode:



      You can choose to have database users disconnected when transaction
      log backups are being restored. If you choose this option, users will
      be disconnected from the database each time the log shipping restore
      job attempts to restore a transaction log to the secondary database.
      Disconnection will happen on the schedule you set for the restore job.



      You can choose not to disconnect users. In this case, the restore job
      cannot restore transaction log backups to the secondary database if
      there are users connected to that database. Transaction log backups
      will accumulate until there are no user connections to the database.




      So yes, it could be hours, days, weeks, or years without a restore if users are still connected to the secondary.






      share|improve this answer























        up vote
        5
        down vote



        accepted







        up vote
        5
        down vote



        accepted






        From this page in the MS documentation:




        There are two options for configuration when you place the secondary
        database in standby mode:



        You can choose to have database users disconnected when transaction
        log backups are being restored. If you choose this option, users will
        be disconnected from the database each time the log shipping restore
        job attempts to restore a transaction log to the secondary database.
        Disconnection will happen on the schedule you set for the restore job.



        You can choose not to disconnect users. In this case, the restore job
        cannot restore transaction log backups to the secondary database if
        there are users connected to that database. Transaction log backups
        will accumulate until there are no user connections to the database.




        So yes, it could be hours, days, weeks, or years without a restore if users are still connected to the secondary.






        share|improve this answer












        From this page in the MS documentation:




        There are two options for configuration when you place the secondary
        database in standby mode:



        You can choose to have database users disconnected when transaction
        log backups are being restored. If you choose this option, users will
        be disconnected from the database each time the log shipping restore
        job attempts to restore a transaction log to the secondary database.
        Disconnection will happen on the schedule you set for the restore job.



        You can choose not to disconnect users. In this case, the restore job
        cannot restore transaction log backups to the secondary database if
        there are users connected to that database. Transaction log backups
        will accumulate until there are no user connections to the database.




        So yes, it could be hours, days, weeks, or years without a restore if users are still connected to the secondary.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 3 hours ago









        SQLRaptor

        2,0831118




        2,0831118
























            up vote
            2
            down vote














            what if I don't tick this box? The restore of the log would not come trough because it could not get exclusive lock on the database?




            Another reason that I can think of is -



            The .tuf file (transaction undo file) that gets generated when you configure logshipping in stand-by mode will keep increasing as no restore is happening due to users being connected to the stand-by server and eventually you will run out of disk space.



            Also, having configured logshipping in standby mode, you should be aware that the logs get restored slower when logshipping runs in standby mode --> Think about your RTO !






            share|improve this answer





















            • thanks Kin but what is RTO?
              – marcello miorelli
              2 hours ago






            • 1




              @marcellomiorelli - RTO stands for Recovery Time Objective - In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how quickly must the data be made accessible again.
              – Scott Hodgin
              2 hours ago















            up vote
            2
            down vote














            what if I don't tick this box? The restore of the log would not come trough because it could not get exclusive lock on the database?




            Another reason that I can think of is -



            The .tuf file (transaction undo file) that gets generated when you configure logshipping in stand-by mode will keep increasing as no restore is happening due to users being connected to the stand-by server and eventually you will run out of disk space.



            Also, having configured logshipping in standby mode, you should be aware that the logs get restored slower when logshipping runs in standby mode --> Think about your RTO !






            share|improve this answer





















            • thanks Kin but what is RTO?
              – marcello miorelli
              2 hours ago






            • 1




              @marcellomiorelli - RTO stands for Recovery Time Objective - In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how quickly must the data be made accessible again.
              – Scott Hodgin
              2 hours ago













            up vote
            2
            down vote










            up vote
            2
            down vote










            what if I don't tick this box? The restore of the log would not come trough because it could not get exclusive lock on the database?




            Another reason that I can think of is -



            The .tuf file (transaction undo file) that gets generated when you configure logshipping in stand-by mode will keep increasing as no restore is happening due to users being connected to the stand-by server and eventually you will run out of disk space.



            Also, having configured logshipping in standby mode, you should be aware that the logs get restored slower when logshipping runs in standby mode --> Think about your RTO !






            share|improve this answer













            what if I don't tick this box? The restore of the log would not come trough because it could not get exclusive lock on the database?




            Another reason that I can think of is -



            The .tuf file (transaction undo file) that gets generated when you configure logshipping in stand-by mode will keep increasing as no restore is happening due to users being connected to the stand-by server and eventually you will run out of disk space.



            Also, having configured logshipping in standby mode, you should be aware that the logs get restored slower when logshipping runs in standby mode --> Think about your RTO !







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered 3 hours ago









            Kin

            52.6k478187




            52.6k478187












            • thanks Kin but what is RTO?
              – marcello miorelli
              2 hours ago






            • 1




              @marcellomiorelli - RTO stands for Recovery Time Objective - In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how quickly must the data be made accessible again.
              – Scott Hodgin
              2 hours ago


















            • thanks Kin but what is RTO?
              – marcello miorelli
              2 hours ago






            • 1




              @marcellomiorelli - RTO stands for Recovery Time Objective - In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how quickly must the data be made accessible again.
              – Scott Hodgin
              2 hours ago
















            thanks Kin but what is RTO?
            – marcello miorelli
            2 hours ago




            thanks Kin but what is RTO?
            – marcello miorelli
            2 hours ago




            1




            1




            @marcellomiorelli - RTO stands for Recovery Time Objective - In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how quickly must the data be made accessible again.
            – Scott Hodgin
            2 hours ago




            @marcellomiorelli - RTO stands for Recovery Time Objective - In simple terms, you can think of RTO as a measure of how much downtime is acceptable, or how quickly must the data be made accessible again.
            – Scott Hodgin
            2 hours ago


















            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224918%2fwhat-is-the-penalty-for-not-setting-disconnect-users-in-the-database-when-restor%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            What visual should I use to simply compare current year value vs last year in Power BI desktop

            Image
            0 I have column SubToBind that shows current year ratio and column PY SubToBind which shows previous year ratio. How can I simply create a card that would be displaying them both in a nice way? Something like that: I cannot achieve that with regular card, I also tried Card with States by OKViz but also unable to see both values. In KPI visual - I dont like the word "Goal", it will confuse end users. Are there any cards or other visuals that would do the job? Thanks powerbi dax powerbi-embedded share | improve this question edited Jun 11 '18 at 20:11 Oleg ...
            Read more

            How to ignore python UserWarning in pytest?

            Image
            up vote 1 down vote favorite I am using openpyxl for parsing .xlsm files, and pytest for testing. When I open a file, I get the: OpenPyxl -> UserWarning: Data Validation extension is not supported and will be removed That is not really a problem bcs. program works and I can't change the .xlsm file to fix that. But... When I run pytest with something like: def test_wrong_file_format(): assert check_excel(open_excel('file.xlsm')) == True I will get the warning i mentioned altought check_excel(open_excel('file.xlsm')) returns True and the test should suceed... Is there a nice way to tell the pytest that "It's not a bug it's a feature" and tests should pass even when they get this warning? Is there other way than using something like: with pytest.warns(UserWarning): ...
            Read more

            Alexandru Averescu

            Image
            Alexandre Averescu Alexandru Averescu Naissance 3 avril 1859 Babele, Ismail  (ro) près de Izmaïl (principauté de Moldavie) Décès 2 octobre 1938 (à 79 ans) [ 1 ] Bucarest Origine   Roumanie Arme cavalerie Grade Maréchal Années de service 1901-1917 Commandement chef d'état-major général 2 e  armée 3 e  armée Groupe d'armée sud armée du Danube Conflits Guerre russo-turque de 1877-1878 Deuxième guerre balkanique Première Guerre mondiale Faits d'armes Bataille de Turtucaia/Tutrakan Bataille de Mărăști Distinctions Autres fonctions 3 fois président du Conseil des ministres ministre des Affaires étrangères ministre sans portefeuille conseiller de la Couronne modifier   Alexandre Averescu (en roumain : Alexandru Averescu .mw-parser-output .prononciation>a{background:url("//upload.wikimedia.org/wikipedia/commons/thumb/8/8a/Loudspeaker.svg/11px-Loudspeaker.svg.png")center ...
            Read more