Quorum question for SQL Cluster (Always On)
We are looking at setting up an SQL Always On High Availability environment at our organisation.
Our organisation has two datacenters of with one much smaller one but this one's being created to create some high availability.
At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)
But here comes the problem. When the primary site is down due to a power outage for example, the whole cluster will be down because the one server doesn't have a majority even with a default quorum. We did also look at adding a quorum to cloud-storage (disk-only quorum setup) but when the internet connection is down (happens sometimes here in Belgium) we also loose the entire cluster.
Does anyone have any experience or a solution for this?
Thanks in advance!
sql-server sql-server-2016 clustering high-availability
edited Nov 16 '18 at 10:32
Tom V
13.8k74677
asked Nov 15 '18 at 14:09
chittybangchittybang
233
add a comment |
We are looking at setting up an SQL Always On High Availability environment at our organisation.
Our organisation has two datacenters of with one much smaller one but this one's being created to create some high availability.
At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)
But here comes the problem. When the primary site is down due to a power outage for example, the whole cluster will be down because the one server doesn't have a majority even with a default quorum. We did also look at adding a quorum to cloud-storage (disk-only quorum setup) but when the internet connection is down (happens sometimes here in Belgium) we also loose the entire cluster.
Does anyone have any experience or a solution for this?
Thanks in advance!
sql-server sql-server-2016 clustering high-availability
edited Nov 16 '18 at 10:32
Tom V
13.8k74677
asked Nov 15 '18 at 14:09
chittybangchittybang
233
So you have 4 node WSFC where 3 nodes are in one DC and last node in other DC correct ?.
– Shanky
Nov 15 '18 at 16:09
@Shanky: That's correct.
– chittybang
Nov 15 '18 at 20:52
add a comment |
We are looking at setting up an SQL Always On High Availability environment at our organisation.
Our organisation has two datacenters of with one much smaller one but this one's being created to create some high availability.
At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)
But here comes the problem. When the primary site is down due to a power outage for example, the whole cluster will be down because the one server doesn't have a majority even with a default quorum. We did also look at adding a quorum to cloud-storage (disk-only quorum setup) but when the internet connection is down (happens sometimes here in Belgium) we also loose the entire cluster.
Does anyone have any experience or a solution for this?
Thanks in advance!
sql-server sql-server-2016 clustering high-availability
edited Nov 16 '18 at 10:32
Tom V
13.8k74677
asked Nov 15 '18 at 14:09
chittybangchittybang
233
We are looking at setting up an SQL Always On High Availability environment at our organisation.
Our organisation has two datacenters of with one much smaller one but this one's being created to create some high availability.
At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)
But here comes the problem. When the primary site is down due to a power outage for example, the whole cluster will be down because the one server doesn't have a majority even with a default quorum. We did also look at adding a quorum to cloud-storage (disk-only quorum setup) but when the internet connection is down (happens sometimes here in Belgium) we also loose the entire cluster.
Does anyone have any experience or a solution for this?
Thanks in advance!
sql-server sql-server-2016 clustering high-availability
sql-server sql-server-2016 clustering high-availability
edited Nov 16 '18 at 10:32
Tom V
13.8k74677
asked Nov 15 '18 at 14:09
chittybangchittybang
233
edited Nov 16 '18 at 10:32
Tom V
13.8k74677
asked Nov 15 '18 at 14:09
chittybangchittybang
233
edited Nov 16 '18 at 10:32
Tom V
13.8k74677
edited Nov 16 '18 at 10:32
Tom V
13.8k74677
edited Nov 16 '18 at 10:32
Tom V
13.8k74677
13.8k74677
asked Nov 15 '18 at 14:09
chittybangchittybang
233
asked Nov 15 '18 at 14:09
chittybangchittybang
233
asked Nov 15 '18 at 14:09
chittybangchittybang
233
233
So you have 4 node WSFC where 3 nodes are in one DC and last node in other DC correct ?.
– Shanky
Nov 15 '18 at 16:09
@Shanky: That's correct.
– chittybang
Nov 15 '18 at 20:52
add a comment |
So you have 4 node WSFC where 3 nodes are in one DC and last node in other DC correct ?.
– Shanky
Nov 15 '18 at 16:09
@Shanky: That's correct.
– chittybang
Nov 15 '18 at 20:52
So you have 4 node WSFC where 3 nodes are in one DC and last node in other DC correct ?.
– Shanky
Nov 15 '18 at 16:09
So you have 4 node WSFC where 3 nodes are in one DC and last node in other DC correct ?.
– Shanky
Nov 15 '18 at 16:09
@Shanky: That's correct.
– chittybang
Nov 15 '18 at 20:52
@Shanky: That's correct.
– chittybang
Nov 15 '18 at 20:52
add a comment |
1 Answer
1
active
oldest
votes
At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)
With the current configuration there is no way you can keep WSFC running if primary site, which has 3 nodes out of 4 nodes WSFC, goes down due to power outage or some other disaster. With such configuration adding Fileshare witness would be useless because even if you add one there will be 5 voting members and for WSFC to be online there should be at least 3 online and in your case when primary DC goes down 3 voting members will be down bringing WSFC down.
Does anyone have any experience or a solution for this?
The solution would be to change the architecture, IF you do not want WSFC to come down when primary DC goes down. What you can do is move the 3rd node from primary to secondary DC and configure FC witness in cloud or at some third location which is not secondary DC or Primary DC but accessible to both, so even if primary DC is down there are 3 voting members 2 from secondary DC and one FS witness to keep WSFC up and running.
The other solution is, if you can afford WSFC downtime, let the architecture be as it is and when disaster strikes at primary DC use Forcequorum to bring WSFC online. This seems like bad choice at first but believe me with little practice it works like charm and within 10-15 mins or even less WSFC would be online on DR node and with
failover with data lossyou could bring database instance online as well. This method is quick with little practice all you need is few commands to be run to bring WSFC and databases online.
Similar readings
- Force Start a Windows Server Failover Cluster without a Quorum to bring a SQL Server Failover Clustered Instance Online
- Recover WSFC using Forced Quorum for SQL Server AlwaysOn Availability Group
answered Nov 16 '18 at 7:37
ShankyShanky
14.1k32039
Thank you for the explanation. I'll discuss the downtime with the collegues but I'm afraid this won't be an option. The first part of your answer: is a disk-only quorum no solution for this? (not for us because of a possible downtime of the internet connection)
– chittybang
Nov 16 '18 at 8:54
Disk quorum is not solution because no matter where it resides it will be of no use when primary DC is down
– Shanky
Nov 16 '18 at 12:40
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%2f222635%2fquorum-question-for-sql-cluster-always-on%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
At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)
With the current configuration there is no way you can keep WSFC running if primary site, which has 3 nodes out of 4 nodes WSFC, goes down due to power outage or some other disaster. With such configuration adding Fileshare witness would be useless because even if you add one there will be 5 voting members and for WSFC to be online there should be at least 3 online and in your case when primary DC goes down 3 voting members will be down bringing WSFC down.
Does anyone have any experience or a solution for this?
The solution would be to change the architecture, IF you do not want WSFC to come down when primary DC goes down. What you can do is move the 3rd node from primary to secondary DC and configure FC witness in cloud or at some third location which is not secondary DC or Primary DC but accessible to both, so even if primary DC is down there are 3 voting members 2 from secondary DC and one FS witness to keep WSFC up and running.
The other solution is, if you can afford WSFC downtime, let the architecture be as it is and when disaster strikes at primary DC use Forcequorum to bring WSFC online. This seems like bad choice at first but believe me with little practice it works like charm and within 10-15 mins or even less WSFC would be online on DR node and with
failover with data lossyou could bring database instance online as well. This method is quick with little practice all you need is few commands to be run to bring WSFC and databases online.
Similar readings
- Force Start a Windows Server Failover Cluster without a Quorum to bring a SQL Server Failover Clustered Instance Online
- Recover WSFC using Forced Quorum for SQL Server AlwaysOn Availability Group
answered Nov 16 '18 at 7:37
ShankyShanky
14.1k32039
Thank you for the explanation. I'll discuss the downtime with the collegues but I'm afraid this won't be an option. The first part of your answer: is a disk-only quorum no solution for this? (not for us because of a possible downtime of the internet connection)
– chittybang
Nov 16 '18 at 8:54
Disk quorum is not solution because no matter where it resides it will be of no use when primary DC is down
– Shanky
Nov 16 '18 at 12:40
add a comment |
At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)
With the current configuration there is no way you can keep WSFC running if primary site, which has 3 nodes out of 4 nodes WSFC, goes down due to power outage or some other disaster. With such configuration adding Fileshare witness would be useless because even if you add one there will be 5 voting members and for WSFC to be online there should be at least 3 online and in your case when primary DC goes down 3 voting members will be down bringing WSFC down.
Does anyone have any experience or a solution for this?
The solution would be to change the architecture, IF you do not want WSFC to come down when primary DC goes down. What you can do is move the 3rd node from primary to secondary DC and configure FC witness in cloud or at some third location which is not secondary DC or Primary DC but accessible to both, so even if primary DC is down there are 3 voting members 2 from secondary DC and one FS witness to keep WSFC up and running.
The other solution is, if you can afford WSFC downtime, let the architecture be as it is and when disaster strikes at primary DC use Forcequorum to bring WSFC online. This seems like bad choice at first but believe me with little practice it works like charm and within 10-15 mins or even less WSFC would be online on DR node and with
failover with data lossyou could bring database instance online as well. This method is quick with little practice all you need is few commands to be run to bring WSFC and databases online.
Similar readings
- Force Start a Windows Server Failover Cluster without a Quorum to bring a SQL Server Failover Clustered Instance Online
- Recover WSFC using Forced Quorum for SQL Server AlwaysOn Availability Group
answered Nov 16 '18 at 7:37
ShankyShanky
14.1k32039
Thank you for the explanation. I'll discuss the downtime with the collegues but I'm afraid this won't be an option. The first part of your answer: is a disk-only quorum no solution for this? (not for us because of a possible downtime of the internet connection)
– chittybang
Nov 16 '18 at 8:54
Disk quorum is not solution because no matter where it resides it will be of no use when primary DC is down
– Shanky
Nov 16 '18 at 12:40
add a comment |
At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)
With the current configuration there is no way you can keep WSFC running if primary site, which has 3 nodes out of 4 nodes WSFC, goes down due to power outage or some other disaster. With such configuration adding Fileshare witness would be useless because even if you add one there will be 5 voting members and for WSFC to be online there should be at least 3 online and in your case when primary DC goes down 3 voting members will be down bringing WSFC down.
Does anyone have any experience or a solution for this?
The solution would be to change the architecture, IF you do not want WSFC to come down when primary DC goes down. What you can do is move the 3rd node from primary to secondary DC and configure FC witness in cloud or at some third location which is not secondary DC or Primary DC but accessible to both, so even if primary DC is down there are 3 voting members 2 from secondary DC and one FS witness to keep WSFC up and running.
The other solution is, if you can afford WSFC downtime, let the architecture be as it is and when disaster strikes at primary DC use Forcequorum to bring WSFC online. This seems like bad choice at first but believe me with little practice it works like charm and within 10-15 mins or even less WSFC would be online on DR node and with
failover with data lossyou could bring database instance online as well. This method is quick with little practice all you need is few commands to be run to bring WSFC and databases online.
Similar readings
- Force Start a Windows Server Failover Cluster without a Quorum to bring a SQL Server Failover Clustered Instance Online
- Recover WSFC using Forced Quorum for SQL Server AlwaysOn Availability Group
answered Nov 16 '18 at 7:37
ShankyShanky
14.1k32039
At our primary site we are looking at installing three servers and one at the secondary site. These server will then be added to a cluster (and we'll add the DB's in some Availability Groups accross these servers)
With the current configuration there is no way you can keep WSFC running if primary site, which has 3 nodes out of 4 nodes WSFC, goes down due to power outage or some other disaster. With such configuration adding Fileshare witness would be useless because even if you add one there will be 5 voting members and for WSFC to be online there should be at least 3 online and in your case when primary DC goes down 3 voting members will be down bringing WSFC down.
Does anyone have any experience or a solution for this?
The solution would be to change the architecture, IF you do not want WSFC to come down when primary DC goes down. What you can do is move the 3rd node from primary to secondary DC and configure FC witness in cloud or at some third location which is not secondary DC or Primary DC but accessible to both, so even if primary DC is down there are 3 voting members 2 from secondary DC and one FS witness to keep WSFC up and running.
The other solution is, if you can afford WSFC downtime, let the architecture be as it is and when disaster strikes at primary DC use Forcequorum to bring WSFC online. This seems like bad choice at first but believe me with little practice it works like charm and within 10-15 mins or even less WSFC would be online on DR node and with
failover with data lossyou could bring database instance online as well. This method is quick with little practice all you need is few commands to be run to bring WSFC and databases online.
Similar readings
- Force Start a Windows Server Failover Cluster without a Quorum to bring a SQL Server Failover Clustered Instance Online
- Recover WSFC using Forced Quorum for SQL Server AlwaysOn Availability Group
answered Nov 16 '18 at 7:37
ShankyShanky
14.1k32039
edited 12 mins ago
answered Nov 16 '18 at 7:37
ShankyShanky
14.1k32039
answered Nov 16 '18 at 7:37
ShankyShanky
14.1k32039
answered Nov 16 '18 at 7:37
ShankyShanky
14.1k32039
14.1k32039
Thank you for the explanation. I'll discuss the downtime with the collegues but I'm afraid this won't be an option. The first part of your answer: is a disk-only quorum no solution for this? (not for us because of a possible downtime of the internet connection)
– chittybang
Nov 16 '18 at 8:54
Disk quorum is not solution because no matter where it resides it will be of no use when primary DC is down
– Shanky
Nov 16 '18 at 12:40
add a comment |
Thank you for the explanation. I'll discuss the downtime with the collegues but I'm afraid this won't be an option. The first part of your answer: is a disk-only quorum no solution for this? (not for us because of a possible downtime of the internet connection)
– chittybang
Nov 16 '18 at 8:54
Disk quorum is not solution because no matter where it resides it will be of no use when primary DC is down
– Shanky
Nov 16 '18 at 12:40
Thank you for the explanation. I'll discuss the downtime with the collegues but I'm afraid this won't be an option. The first part of your answer: is a disk-only quorum no solution for this? (not for us because of a possible downtime of the internet connection)
– chittybang
Nov 16 '18 at 8:54
Thank you for the explanation. I'll discuss the downtime with the collegues but I'm afraid this won't be an option. The first part of your answer: is a disk-only quorum no solution for this? (not for us because of a possible downtime of the internet connection)
– chittybang
Nov 16 '18 at 8:54
Disk quorum is not solution because no matter where it resides it will be of no use when primary DC is down
– Shanky
Nov 16 '18 at 12:40
Disk quorum is not solution because no matter where it resides it will be of no use when primary DC is down
– Shanky
Nov 16 '18 at 12:40
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%2f222635%2fquorum-question-for-sql-cluster-always-on%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
So you have 4 node WSFC where 3 nodes are in one DC and last node in other DC correct ?.
– Shanky
Nov 15 '18 at 16:09
@Shanky: That's correct.
– chittybang
Nov 15 '18 at 20:52