Replication taking too much time between requests

Question

We are using transactional replication in SQL Server 2014 with one master (publisher), 1 distributor (dedicated server), and 3 slaves (subscribers).

All writes are made to master and reading is done from one of the 3 subscribers.

My issue is that if you make a insert/update/delete and the page is refreshing the update isnt there yet. There is a 1-4 sec delay until subscribers are updated which will confuse the user as the row inserted/deleted/updated but isn't reflected on the subscribers yet...

We are considering going for peer-to-peer replication, but it seems to be an overhead with IDENTITY which goes back to write to one but it will also take too much time to replicate...

What could/should we do?

Answer 1

What could/should we do?

Discard the assumption of synchronous replication and redesign your application accordingly. Replication was never designed for this. Latency of hours can happen and you should design accordingly.

None of the existing SQL Server technologies, including Availability Groups readable secondaries in synchronous commit mode, make a guarantee of synchronous propagation of changes to replicas/secondaries. the only place from where you can read back your own changes and be guaranteed to see your own changes is the publisher/primary, where the changes were made in the first place. Any expectation to the contrary is folly.

Answer 2

Why do you expect nearly instant replication? That is not in the definition of replication. See the SQL Server Replication page. It says in part: "remember that replication is an asynchronous data-movement process that is likely to have some latency."

If you are using Availability Groups with synchronous connections, the secondary server will keep in synch with the primary as much as physically possible. This is not "instant" but is the design to keep the transactions in synch.

Answer 3

Rovdjuret: Do you have any suggestions to what could be done instead of this? Scale out database with instant response to at least end user?

I added a second answer because you asked another question entirely.

What you can do is limited by all the physical factors that prevent an activity from happening instantaneously.

Your customers need to understand that instantaneous does not exist, and that you latency is 1 to 10 seconds, or some such number. Then you endeavor to treat that as real limitation. The universe has rules, you know.

If you want to have things seem synchronous (even though they are not) you will need to enforce a delay someway. [Kludge Alert!]

1. Schedule a delay on executing the query on the second server of n seconds to give the data time to usually make it across. (Call it computational overhead, if you wish.)

2. If you keep a transactional time and date (as a datetime2 column) on every row, then you can calculate which rows you will return by using date math to exclude the final n seconds of data.

3. If use the point 2 philosophy on both the primary and secondary server, then the results on both servers will look synchronous most of the time.

But personally, I would not do any of that.