| Author |
Topic |
|
tanvimehta
Starting Member
6 Posts |
 Posted - 2006-03-21 : 11:07:43
|
| Hey folks, I have a question on the Snapshot Replication.We have a snapshot replication that runs periodically on our production server to our reporting server. We have a database of around 25gb, that includes one table with over 30 million rows.The issue is that it takes around 1 hour to generate the snapshot and it took around 7 hours to apply the articles. This is the first time that I have to work on replications, so was just curious if this is normal ?FYI, the replication process was a success. Any kinda help will be appreciated. Thanks,Tanvi. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-21 : 12:34:57
|
| Yes, this is normal.. Does the table structure change between snapshots in any way? If not, then use transactional replication instead as this will only pass any new transactions to the subscriber (INSERTS/DELETES/UPDATES).. |
 |
|
|
tanvimehta
Starting Member
6 Posts |
Posted - 2006-03-21 : 12:47:22
|
| Thanks for the reply, Rick.Yes, there are changes made to the database, but its just that my boss wants to pursue with Snapshot replication.I was worried about the duration of the process because the other database that is almost twice the size, takes only around 90 minutes for the replication to succeed whereas this takes around 9-10 hours each time. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-21 : 13:01:02
|
| It would depend on your network and the link to this server, if it is a slow link or the disks in this server are very slow, a snapshot will take this time, also you may want to check if anyone is using the subscriber or publisher when you do your snapshot as this will affect the time taken.. |
|
|
|
tanvimehta
Starting Member
6 Posts |
Posted - 2006-03-21 : 13:46:30
|
| The conditions are the same for both the databases, they both reside on the same drive and are replicated to the same drive of the other server.There are no users connected to the databases while the replication is in process, since it is run over the weekend.The only difference is that the slow running database has this table with over 30 million rows that takes 7-8 hours to be copied into the subscriber. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-22 : 06:03:56
|
| Does the table have Indexes? What is the row size?It may be quicker if you delete the old snapshot manually before you run the new one as you then don't have to wait for the drop to happen.. |
|
|
|
tanvimehta
Starting Member
6 Posts |
Posted - 2006-03-22 : 09:06:53
|
| Yes, the table does have indexes.Data size is around 13gb and the indexes are around 12gb.There are around 30 million rows in that table.It doesn't take that long to generate the snapshot (approx. 1 hour)But it takes 7-8 hours to apply the snapshot to the subscriber. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-22 : 09:29:29
|
| What is the potential length of each of the rows though? Does it exceed 8092 bytes? |
|
|
|
tanvimehta
Starting Member
6 Posts |
Posted - 2006-03-22 : 10:31:33
|
| No it doesn't.Just some ints, varchars and datetimes.51 fields to be precise. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-22 : 12:04:34
|
| Can you post the structure? |
|
|
|
tanvimehta
Starting Member
6 Posts |
Posted - 2006-03-22 : 12:47:49
|
| Finally, I have convinced my supervisor to use Transactional replication. Hopefully, that works fine. Thanks for your support. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-22 : 12:57:22
|
No problem..  |
|
|
|
|
Replication