Our Web API app is C#/MS-SQL and our DBAs want us to use two databases, one Writable, one Read-only. The database for writes will replicate to the read-only copy. This question may apply to other languages as well.
The reason for this (from the DBAs) was so performance would not be impacted if/when tables are locked during a write.
We are trying to determine the best way to have our Show/Get methods use Readonly, yet ensure if there was just a write operation, we have fresh data. One suggestion was having a ReadOnlyToWritableData connection, but then why have the Readonly database.
What are some solutions already being used? I searched around, and couldn't find anything definitive.
UPDATE: I was informed that our DB patter is an 'Always On Availability Group'.
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server
I may need to do more research.
Best Answer
This is an availability group setup where the application writes to one instance of the database and the reads from another instance. It is database sharding, which is not an uncommon practice and is something you will see for databases with an extremely high number of read and write operations. This is how to maintain scalability for large scale web applications (ecommerce).
An example of this issue could be seen with carts. A user adds an item to their cart, it is saved to the database, then forwarded to the next page which reads the cart and shows the items. If there is a delay with the synchronization, this could be an issue. While the delay (if the environment is set up correctly) may only be a couple of milliseconds, if there is an issue on the secondary database, the read could pull the data before the new data was actually synchronized.
There are only two possibilities:
1 - Use the application intent on the database listener and the connection string. I have heard, however, that the SQL driver for .net did not reliably implement this feature (Microsoft not working well with Microsoft). I don't know if this is still true though. Application intent will automatically switch from rw to ro. However, even implementing this will not resolve the possible issue.
2 - Use a stored procedure for the times when you know you will instantly need the data. In our cart example, a procedure to add an item could then return a recordset with all of the items currently in the cart.
What the OP is asking is this: is there a way to do this in code? Has anyone done this in code (not the stored procedure answer)?