| Author |
Topic |
|
ericmaibach
Starting Member
4 Posts |
 Posted - 2008-09-23 : 09:46:21
|
| We are using snapshot replication to replicate our database over to a report server each night. We do this for a number of different databases. However we are having an issue with one of the databases were sometimes the replication takes a lot longer than normal. This issue occurs when it is creating the database snapshot, usually the snapshot agent only takes about 20 minutes to complete, however randomly it will all of a sudden take about an hour and a half to complete. This causes the replication to not complete by 7:30 AM which is when the report server needs to be available. I have looked at the history for the snapshot agent and the part that is taking longer is the bulk copying of the data. Does anyone have any suggestions why the bulk copying is taking so much longer some nights?Thank You |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-23 : 09:57:14
|
| You shouldn't do snapshot replication for Reporting need. It takes whole snapshot of database each time you run.Also it locks your huge database while generating snapshots.You should use Transactional replication instead where you configure Snapshot one time and All DML and DDL operations are replicated to subscriber database. |
 |
|
|
ericmaibach
Starting Member
4 Posts |
Posted - 2008-09-23 : 11:35:55
|
| Thank you for your post, and I completely agree with you. However these are databases that I just began managing about 60 days ago. I was told that they used to use transactional replication but that it was creating a lot of problems so they had to switch to snapshot replication. I believe that I can eventually move them back to transactional replication and overcome any problems that they where having. However I would prefer to not make that switch yet, my reasons are that I have not yet been able to get a clear description of what issues they experienced with replication and I don't want to repeat them, and that there are a lot of other things that are broken and need fixed and I only want to make so many changes at one time. So my goal is to keep the snapshot replication working for the next couple of months until I can safely migrate it over to transactional replication. However this issue of the snapshot randomly taking a lot longer to complete is creating problems. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-23 : 11:54:17
|
| Can you explain What issues are you having with Transactional Replication? So that we can solve it. We are using Transactional Replication for our reporting needs and is working at its best. Snapshot Replication for Reporting needs is not a good idea if you reports need to be consistent with Primary server. |
|
|
|
ericmaibach
Starting Member
4 Posts |
Posted - 2008-09-23 : 15:31:19
|
| The issue is that I don't know what issues where encountered when transactional replication was used, I was not here at that point. I am trying to reach out to the consultant that was used to change the replication from transactional to snapshot, but so far I have not been able to get the details. About all I know at this point is that it used to use transactional replication and they had a bunch of problems so a consultant came in an changed it to snapshot replication. From the information that I have gathered it was an issue with the application and had something to do with the way the application stores printer information.So I need to take time to gather more information and test transactional replication in lab before I am going to try to switch production to transactional replication. In the meantime I am trying to keep the snapshot replication going. |
|
|
|
ericmaibach
Starting Member
4 Posts |
Posted - 2008-09-24 : 11:23:47
|
| Yesterday I was able to get more information about the issues they encountered with transactional replication. Here is a list of them: - When they installed updates to the application the update sometimes drops items and then recreates them, this sometimes creates problems with the replication. To me this does not seem like a huge deal since we don't install application updates real often.- There are some cases when the application does something that effects tens of thousands of rows. It executes as a singleton update on the published database, but gets propagated to the report server as ten thousand single row update statements. These large amount of updates can take a very long time to process.- The application has some tables with no PK defined.Are these issues that are easily worked around, or would I be better off to use log shipping? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-09-24 : 13:15:21
|
| You can't Transactional Replication Without Primary Key. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Snapshot Replication Sometimes Taking Much Longer