SQL 2012 Always on dilemma, need SQL expertise.

SQL Experts,

We have on Always On infrastructure with 4 SQL DB Servers.  We have a Primary and Secondary in the Main Site and a DR Primary and Secondary in the Secondary Site.

We have 4 Application servers in each of the 2 sites.  We want everything local, meaning for the Primary site we want the 2 application servers to read and write to the Primary DB server.  For the 2 Secondary Application servers we want it to read and write data to the Secondary DB server's.  However according to our DBA, that is not how it works.  The secondary DB servers are only read only and are unable to write from the secondary application servers.  So that means all 4 application servers can only write to the primary DB server only.

Is there anyway for the application write to any of the DB servers using the Always On feature?  Or what can we do so everything local from an application server perspective.  I want the application servers to only read/write to the local SQL 2012 servers in each site.

I appreciate any feedback!  I am not a SQL person, but would love a second opinion on how or if we can get this setup the way I need too!

Thanks!
LVL 1
mystikal1000Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
However according to our DBA, that is not how it works.  The secondary DB servers are only read only and are unable to write from the secondary application servers.  So that means all 4 application servers can only write to the primary DB server only.
Correct. It's how AlwaysOn works. Only the primary is writeable. Think that's is a mirror so all the secondaries are copies from the primary. What you can do is set for example the reports to point to the secondary node so will only read data and don't impact the performance on the primary node.
0
 
mystikal1000Author Commented:
Shucks :(
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You have Merge Replication but that is a little be complex since you need to be very careful to merge all the changes so you won't have repeated rows.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.