Merge replication when new tables are created regularly

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Maani - 29 Jan 2007 16:29 GMT

Hi all,

I am trying to replicate a database (sql server 2000) to a remote site. I
use merge replication and do almost continous replication.
My problem is I have one application which creates atleast 6 or 7 new tables
a day. Everytime it create a tables, snapshot agent restarts again and this
makes the entire server slow. Also when doing this snapshot agent fails most
often!
How can I get around this issue. Anybody with insight to this issue, plz
help me..
Regards,
Maani

Reply to this Message

Paul Ibison - 29 Jan 2007 17:20 GMT

The problem with the snapshot agent on merge publications is that it
snapshots the entire publication, even when only one article is added, and
there isn't an option of a concurrent snapshot unlike transactional. This is
probably causing the snapshot errors you are getting. It's not always very
practical, but you could potentially add the new tables to a new
publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Reply to this Message

David - 29 Jan 2007 19:33 GMT

Sounds to me like a design problem in the application. At that rate, you
would be adding approx. 2,500 tables a year! I'd hate to administer that
database.

David

> Hi all,
>
[quoted text clipped - 11 lines]
> Regards,
> Maani

Reply to this Message

Maani - 30 Jan 2007 09:13 GMT

So.. What should I do?
create a new publication periodically and and all newly created tables
should be added on to newest publication? How can i do that then? Somebody
plz help me with the scripts please as I am not professional DBA... :-)

> Sounds to me like a design problem in the application. At that rate, you
> would be adding approx. 2,500 tables a year! I'd hate to administer that
[quoted text clipped - 16 lines]
> > Regards,
> > Maani

Reply to this Message