Is it possible to get sub-1-second latency with transactional replication?

Question

Our database architecture consists of two Sql Server 2005 servers each with an instance of the same database structure: one for all reads, and one for all writes. We use transactional replication to keep the read database up-to-date.

The two servers are very high-spec indeed (the write server has 32GB of RAM), and are connected via a fibre network.

When deciding upon this architecture we were led to believe that the latency for data to be replicated to the read server would be in the order of a few milliseconds (depending on load, obviously). In practice we are seeing latency of around 2-5 seconds in even the simplest of cases, which is unsatisfactory. By a simplest case, I mean updating a single value in a single row in a single table on the write db and seeing how long it takes to observe the new value in the read database.

What factors should we be looking at to achieve latency below 1 second? Is this even achievable?

Alternatively, is there a different mode of replication we should consider? What is the best practice for the locations of the data and log files?

Edit

Thanks to all for the advice and insight - I believe that the latency periods we are experiencing are normal; we were mis-led by our db hosting company as to what latency times to expect!

We're using the technique described near the bottom of this MSDN article (under the heading "scaling databases"), and we'd failed to deal properly with this warning:

The consequence of creating such specialized databases is latency: a write is now going to take time to be distributed to the reader databases. But if you can deal with the latency, the scaling potential is huge.

We're now looking at implementing a change to our caching mechanism that enforces reads from the write database when an item of data is considered to be "volatile".

Answer 1

I would say it's definately possible.

I would look at:

• Your network
  Run ping commands between the two servers and see if there are any issues
  If the servers are next to each other you should have < 1 ms.
• Bottlenecks on the server
  This could be network traffic (volume)
  Like network cards not being configured for 1GB/sec
  Anti-virus or other things
• Do some analysis on some queries and see if you can identify indexes or locking which might be a problem
• See if any of the selects on the read database might be blocking the writes.
  Add with (nolock), and see if this makes a difference on one or two queries you're analyzing.

Essentially you have a complicated system which you have a problem with, you need to determine which component is the problem and fix it.

Transactional replication is probably best if the reports / selects you need to run need to be up to date. If they don't you could look at log shipping, although that would add some down time with each import.

For data/log files, make sure they're on seperate drives so the performance is maximized.

Answer 2

Something to remember about transaction replication is that a single update now requires several operations to happen for that change to occur.

First you update the source table. Next the log readers sees the change and writes the change to the distribution database. Next the distribution agent sees the new entry in the distribution database and reads that change, then runs the correct stored procedure on the subscriber to update the row.

If you monitor the statement run times on the two servers you'll probably see that they are running in just a few milliseconds. However it is the lag time while waiting for the log reader and distribution agent to see that they need to do something which is going to kill you.

If you truly need sub second processing time then you will want to look into writing your own processing engine to handle data moving from one server to another. I would recommend using SQL Service Broker to handle this as this way everything is native to SQL Server and no third party code has to be written.

Answer 3

No. It's highly unlikely you could achieve sub-1s latency times with SQL Server transactional replication even with fast hardware.

If you can get 1 - 5 seconds latency then you are doing well.

From here:

Using transactional replication, it is possible for a Subscriber to be a few seconds behind the Publisher. With a latency of only a few seconds, the Subscriber can easily be used as a reporting server, offloading expensive user queries and reporting from the Publisher to the Subscriber.

In the following scenario (using the Customer table shown later in this section) the Subscriber was only four seconds behind the Publisher. Even more impressive, 60 percent of the time it had a latency of two seconds or less. The time is measured from when the record was inserted or updated at the Publisher until it was actually written to the subscribing database.