September 10, 2015 at 10:18 am
Hi, i am planning to set up AlwaysOn with 20 databases, Automatic failover(synchronous) in my current environment, SQL server 2014,windows server std 2012 configure WSFC on 2 nodes ,
Node1 Node2 ( both Enterprise sql server editions), both servers located in 2 different locations
In the event of any failures below listed i want to failover from Node1 to Node2.
1.Operating System goes down
2.SQL Server Instance goes offline/Shutdown
3.Disk Failures
4.Network goes down
5.Cpu, motherboard goes bad
6.database goes offilne.
My questions:
1.where do i have to keep AGListner? node 3 ? and if yes node3 (for safety)--what are other options?
2.do i have to use SQL enterprise edition or will SQL express edition works?
3.what about operating server ? and Node3 should also configure WSFC?
4.I am also planning to use node3(SQL express) as distributor because i have replication from node1 to different server., do distributor works on Express edition ?
3. all my Fail over listed above(1-6) works with always on ?
please give some tips
September 10, 2015 at 11:57 am
Someone can chime in if I am wrong on something here.
1. Listeners reside on all nodes in the AG simultaneously.
2. Enterprise Edition is required for Availability Groups
3. If you have node 3 in the AG then the WSFC setup is required, just not the shared disk component.
4. Last I knew SQL Server Express functions only as a Subscriber.
3. To some extent all of these are covered in a 3 node scenario, however if the network goes down your applications can't connect to the db anyway. You mention that you want to fail over from node1 to node2, a disk failure will cause an outage because the FC between those two shares a disk. In this case node3 would take over as primary.
September 10, 2015 at 12:21 pm
Hey Thanks for the reply..
3. To some extent all of these are covered in a 3 node scenario, however if the network goes down your applications can't connect to the db anyway.
----putting node1 and node 2 physically in two different locations help even if network goes down?
You mention that you want to fail over from node1 to node 2, a disk failure will cause an outage because the FC between those two shares a disk
---I am not using old style SQL Fail over clustering, i am going to install 2 or 3 stand alone sql server servers and the configure them in Windows cluster, two nodes will have two separate servers and seperate disks and they are not shared and they sit in two different locations
September 10, 2015 at 12:43 pm
----putting node1 and node 2 physically in two different locations help even if network goes down?
It totally depends on where the network failure is located. Between the locations, total outage at one site, partial outage and where is that outage make a big difference. Remember that only 1 database is writable at any one time.
---I am not using old style SQL Fail over clustering, i am going to install 2 or 3 stand alone sql server servers and the configure them in Windows cluster, two nodes will have two separate servers and seperate disks and they are not shared and they sit in two different locations
Ok, in this case you must use Always On Availability Groups not Always on Failover Clustering. I don't think there is an absolute need for a 3rd node, but it could be helpful especially as a witness server.
September 10, 2015 at 1:33 pm
thanks JeepHound
September 13, 2015 at 12:44 pm
nari.koud (9/10/2015)
Hi, i am planning to set up AlwaysOn with 20 databases, Automatic failover(synchronous) in my current environment, SQL server 2014,windows server std 2012 configure WSFC on 2 nodes ,Node1 Node2 ( both Enterprise sql server editions), both servers located in 2 different locations
In the event of any failures below listed i want to failover from Node1 to Node2.
1.Operating System goes down
2.SQL Server Instance goes offline/Shutdown
3.Disk Failures
4.Network goes down
5.Cpu, motherboard goes bad
6.database goes offilne.
5, 3 and 1 are all relevant to 2, if the instance goes offline and you're configured for automatic failover then this will be covered.
nari.koud (9/10/2015)
My questions:1.where do i have to keep AGListner? node 3 ? and if yes node3 (for safety)--what are other options?
sorry, where does node 3 come from, above you only list 2 nodes
nari.koud (9/10/2015)
2.do i have to use SQL enterprise edition or will SQL express edition works?
Enterprise only.
nari.koud (9/10/2015)
3.what about operating server ? and Node3 should also configure WSFC?
do you mean operating system?
are you planning to add a 3rd node?
nari.koud (9/10/2015)
nari.koud (9/10/2015)
3. all my Fail over listed above(1-6) works with always on ?please give some tips
see above
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply