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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.