Merge changes

I just want to verify that my plan for making changes to SQL 2000 merge
publication and to ask some basic questions. I need to add a new table,
modify a view, add a column to a table in existing articles and uncheck the
"Enforce relationships for replication" on several tables due to it causing
problems with synchs. Here is my plan.
1. Make all changes in EM (if possible).
2. Create new publications and subscriptions.
3. Have laptops synchronize.
Can I just use the current laptops windows synch manager to have them
recognize and replace their current subscriptions?
Is there any other things I need to be concerned with?
Thanks.
David
David,
if you are wanting to initialize these subscribers, you'll have to
synchronize (to get all the data to the publisher) and then drop the
subscriptions first. After that you make the changes and then add the
subscriptions and run the snapshot agent.
All of these changes could be done without reinitializing in this way
though. Adding a new table is straightforward for an existing publication.
Adding a column is done through sp_repladdcolumn. Changing the FK
relationships and modifying the view could be done using sp_addscriptexec.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
Are you saying I won't need to drop and recreate the subscriptions if I do
all of this in scripts? I have done sp_repladdcolumn and sp_addmergearticle
before but not sp_addscriptexec. How do I get the script for unchecking the
"Enforse relationship for replication"? Also, should the
@.force_invalidate_snapshot be 1? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e$T2JN0LHHA.448@.TK2MSFTNGP04.phx.gbl...
> David,
> if you are wanting to initialize these subscribers, you'll have to
> synchronize (to get all the data to the publisher) and then drop the
> subscriptions first. After that you make the changes and then add the
> subscriptions and run the snapshot agent.
> All of these changes could be done without reinitializing in this way
> though. Adding a new table is straightforward for an existing publication.
> Adding a column is done through sp_repladdcolumn. Changing the FK
> relationships and modifying the view could be done using sp_addscriptexec.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||David - yes - this way should be able to avoid the reinitialization. There
is no easy script for the NFR setting - you'd have to drop the Fk constraint
and readd in script eg
ALTER TABLE dbo.Table_2
DROP CONSTRAINT FK_Table_2_Table_1
GO
ALTER TABLE dbo.Table_2 WITH NOCHECK ADD CONSTRAINT
FK_Table_2_Table_1 FOREIGN KEY
(
id
) REFERENCES dbo.Table_1
(
id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
NOT FOR REPLICATION
GO
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||What about the setting for @.force_invalidate_snapshot on the others?
Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OPDUyx0LHHA.140@.TK2MSFTNGP04.phx.gbl...
> David - yes - this way should be able to avoid the reinitialization. There
> is no easy script for the NFR setting - you'd have to drop the Fk
> constraint and readd in script eg
> ALTER TABLE dbo.Table_2
> DROP CONSTRAINT FK_Table_2_Table_1
> GO
> ALTER TABLE dbo.Table_2 WITH NOCHECK ADD CONSTRAINT
> FK_Table_2_Table_1 FOREIGN KEY
> (
> id
> ) REFERENCES dbo.Table_1
> (
> id
> ) ON UPDATE NO ACTION
> ON DELETE NO ACTION
> NOT FOR REPLICATION
> GO
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Paul,
Also, will I need to run sp_addmergearticle for the new table and view? I
thought there was a checkbox option to make it repl if you added it in EM.
Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OPDUyx0LHHA.140@.TK2MSFTNGP04.phx.gbl...
> David - yes - this way should be able to avoid the reinitialization. There
> is no easy script for the NFR setting - you'd have to drop the Fk
> constraint and readd in script eg
> ALTER TABLE dbo.Table_2
> DROP CONSTRAINT FK_Table_2_Table_1
> GO
> ALTER TABLE dbo.Table_2 WITH NOCHECK ADD CONSTRAINT
> FK_Table_2_Table_1 FOREIGN KEY
> (
> id
> ) REFERENCES dbo.Table_1
> (
> id
> ) ON UPDATE NO ACTION
> ON DELETE NO ACTION
> NOT FOR REPLICATION
> GO
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||David - the existing snapshot will become invalid for any other new
subscribers so set this to 1.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||In EM you can select the checkbox to "Show unpublished objects" then select
the new table to have it added. A snapshot will need to be generated but
only the new table will be taken.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
One of the changes is a scripting of a new trigger on a frequently used
table and I prefer to do this during the day when users are in it (approx.
20 users). Will this be a problem? and if so, are there any ways around
this? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OJjQpD1LHHA.3424@.TK2MSFTNGP02.phx.gbl...
> In EM you can select the checkbox to "Show unpublished objects" then
> select the new table to have it added. A snapshot will need to be
> generated but only the new table will be taken.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||Sp_addscriptexec for this will be running a create trigger script which will
then run immediately after synchronization. Assuming you have > 1
subscriber, there could be a disparity of data and lack of synchronization.
Ideally, you could stay late and run the command interactively once
everyone's off the system. Alternatively I'd set up the sp_addscriptexec
statement as a step in a job that runs once only and schedule it for during
the downtime - you obviously have to be sure that it runs fine in your test
environment for this though.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .