Mark,
unless there's some data overlap, I wouldn't recommend merge. Transactional
and bidirectional-transactional would be my preference. Much faster for
synchronization this way. For the snapshot I used to replicate a table
having several hundred million rows so can understand this issue. The
compression of alternative snapshot files goes up to 2GB CAB files and
instead of this I used Winzip 9.0 to compress the files manually, FTPd them
to the subscribers, unzipped then synchronized using the alternative
snapshot location. The time taken to compress the files is small compared to
the electronic transfer, so it's well worth while using this extra step.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Mark,

this won't be maintenance free, especially as you're doing schema changes
using bi-directional. Bi-directional replication is more of a manual setup
than the other types but offers better performance. If this (performance)
isn't an issue, them merge would be more 'out-of-the-box' and
straightforward. In this case new tables and stored procedures are not an
issue, as they can be added to an existing publication. Changing existing
schemas (eg increasing a varchar length) can be a pain, but this is vastly
improved in SQL Server 2005.

For validation, you need to set up alerts to fire if the synchronization
process fails, but this is simple.

Once it's set up and you've covered the bases - eg identity range management
hasn't been forgotten - then usually you can leave it alone. Still, a look
at this newsgroup will show that it's not always so easy :) EG if someone
uses DTS to import some data into one of your merge-replicated tables, the
records won't propagate to the subscriber, because the transform data task
uses a bulk insert which doesn't fire the triggers that merge replication
relies on. These little caveats make life more 'interesting' overallfor the
DBA.

Cheers,

Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)


[quoted text, click to view]

I've worked with SQL Server since 1996, bit I'm just now having to venture
into the replication arena. Here's my situation...

I have a database at our corporate office - about 1.5 GB in size spread
across 100 tables and 450 stored procedures. The largest table right now
has about 600,000 records, but there are probably another 20 tables that
have over 100,000 records. I need to replicate this database to 8 branch
offices that are connected via VPN DSL connections (144KB IDSL on the
low-end, 3MB up/512 KB down on the high-end). Although the DSL connections
are fairly reliable, they do go down occasionally. There is no need to
real-time replication - if there is a delay of 5, 10, or 15 minutes (or
longer), it's not a major concern.

80% of the data is updated at our corporate and needs to be pushed to the
branches. 20% of the data is updated at the branch offices and needs to be
transmitted to the corporate office. There is no chance of data conflicts,
as the local branch offices can't update the data received from corporate,
and the corporate office can't update the data received from the branches.
Every table has been designed with a rowguid as its primary key (with a
non-clustered index, of course). I wish SQL Server didn't default the PK
index to clustered, but that's another rant. :-)

At first, I was first looking at transactional replication. but merge
replication is starting to look it might be a better fit (or maybe some
combination of both).

I'm also a bit concerned with getting the initial snapshot going at each
branch. Obviously, copying a 1.5 GB snapshot over a DSL connection is going
to take a while. Would I be better off overnighting a DVD or backup tape
containing the snapshot?

I appreciate any thoughts or recommendations!

- Mark

Thanks for the insight. There's absolutely no chance of data conflicts
given the application design, although there are quite a few tables that
would need bi-directional transactional replication (most tables have a
'BranchID' column that identifies the "owning" office).

Never having worked with replication before, what kind of maintenance
headache am I looking at? There are periodic updates to the various
applications that use the database, requiring occasional schema changes, new
tables, stored procedures, etc. How big a problem will these changes cause?
I want to avoid having to reinitialize the branch databases at all costs.
The latency time for updates between branches and corporate is irrelevant,
but at the local branch office level, if the database is offline for any
extended period of time because it's having to re-sync - well, let's just
say I'll catch an earful. Also, how reliable is replication? Is it pretty
much fire-and-forget, or does it fairly constant monitoring?

Thanks again for the input.

- Mark

[quoted text, click to view]

Replication performance isn't a huge issue for me - it could take half a day
or more for the data to replicate, and that'd be fine with me. If
replication affects application performance (normal CRUD-type applications),
I'm a bit more interested in what to look for. I also don't need to worry
about rogue users running DTS packages - heck, they can't even spell SQL.
I'd only have to watch out for myself :-) Identity range management
shouldn't be an issue, since I don't use identity fields anywhere.

Number one concern is future flexibility as the application and database
mature over time. Number two concern is ease of setup and maintenance. As
you mentioned, a quick glance at this newgroup shows things don't always go
as planned. I don't mind tweaking things when needed - just don't want it
to turn into a full-time job.

- Mark

[quoted text, click to view]