sql server replication: Hello, I'm wondering what type of replication (if any) is best suited for a
publishing database that has a tendency to change schemas rather often. I'm
under the impression merge is best for me, but I'm not totally sure. I'm not
talking huge changes... just things like adding columns, tables, changing
data types, indicies etc.

As a general question, does anything change for a developer once you start
using replication? For instance, would the way I normally modify table be
different after I started replicating to subscribers?

To add a column, you can use sp_repladdcolumn and to
remove, sp_repldropcolumn, and have a look at this
article for datatype changes:
http://www.replicationanswers.com/AddColumn.asp.
However, if your changes to the schema are often and
involve datatype changes, you might be better off
reinitializing, in which case this shouldn't affect the
type of replication you'll implement.

BTW, in SQL Server 2005 the Alter Table statement is
allowed on replicated tables within the context of
replication, so things become much easier.

As for changes to the developer side of things, this are
some simple comments off the top of my head: merge
replication and replication with updating subscribers
will generally add a guid column. In the case of merge it
might not if there is one already there with the rowguid
attribute. Apart from that, the trigger firing order can
be important in certain replication types, as again merge
and updating subscribers will add triggers to the
replicated table. Transactional replication will not
itselt change the publisher's tables, but there is a
schema requirement - the published table must have a PK,
so this might change your code. Finally, if your code
expects to work on the subscriber in exactly the same way
as the publisher, this might not work as in some cases
the schema is subtly altered, eg PKs become unique
indexes.

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

[quoted text, click to view]
Can you explain this in more detail? I don't follow how reinitializing
allows for the schema and datatype changes.

WB

It doesn't :) Basically what I'm thinking is that the
process to change a datatype is longwinded, in terms of
the processing requirements, and there comes a point when
it would be less work to simply reinitialize. Perhaps
this point is 2 datatype changes - need to check?
Rgds,
Paul Ibison