Database replication. 2 servers, Master database and the 2nd is read-only

Question

Say you have 2 database servers, one database is the 'master' database where all write operations are performed, it is treated as the 'real/original' database. The other server's database is to be a mirror copy of the master database (slave?), which will be used for read only operations for a certain part of the application.

How do you go about setting up a slave database that mirrors the data on the master database? From what I understand, the slave/readonly database is to use the master db's transaction log file to mirror the data correct?
What options do I have in terms of how often the slave db mirrors the data? (real time/every x minutes?).

Answer 1

The answer to this will vary depending on the database server you are using to do this.

Edit: Sorry, maybe i need to learn to look at the tags and not just the question - i can see you tagged this as sqlserver.

Answer 2

What you want is called Transactional Replication in SQL Server 2005. It will replicate changes in near real time as the publisher (i.e. "master") database is updated.

Here is a pretty good walk through of how to set it up.

Answer 3

SQL Server 2008 has three different modes of replication.

• Transactional for one way read only replication
• Merge for two way replication
• Snapshot

Answer 4

From what I understand, the slave/readonly database is to use the master db's transaction log file to mirror the data correct? What options do I have in terms of how often the slave db mirrors the data? (real time/every x minutes?).

This sounds like you're talking about log shipping instead of replication. For what you're planning on doing though I'd agree with Jeremy McCollum and say do transactional replication. If you're going to do log shipping when the database is restored every x minutes the database won't be available.

Here's a good walkthrough of the difference between the two. Sad to say you have to sign up for an account to read it though. =/ http://www.sqlservercentral.com/articles/Replication/logshippingvsreplication/1399/

Answer 5

Transactional replication is real time.

If you do not have any updates to be done on your database , what you need is just retrieving of data say once a day : then use snapshot replication instead of transactional replication. In snapshot replication, changes will replicate when and as defined by the user say once in 24 hrs.