May 21, 2016 at 6:16 am
Hello,
Please give me a best solution with combined the below feature in sql server 2012. i.e. High Availability + Load balancing + Fail over.
At least 2 database servers, both should have the read/write ability at the same time.
If anyone fails, another one should work effectively as a transactional database (not read only) automatically with out any application/site issue.
please send if you have any doc/architecture diagram to sudhakar_1982@ymail.com
Thanks.
May 21, 2016 at 7:28 pm
You can read about clustering here: https://msdn.microsoft.com/en-us/library/ms189134%28v=sql.110%29.aspx
There is no such thing as two nodes, each read/write to the same database. That isn't supported in SQL Server and never has been. One node always owns the databases and can do read/write. With Availability Groups, you have have read only nodes ,but writes always go to the active node.
Active Active clustering means that one node has Databases a, b, c taking reads and writes. The other node has Databases e, f, g taking reads and writes. If either node fails, then the remaining node(s) picks up the failed databases.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 30, 2016 at 1:05 am
Thanks Steve.
I have one more question - Can we run sql agent jobs in multiple instance of a cluster (active) node at a time (This is a two node cluster, running 4 instances, two instances per node)?
May 30, 2016 at 10:36 am
Agent jobs are per instance. Meaning that if you fail over, then you need to enable the jobs on the new instance and disable them on the old instance. This is one area that clustering hasn't been enhanced, though I'm hoping to see jobs in databases soon, which will solve this.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 31, 2016 at 1:56 pm
sudhakar_1982 (5/21/2016)
Hello,Please give me a best solution with combined the below feature in sql server 2012. i.e. High Availability + Load balancing + Fail over.
At least 2 database servers, both should have the read/write ability at the same time.
If anyone fails, another one should work effectively as a transactional database (not read only) automatically with out any application/site issue.
please send if you have any doc/architecture diagram to sudhakar_1982@ymail.com
Thanks.
Please read my stairway starting at this link
http://qa.sqlservercentral.com/stairway/112556/[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 31, 2016 at 2:28 pm
There is no such thing as two nodes, each read/write to the same database. That isn't supported in SQL Server and never has been.
That is not necessarily true. SQL Server doesn't support a shared everything architecture like an Oracle RAC instance might, but SQL Server can support multiple nodes writing to the same database at the same time. Merge replication and Peer-to-peer transactional replication are two ways to achieve this that come to mind. Each require design considerations though.
Joie Andrew
"Since 1982"
May 31, 2016 at 3:10 pm
Merge and peer replication do not have people writing to the same database. There isn't consistency, nor is there guarantees the writes will remain. In both those systems, you write to one database and then that data is transferred to the other. This is managed by SQL Server, but there's no writing to one database. These are two databases, with potential sync issues, delays, conflicts, and more.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 1, 2016 at 8:51 am
SQL Agents are per instance, as Steve has said, however, they fail over with the instance, as long as it's in the same cluster group as the instance.
Leonard
June 1, 2016 at 9:03 am
SQL Jobs are per instance but if you use always on availability groups you can setup a job step to check if this is primary before executing the desired task.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply