March 30, 2007 at 6:34 am
Guys,
I am trying to replicate the production data (25GB database) on to a backup server. The snapshot agent is scheduled to
run at 11pm and replication is set to run at 12am.
What I am worried if the snapshot process will slow down the production database drastically and if that is the case what kind of replication - Merge, transaction would optimize the replication replication.
Any suggestions/inputs would help.
Thanks
March 30, 2007 at 9:54 am
There's likely to be a slowdown when the snapshot takes place. I would suspect that it won't be much more than when you do a full database backup. Whether the slowdown will be noticeable or not, depends on how much of a load the computer is already under (specifically IO) If the machine is doing little IO (for example if your applications are read intensive rather than write intensive). Also, you should check that there are no other processes taking place when the snapshot agent runs. For example, defer your backups until after it finishes.
As to what type of replication you should use, it depends on where the data will be updated. If the data is only updated in the primary server, then use transactional replication. If the data is updated in both servers and you want to maintain both copies up to date then use merge replication.
Carlos
March 30, 2007 at 1:50 pm
I have experienced out and out work stoppage during generation of a snapshot - to include long term blocking and application timeouts. How often do you change your schema - add columns, tables, etc? Because if you don't do it at all or not that often, then you don't need the snapshot ran as often. I would apply the first snapshot and then run transactional replication. I'd also set your distribution agent to run on your subscriber to mitigate load on your source.
Good luck.
😎
March 31, 2007 at 7:07 am
If you're doing transactional replication you can minimize blocking by doing a 'concurrent' snapshot. Requires you to check the box in the pub options in SQL2K, is the default in 2005. I agree that load similar to backup or less.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply