December 21, 2010 at 8:23 am
Greetings all,
The reinitialization of our transaction replication takes over 14 hours and is a process that is avoided if possible by the business. Can anyone share some experiences that forced reinitialization, for instance: (changing the length of a column, or an improper table alters)? Or from the opposite direction can someone share precautions observed to avoid the necessity of reinitializing?
Thanks all,
DK
December 21, 2010 at 8:35 am
Once you have replication configured and your initial snapshot delivered there is no reason that you should HAVE to reinitialize again. Granted it may be necessary as a best option but you can pretty much do anything that I can think of without causing a reinitialization including altering articles and their columns, dropping and adding articles, dropping and adding subscriptions.
The ways to go about this vary depending on what actions you are looking to perform so it would be best if you could give me an overview of what you are trying to accomplish and I can try to provide some overview of what steps may be necessary. Ultimately, replication = creative environment changes. It has its challenges but those can all be great to get you to think outside the box on how to get things done.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 21, 2010 at 11:51 am
David,
Thanks for the response, and I apologize for the piecemeal nature of my forum posts, as I have been tasked with improving the current process but was not involved in its creation. We are replicating a production database to a reporting server for two reasons. One, internal departments are given read access to the reporting server version of the database for daily work functions. Two, the production web app uses the reporting server version of the database to serve up ssrs reports within the app to customers logged on. The database is around 150 GB and they have had issues in the past where they have felt like they had no other option but to reinitialize replication. In that case the reinitialize took over 14 hours which was unacceptable to the business. I am tasked with cutting down the amount of time it would take to reinitialize replication, or prevent the need to reinitialize. I have identified some possible bottlenecks:
32bit publisher to 64bit subscriber.
100 Mbps network card on the subscriber.
Database files i.e. mdfs and ldfs on the same drive as the replication shared folder.
Distribution database on C: drive with only a few gigs of free space.
And then after your last post, it dawned on me to just do two different replication publications one for internal departments and one for the prod web app.
Thanks for your help,
DK
December 21, 2010 at 12:00 pm
Not sure that I helped in this case but.... 🙂
If you do think of a reason that you have to reinitialize give a shout - prior to starting the work / change of course. I'm fairly certain we can come up with a solution that you can avoid the change forcing a reinitialization.
Hope your solution works out for you.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
December 23, 2010 at 9:36 am
if you have the same indexes on the subscribers as the publisher, start running the index DMV's to check for unused indexes and delete them. it will reduce your I/O and reduce hiccups where replication seems to stop
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply