May 6, 2009 at 6:41 pm
I'm not VERY familiar with Replication so please forgive me if these questions are elementary:
We have a 19 GB production DB and I need to replicate (transactional) ALL of it to a reporting (dev) DB real time.
Is it ideal to replicate something like this every 5 minutes to keep it real time?
Can you replicate the production DB to the dev DB while they're both online? (I've heard one has to be offline, which doesn't make sense at all)
Is there a way to replicate this during peak hours without the processor spiking?
What do you suggest I do in a situation like this?
The only way to replicate everything would be to use snapshot replication because transactional doesn't replicate tables without a PK?
May 6, 2009 at 10:33 pm
We have a 19 GB production DB and I need to replicate (transactional) ALL of it to a reporting (dev) DB real time.
Is it ideal to replicate something like this every 5 minutes to keep it real time?
"Ideal" is really up to you to define - based on your requirements. Replication can be scheduled every 5 minutes if you want.
Can you replicate the production DB to the dev DB while they're both online? (I've heard one has to be offline, which doesn't make sense at all)
You are correct - offline does not make sense although, since transactional replication is actually store and forward (via the distribution database), it is possible that the replica is offline for a period of time without causing any problems. However, it must be online for it to be updated.
Is there a way to replicate this during peak hours without the processor spiking?
Not really - the only way to stop this would be to schedule the replication jobs to run off-peak. Some replication configurations can help to reduce the load (e.g. running the replication agents from a different server, using a different server as the distribution server).
What do you suggest I do in a situation like this?
Use a different server as the distributor, run all agents from this server and possibly schedule the agents to run offpeak
The only way to replicate everything would be to use snapshot replication because transactional doesn't replicate tables without a PK?
Transactional replication will not replicate a table that has not PK. Snapshot becomes you only choice if you cannot define a PK on these tables.
May 8, 2009 at 7:34 am
Also if you have tables without PK, fix them!
* Noel
May 13, 2009 at 11:26 am
Thanks for the info.
I have replication setup now on 3 different servers. (Pub, Dist, Sub)
I'm trying to replicate around 100 tables from a production server to a reporting server about every 30 minutes, but it's taking FOREVER. Is there a better way to acheive what I'm trying to do with my reporting server?
May 13, 2009 at 1:06 pm
brade11 (5/13/2009)
Thanks for the info.I have replication setup now on 3 different servers. (Pub, Dist, Sub)
I'm trying to replicate around 100 tables from a production server to a reporting server about every 30 minutes, but it's taking FOREVER. Is there a better way to acheive what I'm trying to do with my reporting server?
What type of replication did you chose, transactional ?
How many rows are you affecting ?
Have you tried replicating "continuously (if you are using transactional) ?
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply