replication and schema change

Hello everyone!
We are software developement company. We are planning to setup replication
for few our customers. But our software is still under developement and
database schema is changing quite often.
What is the best, fastest and easiest way to implement this database chnages
at our "replication" customers?
Uros
Uros,
sp_repladdcolumn and sp_repldropcolumn cater for a lot of schema changes. To
alter a column take a look at this article:
http://www.replicationanswers.com/AddColumn.asp. On SQL Server 2005 things
are easier: http://www.replicationanswers.com/AlterSchema2005.asp.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||An alternative method which allows for more than just adding or removing 1
column at a time...
--Drop Subscription & Article
exec sp_dropsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
exec sp_droparticle @.publication = 'fxDB6_Pub1'
, @.article = 'MyTable'
-- Make DDL changes
ALTER TABLE MyTable
ALTER COLUMN...
-- Add article
exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
@.source_owner = N'dbo'
, @.source_object = N'MyTable', @.destination_table = N'MyTable', @.type =
N'logbased', @.creation_script = null
, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16
, @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_MyTable',
@.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
@.filter = null
, @.sync_object = null, @.auto_identity_range = N'false'
-- Add Subscription(s)
exec sp_addsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
, @.destination_db = 'SubscrDBName'
, @.sync_type = 'automatic'
-- Start Snapshot agent - creates snapshot only for MyTable article
-- Distribution agents will ship to subscribers
Another solution for changes to many tables:
- drop all subscriptions
- drop publication
- make changes
- re-create publication, with script of course (ui takes too long w/ many
tables)
- add subscriptions
- fire up agents...
Regards,
ChrisB
www.MyDatabaseAdmin.com
"uros" wrote:

> Hello everyone!
> We are software developement company. We are planning to setup replication
> for few our customers. But our software is still under developement and
> database schema is changing quite often.
> What is the best, fastest and easiest way to implement this database chnages
> at our "replication" customers?
> Uros
|||Chris - Good suggestion. However, "@.schema_option =
0x00000000000000F3" can be a different value depending on the options one
may have chosen through the interface during the first attempt for the setup.
-A
"Chris" wrote:
[vbcol=seagreen]
> An alternative method which allows for more than just adding or removing 1
> column at a time...
> --Drop Subscription & Article
> exec sp_dropsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> exec sp_droparticle @.publication = 'fxDB6_Pub1'
> , @.article = 'MyTable'
> -- Make DDL changes
> ALTER TABLE MyTable
> ALTER COLUMN...
> -- Add article
> exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
> @.source_owner = N'dbo'
> , @.source_object = N'MyTable', @.destination_table = N'MyTable', @.type =
> N'logbased', @.creation_script = null
> , @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x00000000000000F3, @.status = 16
> , @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_MyTable',
> @.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
> @.filter = null
> , @.sync_object = null, @.auto_identity_range = N'false'
> -- Add Subscription(s)
> exec sp_addsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> , @.destination_db = 'SubscrDBName'
> , @.sync_type = 'automatic'
> -- Start Snapshot agent - creates snapshot only for MyTable article
> -- Distribution agents will ship to subscribers
> Another solution for changes to many tables:
> - drop all subscriptions
> - drop publication
> - make changes
> - re-create publication, with script of course (ui takes too long w/ many
> tables)
> - add subscriptions
> - fire up agents...
> Regards,
> ChrisB
> www.MyDatabaseAdmin.com
> "uros" wrote: