What exactly happens during Database Mirroring? What about during a fail over?
So I am trying to configure a two server setup, both containing SQL with independent storage. We're using windows server 2012, and I have a question about mirroring. If the server's data bases are in sync then the primary server fails and the mirrored server takes over. How do I re-synchronize the servers after I put the primary(now the one that will act as the mirror) back up? Is that handled automatically?
Edit: I guess I mean after a fail over, how does the old primary get back in sync with the mirror that has been receiving transactions. I think the endpoints are crucial as Kin said.
replication mirroring
asked Jun 30 '16 at 15:26
Mario MerendinoMario Merendino
11
bumped to the homepage by Community♦ 10 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 |
So I am trying to configure a two server setup, both containing SQL with independent storage. We're using windows server 2012, and I have a question about mirroring. If the server's data bases are in sync then the primary server fails and the mirrored server takes over. How do I re-synchronize the servers after I put the primary(now the one that will act as the mirror) back up? Is that handled automatically?
Edit: I guess I mean after a fail over, how does the old primary get back in sync with the mirror that has been receiving transactions. I think the endpoints are crucial as Kin said.
replication mirroring
asked Jun 30 '16 at 15:26
Mario MerendinoMario Merendino
11
bumped to the homepage by Community♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Mario, are you using automatic failover?
– QWE
Jun 30 '16 at 15:28
@QWE nope, we're using manual failover (maybe forced manual failover)
– Mario Merendino
Jun 30 '16 at 15:30
If you run this on your mirror server (which became primary after failover) then your servers will be back to their original roles. 'ALTER DATABASE [yourDatabase] SET PARTNER FAILOVER GO' Please have a look: link
– QWE
Jun 30 '16 at 15:36
Also this one: sqlservercentral.com/blogs/robert_davis/2009/02/22/…
– QWE
Jun 30 '16 at 15:46
add a comment |
So I am trying to configure a two server setup, both containing SQL with independent storage. We're using windows server 2012, and I have a question about mirroring. If the server's data bases are in sync then the primary server fails and the mirrored server takes over. How do I re-synchronize the servers after I put the primary(now the one that will act as the mirror) back up? Is that handled automatically?
Edit: I guess I mean after a fail over, how does the old primary get back in sync with the mirror that has been receiving transactions. I think the endpoints are crucial as Kin said.
replication mirroring
asked Jun 30 '16 at 15:26
Mario MerendinoMario Merendino
11
So I am trying to configure a two server setup, both containing SQL with independent storage. We're using windows server 2012, and I have a question about mirroring. If the server's data bases are in sync then the primary server fails and the mirrored server takes over. How do I re-synchronize the servers after I put the primary(now the one that will act as the mirror) back up? Is that handled automatically?
Edit: I guess I mean after a fail over, how does the old primary get back in sync with the mirror that has been receiving transactions. I think the endpoints are crucial as Kin said.
replication mirroring
replication mirroring
asked Jun 30 '16 at 15:26
Mario MerendinoMario Merendino
11
asked Jun 30 '16 at 15:26
Mario MerendinoMario Merendino
11
edited Jun 30 '16 at 17:23
Mario Merendino
asked Jun 30 '16 at 15:26
Mario MerendinoMario Merendino
11
asked Jun 30 '16 at 15:26
Mario MerendinoMario Merendino
11
asked Jun 30 '16 at 15:26
Mario MerendinoMario Merendino
11
11
bumped to the homepage by Community♦ 10 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♦ 10 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Mario, are you using automatic failover?
– QWE
Jun 30 '16 at 15:28
@QWE nope, we're using manual failover (maybe forced manual failover)
– Mario Merendino
Jun 30 '16 at 15:30
If you run this on your mirror server (which became primary after failover) then your servers will be back to their original roles. 'ALTER DATABASE [yourDatabase] SET PARTNER FAILOVER GO' Please have a look: link
– QWE
Jun 30 '16 at 15:36
Also this one: sqlservercentral.com/blogs/robert_davis/2009/02/22/…
– QWE
Jun 30 '16 at 15:46
add a comment |
Mario, are you using automatic failover?
– QWE
Jun 30 '16 at 15:28
@QWE nope, we're using manual failover (maybe forced manual failover)
– Mario Merendino
Jun 30 '16 at 15:30
If you run this on your mirror server (which became primary after failover) then your servers will be back to their original roles. 'ALTER DATABASE [yourDatabase] SET PARTNER FAILOVER GO' Please have a look: link
– QWE
Jun 30 '16 at 15:36
Also this one: sqlservercentral.com/blogs/robert_davis/2009/02/22/…
– QWE
Jun 30 '16 at 15:46
Mario, are you using automatic failover?
– QWE
Jun 30 '16 at 15:28
Mario, are you using automatic failover?
– QWE
Jun 30 '16 at 15:28
@QWE nope, we're using manual failover (maybe forced manual failover)
– Mario Merendino
Jun 30 '16 at 15:30
@QWE nope, we're using manual failover (maybe forced manual failover)
– Mario Merendino
Jun 30 '16 at 15:30
If you run this on your mirror server (which became primary after failover) then your servers will be back to their original roles. 'ALTER DATABASE [yourDatabase] SET PARTNER FAILOVER GO' Please have a look: link
– QWE
Jun 30 '16 at 15:36
If you run this on your mirror server (which became primary after failover) then your servers will be back to their original roles. 'ALTER DATABASE [yourDatabase] SET PARTNER FAILOVER GO' Please have a look: link
– QWE
Jun 30 '16 at 15:36
Also this one: sqlservercentral.com/blogs/robert_davis/2009/02/22/…
– QWE
Jun 30 '16 at 15:46
Also this one: sqlservercentral.com/blogs/robert_davis/2009/02/22/…
– QWE
Jun 30 '16 at 15:46
add a comment |
1 Answer
1
active
oldest
votes
When you do a failover, a role reversal happens i.e. your primary becomes secondary and your secondary becomes primary.
ALTER DATABASE [yourDatabase]
SET PARTNER FAILOVER
GO
Doing so, only the role reversal happens (this is what happens when you initiate a failover using above command) and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN. Refer to my answer : If I fail over one database, do the others that share the same mirror endpoint fail over as well? for more details.
You can have your application use Failover Partner=your_mirror_serve_name, so that your application is redirected to replica database when a failover happens.
Refer to : connectionstring in Database Mirror setup
edited Apr 13 '17 at 12:42
Community♦
1
answered Jun 30 '16 at 16:31
KinKin
53.5k480188
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%2f142691%2fwhat-exactly-happens-during-database-mirroring-what-about-during-a-fail-over%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
When you do a failover, a role reversal happens i.e. your primary becomes secondary and your secondary becomes primary.
ALTER DATABASE [yourDatabase]
SET PARTNER FAILOVER
GO
Doing so, only the role reversal happens (this is what happens when you initiate a failover using above command) and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN. Refer to my answer : If I fail over one database, do the others that share the same mirror endpoint fail over as well? for more details.
You can have your application use Failover Partner=your_mirror_serve_name, so that your application is redirected to replica database when a failover happens.
Refer to : connectionstring in Database Mirror setup
edited Apr 13 '17 at 12:42
Community♦
1
answered Jun 30 '16 at 16:31
KinKin
53.5k480188
add a comment |
When you do a failover, a role reversal happens i.e. your primary becomes secondary and your secondary becomes primary.
ALTER DATABASE [yourDatabase]
SET PARTNER FAILOVER
GO
Doing so, only the role reversal happens (this is what happens when you initiate a failover using above command) and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN. Refer to my answer : If I fail over one database, do the others that share the same mirror endpoint fail over as well? for more details.
You can have your application use Failover Partner=your_mirror_serve_name, so that your application is redirected to replica database when a failover happens.
Refer to : connectionstring in Database Mirror setup
edited Apr 13 '17 at 12:42
Community♦
1
answered Jun 30 '16 at 16:31
KinKin
53.5k480188
add a comment |
When you do a failover, a role reversal happens i.e. your primary becomes secondary and your secondary becomes primary.
ALTER DATABASE [yourDatabase]
SET PARTNER FAILOVER
GO
Doing so, only the role reversal happens (this is what happens when you initiate a failover using above command) and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN. Refer to my answer : If I fail over one database, do the others that share the same mirror endpoint fail over as well? for more details.
You can have your application use Failover Partner=your_mirror_serve_name, so that your application is redirected to replica database when a failover happens.
Refer to : connectionstring in Database Mirror setup
edited Apr 13 '17 at 12:42
Community♦
1
answered Jun 30 '16 at 16:31
KinKin
53.5k480188
When you do a failover, a role reversal happens i.e. your primary becomes secondary and your secondary becomes primary.
ALTER DATABASE [yourDatabase]
SET PARTNER FAILOVER
GO
Doing so, only the role reversal happens (this is what happens when you initiate a failover using above command) and mirroring direction is reversed - principal becomes mirror and vice-versa. Note that MIRRORING is not BROKEN. Refer to my answer : If I fail over one database, do the others that share the same mirror endpoint fail over as well? for more details.
You can have your application use Failover Partner=your_mirror_serve_name, so that your application is redirected to replica database when a failover happens.
Refer to : connectionstring in Database Mirror setup
edited Apr 13 '17 at 12:42
Community♦
1
answered Jun 30 '16 at 16:31
KinKin
53.5k480188
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
1
answered Jun 30 '16 at 16:31
KinKin
53.5k480188
answered Jun 30 '16 at 16:31
KinKin
53.5k480188
answered Jun 30 '16 at 16:31
KinKin
53.5k480188
53.5k480188
add a comment |
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%2f142691%2fwhat-exactly-happens-during-database-mirroring-what-about-during-a-fail-over%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
Mario, are you using automatic failover?
– QWE
Jun 30 '16 at 15:28
@QWE nope, we're using manual failover (maybe forced manual failover)
– Mario Merendino
Jun 30 '16 at 15:30
If you run this on your mirror server (which became primary after failover) then your servers will be back to their original roles. 'ALTER DATABASE [yourDatabase] SET PARTNER FAILOVER GO' Please have a look: link
– QWE
Jun 30 '16 at 15:36
Also this one: sqlservercentral.com/blogs/robert_davis/2009/02/22/…
– QWE
Jun 30 '16 at 15:46