One of my friends was asked this question in interview. Given two different databases one on which only write is done and the other on which only read is done. How does one maintain synchronization between the two given a specified time lag. I would like to add that these databases have same schema.
Eg: R is my Read database and W is my Write database. I want to ensure that read database should be the replica of Write database say a minute earlier.
What I am keen here is on the techniques(concepts) this is done rather than the implementation. Lets say the RDBMS is SQL Server.
Best Answer
In SQL Server 2012 you can have read-only secondaries, which allow you to use Availability Groups to mirror a database or set of databases to another server, and perform read-only queries against them even as they are being mirrored.
The downside: this requires Enterprise Edition on both nodes, which can only be licensed per core (not CAL), and the underlying OS needs to be WSFC, so the OS needs to be Enterprise Edition as well (this is less of a big deal since the additional cost of Enterprise at the OS level is laughable compared to the jump in SQL Server license costs).
In previous versions you could do this with log shipping (subject to the annoying limitations @datagod already mentioned) or with mirroring + snapshots (which also needs Enterprise Edition). I haven't seen any of our customers using replication for this specific requirement but I suppose that is possible as well.