Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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.

share|improve this question
1  
The question is a bit too broad for this site. Different RDBMSes use different techniques (but usually revolve around log or statement shipping). Did you have a specific RDBMS in mind? – Phil Sep 12 '12 at 12:58
Lets say the RDBMS is SQL Server – Egalitarian Sep 12 '12 at 13:05

2 Answers

up vote 5 down vote

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.

share|improve this answer
up vote 3 down vote

Assuming SQL Server, the transactions that take place in the writeable database will need to be backed up and applied to a read-only copy of the database. This is known as log-shipping.

The downside with this approach is that the read-only database must be put into single user mode while the restore is taking place. Either the connected users are forced to exit, or the logs pile up waiting to be applied.

There may be an option in recent versions of SQL server that get around this, but I am not sure.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.