Addtion of Table in merge replication

Hi,
I am dealing with merge replication (104 tables/database).
I need to add some new tables in replication. There are two options
1. Stop the replication and publish all tables including new tables then
again set the replication.
2. Create a new subscription for these new tables alone
Advise me, Which one is better?
Thanks,
Soura.
SouRa,
you can just add the new table to the existing publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This will cause an complete snapshot to be generated and distributed. A new
publication might be the answer.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uCxBq%23hsFHA.1168@.TK2MSFTNGP11.phx.gbl...
> SouRa,
> you can just add the new table to the existing publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||AFAIR, although the new snapshot will include all tables, only the new table
will get distributed.
Cheers,
Paul
|||Hi,
I think u can add additional articles by using the System SP
sp_addmergearticle .
After you've added it, you run the snapshot agent. This will generate a
complete snapshot but only the new article will be propagated by the merge
agent.
If ur having the MR option as NotSync then have this table exists in
Subscriber also.
Once u tested in test server, u can implement in production.
regards,
Herbert
"SouRa" wrote:

> Hi,
> I am dealing with merge replication (104 tables/database).
> I need to add some new tables in replication. There are two options
> 1. Stop the replication and publish all tables including new tables then
> again set the replication.
> 2. Create a new subscription for these new tables alone
> Advise me, Which one is better?
> Thanks,
> Soura.
>

Addition of article in Runnung MERGE Replication

Can any help me addition of article in runnning merge replication in MSSQL SERVER 2000 with SP3.
Any step by step help is realy great for me.
Thanks
R.MallSp_addMergeArticle is one system procedure, which may be workable, but I don't know detail step by step methods for same.

Adding table to merge replication - What is result of Snapshot?

I would like to add a table to an existing merge replication. The DB is
approximately 11GB in size. When I proceed through the steps to add the
table using Enterprise Manager, SQL Server 2000 responds as follows:
"After adding a new merge article, you must generate a new snapshot before
changes from any subscription can be merged."
"Although a snapshot of all articles must be generated, only the snapshot of
the new article will be used to synchronize existing subscriptions."
I remember generating an new snapshot for this DB... it took a Loooonnnnggg
time. When SQL2000 says it must generate a snapshot, does it mean for the
entire DB or just the new article?
I thought one of the features of SQL Server 2000 was the ability to add
articles "on the fly" without disturbing what exists.? It is NOT good if I
have to, first, generate a snapshot of the ENTIRE DB... AGAIN! This is not
the way I understand the documentation.
Someone please clarify.?
Brent
brent.erb@.philips.com
IIRC, just a snapshot for the new article will be generated, and distributed
along with metadata for this article.
"Berb" <berb1969@.earthlink.net> wrote in message
news:826193B1-ED96-4D11-B66D-81C4E17D80CF@.microsoft.com...
>I would like to add a table to an existing merge replication. The DB is
> approximately 11GB in size. When I proceed through the steps to add the
> table using Enterprise Manager, SQL Server 2000 responds as follows:
> "After adding a new merge article, you must generate a new snapshot before
> changes from any subscription can be merged."
> "Although a snapshot of all articles must be generated, only the snapshot
> of
> the new article will be used to synchronize existing subscriptions."
> I remember generating an new snapshot for this DB... it took a
> Loooonnnnggg
> time. When SQL2000 says it must generate a snapshot, does it mean for the
> entire DB or just the new article?
> I thought one of the features of SQL Server 2000 was the ability to add
> articles "on the fly" without disturbing what exists.? It is NOT good if
> I
> have to, first, generate a snapshot of the ENTIRE DB... AGAIN! This is
> not
> the way I understand the documentation.
> Someone please clarify.?
>
> Brent
> brent.erb@.philips.com
>

Adding new subscribers to a publisher

We are using Merge Replication on SQL 2000
The snapshot on our publisher has a 180 day retention period. We are able
to add new subscribers just fine within that 180 days, but it seems that come
day 181 when we add a new subscriber it is missing a bunch of schema
information that it should be getting from the snapshot.
All the MS documentation on the retention setting points to keeping current
subscribers active and says nothing about are correlation to new subscribers.
I was wondering if there is relation between the retention setting and when
you can add new subscribers. Does anyone know? Thanks!
I got some more info yesterday from one of the logs. Turns out that the
snapshot is being marked as obsolete. From what I've read the only way a
snapshot can be marked as obsolete is by running one of the following:
sp_addmergearticle
sp_changemergearticle
sp_mergecleanupmetadata
We have not run any of these stored procedures, nor have we modified the
schema in any other way. So at this point it looks like the snapshot is
being marked as obsolete as soon as the retention period has passed. Does
this sound right?
"Paul Ibison" wrote:

> Ben,
> are you saying that a new snapshot doesn't have the same schema as the
> existing subscribers? What things are not being snapshotted, and is the
> snapshot definitely recent?
> Cheers,
> Paul Ibison
>
>
|||I just want to make sure I'm understanding this right. So if our retention
period is set to the default of 14 days and we set up a new snapshot on
7/1/07 then we have to make sure we get all of our subscribers added to the
publisher by 7/14/07 since we cannot add any new subscribers after 7/15/07.
Is this right?
The problem is that we will continuously be adding new subscribers one week
from now, one month from now and a couple years from now. So is our only
option then to lengthen the retention period to something like 1800 days
(around 5 years) and deal with the fact that our database will be storing a
ton of metadata?
Thanks,
Ben
"Paul Ibison" wrote:

> This sounds fine to me - once the retention period is reached, the required
> changes aren't there to make the snapshot data upto date.
> Cheers,
> Paul Ibison
>
>
|||Thanks Paul,
Our experience with running the snapshot agent is that all current
subscribers have to be resyncronized which with the publisher which is a lot
of work when you have a bunch of subscribers. Maybe we are not utilizing
something that would allow this to task to be less of a headache. If you
know of any documentation that may help us in this area it would be much
appreciated. Thanks!
-Ben Fyvie
"Paul Ibison" wrote:

> Correct ... or run the snapshot agent more frequently.
> HTH,
> Paul Ibison
>
>

Adding new index to existing table in Merge Replication

I see from other posts that sp_addscriptexec is recommended for
updating/creating indexes. It isn't clear to me if I can use Enterprise
Manager to go into the table on the publisher and add additional indexes on
a table and then have the added indexes automatically populate to the
subscribers.
Additionally, BOL says that an index can contain upto 16 fields but they
recommend upto three. Does that mean I should create many indexes with only
upto three fields in each index? Help or references on this matter would
be greatly appreciated.
WB
The index can be added at the publisher as per usual. It won't get
automatically propagated to the subscribers and that is what
sp_addscriptexec is for. If I just have one or two subscribers then I tend
to create the index by hand, but if you have lots, then it is a way or being
sure they all receive the changes, especially if they are pull
subscriptions.
As for the size, this seems a general question to do with indexes. Have a
look for clustered, nonclustered and covering in BOL. Indexes are sorthed on
the first column, then for duplicates of the first column they are sorted on
the second and so on, so 2 indexes with 3 columns is not the same as one
index with 6 columns. An index with 6 columns will be 'wide' and therefore
slow, but on the other hand it might have been created as a covering index
for a particluar long-running query. This is a really big subject and I'm
sure there are loads of resources on it apart from BOL.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding New Column & updating bulk data in merge replication

Hi,
We have about 50 databases (SQL Server 2000, SP3) which are merge
replicated. We merge replicate about 100 odd tables in each of these
database. We need to add couple of columns in one of our major transaction
table where most insert/updates are being done. This table presently on
average has 5 lacs records.
During testing, we noticed that it takes about 60-80 minutes to add a
column in this table. Considering the # of database we have where the change
need to implemented, we will not be able to plan the upgrade without
production downtime. For upgrade 50 database it will take about 50 hours.
What are the options available in Replication so this can be done quickly
w/o any production downtime.
Adding to this, in one of the column we have added to the transaction table
, we need to update a new value. On testing we found that for 5 lacs records
it takes anyway between 2-3 hours. This takes roughly another 75 hours for
us to do this update after adding the new column in the table. How can this
be speeded up ?
thanks,
Soura
What's a lacs?
Basically there is probably no good solution for this. I would look at doing
a sync. Then creating my publication and then backing it up and restoring it
to all the subscribers and doing a no sync subscription, or I would look at
regenerating a snapshot and distributing it after you have made the column
change.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:A19172BF-E8AC-4BB7-9FED-87EEC5353D89@.microsoft.com...
> Hi,
> We have about 50 databases (SQL Server 2000, SP3) which are merge
> replicated. We merge replicate about 100 odd tables in each of these
> database. We need to add couple of columns in one of our major transaction
> table where most insert/updates are being done. This table presently on
> average has 5 lacs records.
> During testing, we noticed that it takes about 60-80 minutes to add a
> column in this table. Considering the # of database we have where the
> change
> need to implemented, we will not be able to plan the upgrade without
> production downtime. For upgrade 50 database it will take about 50 hours.
> What are the options available in Replication so this can be done quickly
> w/o any production downtime.
> Adding to this, in one of the column we have added to the transaction
> table
> , we need to update a new value. On testing we found that for 5 lacs
> records
> it takes anyway between 2-3 hours. This takes roughly another 75 hours for
> us to do this update after adding the new column in the table. How can
> this
> be speeded up ?
> thanks,
> Soura
|||5 lacs is 500 K or 500 thousand i.e 500,000
lacs is primarily an indian unit of measurment. 1 lac is 0.1 million
"Hilary Cotter" wrote:

> What's a lacs?
> Basically there is probably no good solution for this. I would look at doing
> a sync. Then creating my publication and then backing it up and restoring it
> to all the subscribers and doing a no sync subscription, or I would look at
> regenerating a snapshot and distributing it after you have made the column
> change.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:A19172BF-E8AC-4BB7-9FED-87EEC5353D89@.microsoft.com...
>
>

Adding more keys in a table

Hi All,
I'm running merge replication.
I want to add some keys for existing fields in a replicated table (composite
keys). Is it possible? How to do that?
Thanks
use sp_repladdcolumn for this. When I hear the term composite keys I think
a primary key consisting of more than one column. Replication normally
complains when you change a PK.
"echo" <echo@.discussions.microsoft.com> wrote in message
news:462ECC14-6DF7-4D12-9B46-3744B388B563@.microsoft.com...
> Hi All,
> I'm running merge replication.
> I want to add some keys for existing fields in a replicated table
(composite
> keys). Is it possible? How to do that?
> Thanks
|||Hi Hilary,
That's why. The column is already exist. I just want to change/modify it as
(another) primary key.
Should I do create dummy column --> copy the contents --> drop the column
--> create the new one --> copy from dummy?
The replication runs in continues mode. Will it stops immediately when I do
these changes? What should I do next?
TIA
Echo
btw do you have merge replication book? how can I get it? thanks )
"Hilary Cotter" wrote:

> use sp_repladdcolumn for this. When I hear the term composite keys I think
> a primary key consisting of more than one column. Replication normally
> complains when you change a PK.
> "echo" <echo@.discussions.microsoft.com> wrote in message
> news:462ECC14-6DF7-4D12-9B46-3744B388B563@.microsoft.com...
> (composite
>
>

Adding column to table

Just wanted to check. Running merge replication on SQL 2000. If I add a
column to a table that is a published article, I assume I just run
sp_repladdcolumn is that correct and is that all I need to do in order for
the laptops to get the change with their next synch? Also, what do I set
the value for @.force_reinit_subscription to? Thanks.
David
Yes. However I believe you need to set @.force_reinit_subscription and
@.force_invalidate_snapshot to 1.
Basically these switches will stop your script from working if they are set
to their defaults of 0. If you set them to 1 your scripts will run but you
may have to reinitialize your subscriptions.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23IOG4PPOHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Just wanted to check. Running merge replication on SQL 2000. If I add a
> column to a table that is a published article, I assume I just run
> sp_repladdcolumn is that correct and is that all I need to do in order for
> the laptops to get the change with their next synch? Also, what do I set
> the value for @.force_reinit_subscription to? Thanks.
> David
>

Adding column to Merge Pub

I have a merge publication (SQL 2k) and needed to add a column to a
replicated table. I used the stored proc and settings below and it seemed
to work fine. When I synched with a subscriber, it seemed to have applied
the table modification first, then downloaded the updates that were on the
publication. I just want to make sure I am doing this correctly before I run
on the production database. Thanks.
David
exec sp_repladdcolumn @.source_object = 'ConfigFA'
, @.column = 'NextCaseNumber'
, @.typetext = 'int NOT NULL DEFAULT 1'
, @.publication_to_add = 'MCFIDataPub'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 1
That is exactly right, it has to do the table modification first to pull the
data back to the publisher and then merge it there. The data on the
publisher will already have the changed format.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:u163fB4HGHA.2680@.TK2MSFTNGP09.phx.gbl...
>I have a merge publication (SQL 2k) and needed to add a column to a
>replicated table. I used the stored proc and settings below and it seemed
>to work fine. When I synched with a subscriber, it seemed to have applied
>the table modification first, then downloaded the updates that were on the
>publication. I just want to make sure I am doing this correctly before I
>run on the production database. Thanks.
> David
> exec sp_repladdcolumn @.source_object = 'ConfigFA'
> , @.column = 'NextCaseNumber'
> , @.typetext = 'int NOT NULL DEFAULT 1'
> , @.publication_to_add = 'MCFIDataPub'
> , @.force_invalidate_snapshot = 0
> , @.force_reinit_subscription = 1
>

Adding column in table in repliation

Dear Friends
I want to add a column to the table which is in the
replication please suggest how i can achive the same.
I am using Merge Replication.
Best regards
Sharad
Sharad,
have a look in BOL for sp_repladdcolumn - this is the procedure you'll need.
Regards,
Paul Ibison
|||This script will require reinitialization - better to use sp_repladdcolumn.
Regards,
Paul Ibison

adding article in merge replication

Hi,
our configuration is SQL 2000 (publisher and subscribers as well), merge
replication, pull subscription.
From time to time we add new subscriber.
I need to add new table or stored procedure into current replication
schema. What to do exactly if I want to avoid reinit with total snapshot
transfer for existing subscriptions and any new subscription should be
created with new articles already?
Is following sequence correct?
EXEC sp_addmergearticle
@.publication = @.our_publication,
@.article = N'newarticle',
... @.force_invalidate_snapshot = 1
EXEC sp_refreshsubscriptions
@.publication = @.our_publication
Thanks for an answer or suggestion.
Lenka
Adding a new merge article to the publication will result in an entire
snapshot being created but NOT a reinitialization. Just the new article will
be transferred. Once the article has been added to the publication, you'll
need to also run the snapshot agent to have it propagated.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Adding an article in merge replication

Hi
I want to add an article to an existing publication using merge replication,
the database is around 5 Gb. Using sp_addmergearticle defaults to invalidate
the snapshot and a new one has to be created. When the new snapshot is
applied to the subscriber is there anyway of just applying just the new
article and its data and nothing else. Setting the pre_creation_command to
'none' only seems to ignore the creation of tables and not the bcp of data
into them.
many thanks
Hi Rob,
When you add an incremental article by specifying @.force_invalidate_snapshot
and generate the snapshot, the snapshot is generated for all the articles.
However when you apply the snapshot at the subscriber, it will apply *only*
the incremental article schema files and data. It will *not* be applying the
snapshot for all the other articles, unless you are reinitializing the
subscriber.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"robham" <nospam@.co.uk> wrote in message
news:%235fLYMIrEHA.4008@.TK2MSFTNGP14.phx.gbl...
> Hi
> I want to add an article to an existing publication using merge
replication,
> the database is around 5 Gb. Using sp_addmergearticle defaults to
invalidate
> the snapshot and a new one has to be created. When the new snapshot is
> applied to the subscriber is there anyway of just applying just the new
> article and its data and nothing else. Setting the pre_creation_command to
> 'none' only seems to ignore the creation of tables and not the bcp of data
> into them.
> many thanks
>

Adding an additional Subscription to Merge Replication

I have two servers in a merge replication setup (linked every night using an ISDN line). I wish to add a new subscriber to this but I am not sure if I need to do a new snapshot.
I was thinking of using the latest backup from the publisher and recover this on the new subscriber - if I do this will i need
to run the snapshot again ? or will they all simply carry on with the merge...
Thanks
Gerry
you have to create a no-sync subcription which will run the snapshot only
for necessary merge replication metadata.
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:3490FE65-DF3F-401D-A6EE-78236E508677@.microsoft.com...
> I have two servers in a merge replication setup (linked every night using
an ISDN line). I wish to add a new subscriber to this but I am not sure if
I need to do a new snapshot.
> I was thinking of using the latest backup from the publisher and recover
this on the new subscriber - if I do this will i need
> to run the snapshot again ? or will they all simply carry on with the
merge...
> Thanks
> Gerry
|||How do I create/define a no-sync subscription ? Does it get defined when I run the
Push Subscription Wizard in the usual way?
Thanks
Gerry
|||You have to select an option that no data needs to be synchronized
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Gerry" <anonymous@.discussions.microsoft.com> wrote in message
news:0709ECB3-8396-4593-A423-93761B4137EE@.microsoft.com...
> How do I create/define a no-sync subscription ? Does it get defined when I
run the
> Push Subscription Wizard in the usual way?
> Thanks
> Gerry
>