Configuring log shipping with one primary and two secondary instances
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3).I want configure three (or more) servers:
- SQLServer-1 – primary
- SQLServer-2 – secondary
- SQLServer-3 – secondary
I want the database in standby mode on the secondary servers – database available for querying, and users can access it, but in read-only mode.
I am able to configure this for two servers (one primary and one secondary), but when I try three servers, log shipping setup and secondary server (SQLInstance-3) gets stuck in restore mode, and the log shipping SQL Agent jobs on the second secondary (SQLInstance-3) fail repeatedly. I am confused here what to do?
The second secondary (SQLInstance-3) is showing No Last_Restore_file. I checked Transactional Log Shipping status and it has the same status for the restore file.
Does this mean log shipping is not configured properly between SQL Instance 1 & 3?
I set up log shipping originally following the process outlined in Configure Log Shipping (SQL Server). I didn't use the wizard, I restored the log backups on secondary, with SQL Agent jobs created for log restores. The copy and restore jobs failed.
I have checked Restoring Job failed with error 'Could not find a log backup file that could be applied' on Log Shipping in SQL Server (Secondary)? but it won't work for me. As suggested, I started from scratch and re-initialized the log shipping on secondary (2), jobs on secondary (1) failed with an error, and jobs on secondary (2) are running fine. Both secondaries are set up the same way, and I got successful notifications for log shipping, but jobs failed on one secondary.
How should I setup log shipping with one primary and multiple secondary servers?
sql-server log-shipping
edited Jun 5 '18 at 2:58
Paul White♦
54.3k14288461
asked Jun 19 '17 at 20:09
CR241CR241
814420
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3).I want configure three (or more) servers:
- SQLServer-1 – primary
- SQLServer-2 – secondary
- SQLServer-3 – secondary
I want the database in standby mode on the secondary servers – database available for querying, and users can access it, but in read-only mode.
I am able to configure this for two servers (one primary and one secondary), but when I try three servers, log shipping setup and secondary server (SQLInstance-3) gets stuck in restore mode, and the log shipping SQL Agent jobs on the second secondary (SQLInstance-3) fail repeatedly. I am confused here what to do?
The second secondary (SQLInstance-3) is showing No Last_Restore_file. I checked Transactional Log Shipping status and it has the same status for the restore file.
Does this mean log shipping is not configured properly between SQL Instance 1 & 3?
I set up log shipping originally following the process outlined in Configure Log Shipping (SQL Server). I didn't use the wizard, I restored the log backups on secondary, with SQL Agent jobs created for log restores. The copy and restore jobs failed.
I have checked Restoring Job failed with error 'Could not find a log backup file that could be applied' on Log Shipping in SQL Server (Secondary)? but it won't work for me. As suggested, I started from scratch and re-initialized the log shipping on secondary (2), jobs on secondary (1) failed with an error, and jobs on secondary (2) are running fine. Both secondaries are set up the same way, and I got successful notifications for log shipping, but jobs failed on one secondary.
How should I setup log shipping with one primary and multiple secondary servers?
sql-server log-shipping
edited Jun 5 '18 at 2:58
Paul White♦
54.3k14288461
asked Jun 19 '17 at 20:09
CR241CR241
814420
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
What's the exact failure on the Copy/Restore steps.
– Kris Gruttemeyer
Jun 26 '17 at 14:52
add a comment |
I have three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3).I want configure three (or more) servers:
- SQLServer-1 – primary
- SQLServer-2 – secondary
- SQLServer-3 – secondary
I want the database in standby mode on the secondary servers – database available for querying, and users can access it, but in read-only mode.
I am able to configure this for two servers (one primary and one secondary), but when I try three servers, log shipping setup and secondary server (SQLInstance-3) gets stuck in restore mode, and the log shipping SQL Agent jobs on the second secondary (SQLInstance-3) fail repeatedly. I am confused here what to do?
The second secondary (SQLInstance-3) is showing No Last_Restore_file. I checked Transactional Log Shipping status and it has the same status for the restore file.
Does this mean log shipping is not configured properly between SQL Instance 1 & 3?
I set up log shipping originally following the process outlined in Configure Log Shipping (SQL Server). I didn't use the wizard, I restored the log backups on secondary, with SQL Agent jobs created for log restores. The copy and restore jobs failed.
I have checked Restoring Job failed with error 'Could not find a log backup file that could be applied' on Log Shipping in SQL Server (Secondary)? but it won't work for me. As suggested, I started from scratch and re-initialized the log shipping on secondary (2), jobs on secondary (1) failed with an error, and jobs on secondary (2) are running fine. Both secondaries are set up the same way, and I got successful notifications for log shipping, but jobs failed on one secondary.
How should I setup log shipping with one primary and multiple secondary servers?
sql-server log-shipping
edited Jun 5 '18 at 2:58
Paul White♦
54.3k14288461
asked Jun 19 '17 at 20:09
CR241CR241
814420
I have three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3).I want configure three (or more) servers:
- SQLServer-1 – primary
- SQLServer-2 – secondary
- SQLServer-3 – secondary
I want the database in standby mode on the secondary servers – database available for querying, and users can access it, but in read-only mode.
I am able to configure this for two servers (one primary and one secondary), but when I try three servers, log shipping setup and secondary server (SQLInstance-3) gets stuck in restore mode, and the log shipping SQL Agent jobs on the second secondary (SQLInstance-3) fail repeatedly. I am confused here what to do?
The second secondary (SQLInstance-3) is showing No Last_Restore_file. I checked Transactional Log Shipping status and it has the same status for the restore file.
Does this mean log shipping is not configured properly between SQL Instance 1 & 3?
I set up log shipping originally following the process outlined in Configure Log Shipping (SQL Server). I didn't use the wizard, I restored the log backups on secondary, with SQL Agent jobs created for log restores. The copy and restore jobs failed.
I have checked Restoring Job failed with error 'Could not find a log backup file that could be applied' on Log Shipping in SQL Server (Secondary)? but it won't work for me. As suggested, I started from scratch and re-initialized the log shipping on secondary (2), jobs on secondary (1) failed with an error, and jobs on secondary (2) are running fine. Both secondaries are set up the same way, and I got successful notifications for log shipping, but jobs failed on one secondary.
How should I setup log shipping with one primary and multiple secondary servers?
sql-server log-shipping
sql-server log-shipping
edited Jun 5 '18 at 2:58
Paul White♦
54.3k14288461
asked Jun 19 '17 at 20:09
CR241CR241
814420
edited Jun 5 '18 at 2:58
Paul White♦
54.3k14288461
asked Jun 19 '17 at 20:09
CR241CR241
814420
edited Jun 5 '18 at 2:58
Paul White♦
54.3k14288461
edited Jun 5 '18 at 2:58
Paul White♦
54.3k14288461
edited Jun 5 '18 at 2:58
Paul White♦
54.3k14288461
54.3k14288461
asked Jun 19 '17 at 20:09
CR241CR241
814420
asked Jun 19 '17 at 20:09
CR241CR241
814420
asked Jun 19 '17 at 20:09
CR241CR241
814420
814420
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 8 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
What's the exact failure on the Copy/Restore steps.
– Kris Gruttemeyer
Jun 26 '17 at 14:52
add a comment |
What's the exact failure on the Copy/Restore steps.
– Kris Gruttemeyer
Jun 26 '17 at 14:52
What's the exact failure on the Copy/Restore steps.
– Kris Gruttemeyer
Jun 26 '17 at 14:52
What's the exact failure on the Copy/Restore steps.
– Kris Gruttemeyer
Jun 26 '17 at 14:52
add a comment |
1 Answer
1
active
oldest
votes
Below are the steps that you need to follow :
- Take full backup on primary (server 1 ).
- Restore full backup on server 2 and 3 using no_recovery option.
- Take log backup on server 1
- Restore log backup on server 2 and 3 using no_recovery option.
- Using GUI wizard, add secondary servers (server 2 and 3).
This will help you set up tertiary logshipping. I have done it and it just works fine.
Note: When you failover, you need to be careful as to what server you are failing over and have to manually restore the tail log backup on the other secondary server.
answered Jun 26 '17 at 14:50
KinKin
54.3k481192
Thanks for inputs. I already tried for set up Log shipping as same you mentioned in above, it got successfully config Log-shipping between 1 primary and 2 secondary server. But Copy and Restore jobs on one of Secondary servers failing frequently after setup (2nd Secondary server working fine and Jobs are running with out error). I double check setup for both secondary everything was looking good and I haven't figure it out what is the exact issue causing Job failures?
– CR241
Jul 11 '17 at 21:09
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%2f176696%2fconfiguring-log-shipping-with-one-primary-and-two-secondary-instances%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Below are the steps that you need to follow :
- Take full backup on primary (server 1 ).
- Restore full backup on server 2 and 3 using no_recovery option.
- Take log backup on server 1
- Restore log backup on server 2 and 3 using no_recovery option.
- Using GUI wizard, add secondary servers (server 2 and 3).
This will help you set up tertiary logshipping. I have done it and it just works fine.
Note: When you failover, you need to be careful as to what server you are failing over and have to manually restore the tail log backup on the other secondary server.
answered Jun 26 '17 at 14:50
KinKin
54.3k481192
Thanks for inputs. I already tried for set up Log shipping as same you mentioned in above, it got successfully config Log-shipping between 1 primary and 2 secondary server. But Copy and Restore jobs on one of Secondary servers failing frequently after setup (2nd Secondary server working fine and Jobs are running with out error). I double check setup for both secondary everything was looking good and I haven't figure it out what is the exact issue causing Job failures?
– CR241
Jul 11 '17 at 21:09
add a comment |
Below are the steps that you need to follow :
- Take full backup on primary (server 1 ).
- Restore full backup on server 2 and 3 using no_recovery option.
- Take log backup on server 1
- Restore log backup on server 2 and 3 using no_recovery option.
- Using GUI wizard, add secondary servers (server 2 and 3).
This will help you set up tertiary logshipping. I have done it and it just works fine.
Note: When you failover, you need to be careful as to what server you are failing over and have to manually restore the tail log backup on the other secondary server.
answered Jun 26 '17 at 14:50
KinKin
54.3k481192
Thanks for inputs. I already tried for set up Log shipping as same you mentioned in above, it got successfully config Log-shipping between 1 primary and 2 secondary server. But Copy and Restore jobs on one of Secondary servers failing frequently after setup (2nd Secondary server working fine and Jobs are running with out error). I double check setup for both secondary everything was looking good and I haven't figure it out what is the exact issue causing Job failures?
– CR241
Jul 11 '17 at 21:09
add a comment |
Below are the steps that you need to follow :
- Take full backup on primary (server 1 ).
- Restore full backup on server 2 and 3 using no_recovery option.
- Take log backup on server 1
- Restore log backup on server 2 and 3 using no_recovery option.
- Using GUI wizard, add secondary servers (server 2 and 3).
This will help you set up tertiary logshipping. I have done it and it just works fine.
Note: When you failover, you need to be careful as to what server you are failing over and have to manually restore the tail log backup on the other secondary server.
answered Jun 26 '17 at 14:50
KinKin
54.3k481192
Below are the steps that you need to follow :
- Take full backup on primary (server 1 ).
- Restore full backup on server 2 and 3 using no_recovery option.
- Take log backup on server 1
- Restore log backup on server 2 and 3 using no_recovery option.
- Using GUI wizard, add secondary servers (server 2 and 3).
This will help you set up tertiary logshipping. I have done it and it just works fine.
Note: When you failover, you need to be careful as to what server you are failing over and have to manually restore the tail log backup on the other secondary server.
answered Jun 26 '17 at 14:50
KinKin
54.3k481192
answered Jun 26 '17 at 14:50
KinKin
54.3k481192
answered Jun 26 '17 at 14:50
KinKin
54.3k481192
answered Jun 26 '17 at 14:50
KinKin
54.3k481192
54.3k481192
Thanks for inputs. I already tried for set up Log shipping as same you mentioned in above, it got successfully config Log-shipping between 1 primary and 2 secondary server. But Copy and Restore jobs on one of Secondary servers failing frequently after setup (2nd Secondary server working fine and Jobs are running with out error). I double check setup for both secondary everything was looking good and I haven't figure it out what is the exact issue causing Job failures?
– CR241
Jul 11 '17 at 21:09
add a comment |
Thanks for inputs. I already tried for set up Log shipping as same you mentioned in above, it got successfully config Log-shipping between 1 primary and 2 secondary server. But Copy and Restore jobs on one of Secondary servers failing frequently after setup (2nd Secondary server working fine and Jobs are running with out error). I double check setup for both secondary everything was looking good and I haven't figure it out what is the exact issue causing Job failures?
– CR241
Jul 11 '17 at 21:09
Thanks for inputs. I already tried for set up Log shipping as same you mentioned in above, it got successfully config Log-shipping between 1 primary and 2 secondary server. But Copy and Restore jobs on one of Secondary servers failing frequently after setup (2nd Secondary server working fine and Jobs are running with out error). I double check setup for both secondary everything was looking good and I haven't figure it out what is the exact issue causing Job failures?
– CR241
Jul 11 '17 at 21:09
Thanks for inputs. I already tried for set up Log shipping as same you mentioned in above, it got successfully config Log-shipping between 1 primary and 2 secondary server. But Copy and Restore jobs on one of Secondary servers failing frequently after setup (2nd Secondary server working fine and Jobs are running with out error). I double check setup for both secondary everything was looking good and I haven't figure it out what is the exact issue causing Job failures?
– CR241
Jul 11 '17 at 21:09
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%2f176696%2fconfiguring-log-shipping-with-one-primary-and-two-secondary-instances%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
What's the exact failure on the Copy/Restore steps.
– Kris Gruttemeyer
Jun 26 '17 at 14:52