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:

Replication adn tempdb log file

I am relatively new to SQL Replication and am trying to configure replication
for some SQL databases for a disaster recovery project. I have several
databases ranging from 100MB to 5.5GB that I want to replicate to another
server offsite. I am starting with a small database. I go through the
Replication Wizard and it adds the articles. Then it sits on the create
publication screen forever and finally I get an error that says the tempdb
log file is full. I notice that the log file is over 10GB in size. Why is
that for a 100MB database? I have 17GB available on the server. How can I
create a publication for a database without filling up the tempdb log file?
If it uses over 10GB for a 100MB database what is going to happen when I
replicate a 5.5GB database?
Bill - such useage of tempdb is very odd - is it possible there is another
process occurring concurrently which fills the tempdb? Can you remove the
inactive entries to see if this helps:
backup log tempdb with no_log
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)