what is the penalty for NOT setting Disconnect users in the database when restoring backups?
up vote
1
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?
sql-server-2016 restore transaction-log log-shipping session
asked Dec 13 at 18:07
marcello miorelli
5,5771960128
add a comment |
up vote
1
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?
sql-server-2016 restore transaction-log log-shipping session
asked Dec 13 at 18:07
marcello miorelli
5,5771960128
4
I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
Dec 13 at 18:16
3
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 at 18:17
add a comment |
up vote
1
down vote
favorite
up vote
1
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?
sql-server-2016 restore transaction-log log-shipping session
asked Dec 13 at 18:07
marcello miorelli
5,5771960128
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 at 18:07
marcello miorelli
5,5771960128
asked Dec 13 at 18:07
marcello miorelli
5,5771960128
asked Dec 13 at 18:07
marcello miorelli
5,5771960128
asked Dec 13 at 18:07
marcello miorelli
5,5771960128
asked Dec 13 at 18:07
marcello miorelli
5,5771960128
5,5771960128
4
I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
Dec 13 at 18:16
3
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 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 at 18:16
3
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 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 at 18:16
I believe it'll wait for those connections to naturally finish/disconnect before the restore operation begins.
– John Eisbrener
Dec 13 at 18:16
3
3
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 at 18:17
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 at 18:17
add a comment |
2 Answers
2
active
oldest
votes
up vote
6
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.
answered Dec 13 at 18:29
SQLRaptor
2,1331119
add a comment |
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 !
answered Dec 13 at 18:57
Kin
52.7k478187
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 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 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',
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
up vote
6
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.
answered Dec 13 at 18:29
SQLRaptor
2,1331119
add a comment |
up vote
6
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.
answered Dec 13 at 18:29
SQLRaptor
2,1331119
add a comment |
up vote
6
down vote
accepted
up vote
6
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.
answered Dec 13 at 18:29
SQLRaptor
2,1331119
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 at 18:29
SQLRaptor
2,1331119
answered Dec 13 at 18:29
SQLRaptor
2,1331119
answered Dec 13 at 18:29
SQLRaptor
2,1331119
answered Dec 13 at 18:29
SQLRaptor
2,1331119
2,1331119
add a comment |
add a comment |
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 !
answered Dec 13 at 18:57
Kin
52.7k478187
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 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 at 19:17
add a comment |
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 !
answered Dec 13 at 18:57
Kin
52.7k478187
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 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 at 19:17
add a comment |
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 !
answered Dec 13 at 18:57
Kin
52.7k478187
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 at 18:57
Kin
52.7k478187
answered Dec 13 at 18:57
Kin
52.7k478187
answered Dec 13 at 18:57
Kin
52.7k478187
answered Dec 13 at 18:57
Kin
52.7k478187
52.7k478187
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 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 at 19:17
add a comment |
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 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 at 19:17
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 at 19:07
thanks Kin but what is RTO?
– marcello miorelli
Dec 13 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 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 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.
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.
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 at 18:16
3
And then the restore will time out depending on how the restore is performed.
– Randolph West
Dec 13 at 18:17