what is the penalty for NOT setting Disconnect users in the database when restoring backups?
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?
sql-server-2016 restore transaction-log log-shipping session
asked Dec 13 '18 at 18:07
marcello miorellimarcello miorelli
5,6431961129
add a comment |
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?
sql-server-2016 restore transaction-log log-shipping session
asked Dec 13 '18 at 18:07
marcello miorellimarcello miorelli
5,6431961129
4
I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
Dec 13 '18 at 18:16
3
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 '18 at 18:17
add a comment |
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?
sql-server-2016 restore transaction-log log-shipping session
asked Dec 13 '18 at 18:07
marcello miorellimarcello miorelli
5,6431961129
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?
sql-server-2016 restore transaction-log log-shipping session
sql-server-2016 restore transaction-log log-shipping session
asked Dec 13 '18 at 18:07
marcello miorellimarcello miorelli
5,6431961129
asked Dec 13 '18 at 18:07
marcello miorellimarcello miorelli
5,6431961129
asked Dec 13 '18 at 18:07
marcello miorellimarcello miorelli
5,6431961129
asked Dec 13 '18 at 18:07
marcello miorellimarcello miorelli
5,6431961129
asked Dec 13 '18 at 18:07
marcello miorellimarcello miorelli
5,6431961129
5,6431961129
4
I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
Dec 13 '18 at 18:16
3
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 '18 at 18:17
add a comment |
4
I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
Dec 13 '18 at 18:16
3
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 '18 at 18:17
4
4
I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
Dec 13 '18 at 18:16
I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
Dec 13 '18 at 18:16
3
3
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 '18 at 18:17
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 '18 at 18:17
add a comment |
2 Answers
2
active
oldest
votes
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.
answered Dec 13 '18 at 18:29
SQLRaptorSQLRaptor
2,2611119
add a comment |
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 !
answered Dec 13 '18 at 18:57
KinKin
52.9k478187
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 '18 at 19:07
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
Dec 13 '18 at 19:17
add a comment |
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',
autoActivateHeartbeat: false,
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
answered Dec 13 '18 at 18:29
SQLRaptorSQLRaptor
2,2611119
add a comment |
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.
answered Dec 13 '18 at 18:29
SQLRaptorSQLRaptor
2,2611119
add a comment |
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.
answered Dec 13 '18 at 18:29
SQLRaptorSQLRaptor
2,2611119
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.
answered Dec 13 '18 at 18:29
SQLRaptorSQLRaptor
2,2611119
answered Dec 13 '18 at 18:29
SQLRaptorSQLRaptor
2,2611119
answered Dec 13 '18 at 18:29
SQLRaptorSQLRaptor
2,2611119
answered Dec 13 '18 at 18:29
SQLRaptorSQLRaptor
2,2611119
2,2611119
add a comment |
add a comment |
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 !
answered Dec 13 '18 at 18:57
KinKin
52.9k478187
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 '18 at 19:07
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
Dec 13 '18 at 19:17
add a comment |
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 !
answered Dec 13 '18 at 18:57
KinKin
52.9k478187
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 '18 at 19:07
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
Dec 13 '18 at 19:17
add a comment |
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 !
answered Dec 13 '18 at 18:57
KinKin
52.9k478187
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 !
answered Dec 13 '18 at 18:57
KinKin
52.9k478187
answered Dec 13 '18 at 18:57
KinKin
52.9k478187
answered Dec 13 '18 at 18:57
KinKin
52.9k478187
answered Dec 13 '18 at 18:57
KinKin
52.9k478187
52.9k478187
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 '18 at 19:07
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
Dec 13 '18 at 19:17
add a comment |
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 '18 at 19:07
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
Dec 13 '18 at 19:17
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 '18 at 19:07
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 '18 at 19:07
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
Dec 13 '18 at 19:17
@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
Dec 13 '18 at 19:17
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
4
I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
Dec 13 '18 at 18:16
3
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 '18 at 18:17