How to do an Active/Active server setup in sql server 2012.

Question

How to do an Active/Active server setup in sql server 2012.

Sudhakar Ramakrishnan

SSC Rookie

Points: 41
More actions
May 21, 2016 at 6:16 am

#316723

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

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.

Sudhakar Ramakrishnan SSC Rookie Points: 41 More actions · Answer 2

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)?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

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.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

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://www.sqlservercentral.com/stairway/112556/[/url]

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 5

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"

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

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.

Leonard Rutkowski SSCrazy Points: 2668 More actions · Answer 7

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

dannydudek SSC-Addicted Points: 486 More actions · Answer 8

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.