I tried many things and analyzed lots of documents but I haven't found a solution yet.
I have three virtual machines in VmWare called (DC,SQLServer01,SQLServer02). All of SQL Servers are member of a domain.(DC) I installed failover cluster for SQLServer01 and SQLServer02. I did necessary configurations in SQLServer01. Then I installed SQL Server 2014 for both servers. Now, I created an alwaysOn group. SQLServer01 is a primary and other is secondary. When I cut the connection of SQLServer01, everything is fine (Secondary becomes primary). It is acceptable for other condition.
However, when all servers are online, I can not do any operation (insert,update,delete,alter ,etc) except read operations in my secondary replica. I see always "database is read only" error. In properties of Alwayson group, both primary and secondary replica have all connections and secondary readable is "YES".
I want to make CRUD operations even if all servers are online. (I mean, do everything also for secondary replica. )
So, do you have any suggestion or idea?
Thank your time and consideration.
The error occurs because writing to secondary replicas in sql server is not possible. Only primary replica can host read-write databases, and an availability group can only have one single primary replica. Secondary replicas can host read-only databases only. When both replicas are available, only one of the two can be the primary and therefore support read-write. When only a single replica is available, that replica becomes primary replica because there are no other replicas, and read-write operations against that replica is possible.
What you need to configure instead is replication.
In SQL Server, merge replication allows you to write at multiple nodes, with periodic synchronization that resolves conflicts and pushes changes to all replicas.
Peer to Peer replication is another solution. Application layer must not allow conflicts (update of same row at more than one node), but is much faster.