We want to have a three stage production environment:

1. Test Server
2. Preproduction - a server for show clients stuff before putting it live
3. Production

Ideally we would like the data on Preproduction and Test to be very close to
the live. Replication seems the right tool for this. How does replication
deal with differences in data structure. Our preproduction server will have
additional columns and tables while it is demonstrating new stuff. What is
the best way to deal with this. Say Table A on the live server has 4 columns
and Table B on the preproduction has 5 and we do snap shot replication and
the 4 columns are replicated. When we add the additional column on the live
server can it automatically "map" the fifth column or does it have to be
explicitly told. Regards, Chris.

Chris,
I would probably use snapshot replication for this. The data on the test
server can be manipulated as you like and there isn't an overhead of the log
reader agent on the production kit (and distribution agent for push, or at
least disk access to write the transactions to the distribution database).
You can refresh the data every so often, and new columns are pretty
straightforward to add to the published articles. Until these columns are
added onto the production server, you can have a post-snapshot script to add
them to the subscriber.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Does that mean I have to manually add the published columns from the
production server to the preproduction. Is there an automated way of doing
this, e.g. say I want the entire table, rather than specifying each
individual column.

[quoted text, click to view]

All the published columns would be in the replicated article - just the
additional testing ones would be added in the post-snapshot script. The
replication script to create the publication would just add the table as an
article and so get all the available columns from the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com