I am using SQL Server 2005 Standard Edition. I have a primary DB and a read-only replica with Transaction replication configured. My understanding is that with transactional replication that the 'INSERT' statement would not return until
- The row is inserted into the primary, and
- The row was completely replicated to the replica DB
However, I am experiencing a problem where we do an insert, it returns (I know this because I am using an identity PK column, and I have my ID value). I then run a SELECT statement on the replica DB where I attempt to lookup the row by ID, but it does not yet exist. If I wait a few more seconds everything is fine. Is my understanding of transactional replication off? Is there a way to make it work the way I am thinking?