Replication without snapshot
-
I everybody,
This is my background:
A SQL database of 2Gb (server 1) clustered runs as publisher.
A second server (server 2) runs as Distributor and local Subscriber.
A first transactional job sends a complete publishing from server 1 to server 2.
From Distributor (server 2) tree different partial distributions are sent to external subscribers (server 3, 4, 5) and one complete to itself.
This model works already fine.
My problem is every month I get new version of this database.
Every time the table structure is modified (add, modify or delete table)
I have to break my replication model, recreate structure and both snapshot.
One snapshot takes 45 minutes and I only have one hour of outage…
I already write a script to look for modification structure on publisher and to add as create, update, delete SP.
However synchronization agent doesn’t want to start before snapshot is created.
Anybody have a trip to pass up this stupid snapshot?
Thank's
-
Running SQL7 or SQL2K? Just curious.
One easy way is to break the publication up, either into one pub per table or into groups. Big tables by themselves, smaller tables in a group. That way you don't have to snapshot everything. You might also look at how to reduce the time it takes to snapshot. Faster connection, better drives, changing index pattern on subscriber, etc.
Options are either snapshot or restore from backup. You'd have to restore it to a temporary db, then pull over into your subscriber db. Not impossible but a fair amount of work.
SQL2K lets you add/drop columns without snapshotting, so that might help in some cases.
I know that doesnt quite answer the question, but maybe will give you some ideas. Hopefully someone else will have a better answer!
Andy
-
Thank you Andy.
We just transfert our Sql7 database to Sql2000.
I will test and tell you if this version accept add/delete table/collums without snapshot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply