Adding a sucsriber

Assuming that there is an exiting replication in place between ServerA and
ClientA (subscriber) and now I want to add another subscriber ClientB
How do I go about that
Should I just copy the database from the serverA and add a subscription to
ClientB or I have to do something to the Publication on ServerA
Thank you,
Samuel
Just add another subscriber using the wizard or sp_addsubcription. The
snapshot will be sent to Client B automatically.
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
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:OMZb2rZ4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Assuming that there is an exiting replication in place between ServerA and
> ClientA (subscriber) and now I want to add another subscriber ClientB
> How do I go about that
> Should I just copy the database from the serverA and add a subscription to
> ClientB or I have to do something to the Publication on ServerA
> Thank you,
> Samuel
>

Adding a subscriber which already has the schema and data

Hello,
I use a dynamically filtered publication.
I want to add a subscriber that already has the schema and data. So I create
a new subscription and set the option 'No, the Subscriber already has the
schema and data'.
When I start the new merge agent for the first time, it replicates all
historical data changes since the creation of the publication.
I don't want this behaviour, because the subscriber has already updated data.
I want the merge agent to replicate all changes since the creation of the
subscription.
Does someone has ideas?
thanks in advance, Marco
The merge replication process requires this metadata in order to track
changes. If you run a sp_mergemetadataretentioncleanup this could clean up
your metadata on your publisher and all subscriber, so you might be able to
start with metadata replication tables with no row in them.
You need sp 1 and above for this proc.
"Marco Broenink" <MarcoBroenink@.discussions.microsoft.com> wrote in message
news:491BE7CE-2FD2-4885-908B-355460FAF06C@.microsoft.com...
> Hello,
> I use a dynamically filtered publication.
> I want to add a subscriber that already has the schema and data. So I
create
> a new subscription and set the option 'No, the Subscriber already has the
> schema and data'.
> When I start the new merge agent for the first time, it replicates all
> historical data changes since the creation of the publication.
> I don't want this behaviour, because the subscriber has already updated
data.
> I want the merge agent to replicate all changes since the creation of the
> subscription.
> Does someone has ideas?
> thanks in advance, Marco
>
>

adding a stored proc to a publication

Hi

I created a publication (merge anonymous via Web) - the subscriber already had the data, so there was no need to to initialize data and schema.

The problem is when it comes to adding a new stored proc to the publication. I create the proc, add it as an article to the publication and recreate the snapshot ok, but then the synchronisation fails. I presume this is because the stored proc does not exist at the subscriber. I bet it would work if I had initialized the schema and data, but due to the size of the data this is not practical.

If I manually create the proc at the subscriber then it works fine - but this defeats the purpose somewhat.....

Any ideas ?

thanks
BruceSince the subscription was created as no-sync, you will need to manually create the proc on the subscriber. You could create different publications for tables and stored procs and workaround that way. Now you can initialize the subscriber for the publication with the stored proc (and other smaller articles if you wish) and large data publication can stay intact (as no-sync)|||Thanks for the suggestion - a good workaround for now....

adding a new subscriber

Hi
I have merge replication. One client is already subscribed. I need to add
another client to the publication
The database is pretty big, so I don't want to send the whole db over the
internet.
I want to back it up and restore it, then establish the subscription.
Are the steps
a) backup database
b) recreate snapshot
c) restored db at subcriber B
d) establish subscription at subcriber B
I would assume I don't want any changes to the database made from the other
subscriber between steps a) and d)
Is this right. And I hope recreating the snapshot will not 'damage'
subscriber A
Thanks
Bruce
Thanks
Bruce
Bruce,
if you want a pull subscription, the easiest way is to create an Attachable
Subscription Database (details in BOL). If not, then you can backup and
restore and subscribe without initialization - @.sync_type = none.
HTH,
Paul Ibison
|||Bear in mind that if the database is too large the attachable subscription
will not work. The compressed databse is limited to 2 gig in size so a
large database will have problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Adding a Distributor

I have a current merge replication system with a publisher server that is its
own distributor, and four remote subscriber servers. I now want to make the
present publisher/distributor a distibutor, and add a new publisher only
server. What is the easiest method?
Can I avoid having to recreate the whole system and remake the subscribers?
Thanks for advice.
You will have to script out your publications and subscriptions. Do a final
synchronization, drop them, and then migrate to the new publisher.
Then recreate everything.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"MDP" <MDP@.discussions.microsoft.com> wrote in message
news:0578784C-DA1B-40D5-9903-3F7228E79EA5@.microsoft.com...
>I have a current merge replication system with a publisher server that is
>its
> own distributor, and four remote subscriber servers. I now want to make
> the
> present publisher/distributor a distibutor, and add a new publisher only
> server. What is the easiest method?
> Can I avoid having to recreate the whole system and remake the
> subscribers?
> Thanks for advice.

Adding a column to tables in merge replication.

Hi, we need to add a column to the table that is used as the subscriber table
in a merge replication, will this break the replication.
We also need to add a column to the table that is used as the publisher
table in a merge replication, will this also break the replication?
Thanks,
Pingx
You should not touch the subscriber tables - they will be locked and you
will be unable to make modifications to them.
Use sp_repladdcolumn to add the column in SQL 2000. In SQL 2005 is the
replicate_ddl statement is set to true table modifications will be
replicated.
http://www.zetainteractive.com - Shift Happens!
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
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:2E4682B7-BD3D-4765-984F-2EC1A83C3BD0@.microsoft.com...
> Hi, we need to add a column to the table that is used as the subscriber
> table
> in a merge replication, will this break the replication.
> We also need to add a column to the table that is used as the publisher
> table in a merge replication, will this also break the replication?
> Thanks,
> Pingx
|||I used the statement
ALTER TABLE table_name ADD <column> at the publisher.
It got replicated to the subscriber.
Is this OK? do I need to use replicate_ddl statement ?
Thanks.
Pingx
"Hilary Cotter" wrote:

> You should not touch the subscriber tables - they will be locked and you
> will be unable to make modifications to them.
> Use sp_repladdcolumn to add the column in SQL 2000. In SQL 2005 is the
> replicate_ddl statement is set to true table modifications will be
> replicated.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:2E4682B7-BD3D-4765-984F-2EC1A83C3BD0@.microsoft.com...
>
>
|||It appears that you are using SQL 2005 where replicate_ddl is enabled by
default.
http://www.zetainteractive.com - Shift Happens!
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
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:1991127A-39EC-492C-8FBE-EAAB44EE0C3C@.microsoft.com...[vbcol=seagreen]
>I used the statement
> ALTER TABLE table_name ADD <column> at the publisher.
> It got replicated to the subscriber.
> Is this OK? do I need to use replicate_ddl statement ?
> Thanks.
> Pingx
> "Hilary Cotter" wrote:

add subscriber in publication database`

Hi ...i am new to sql server replication, im using the sql server 2005.

I was configure the replication n publish a database , i wanna add a new subscription, but i am facing the follow error

You have selected the Publisher as a Subscriber and entered a subscription database that is the same as the publishing database. Select another subscription database.

How do i add another subscription database,since in my sql server has only 1 subscriber?

You have to manually pre-create the subscription database.|||THanks Greg , but how to muanually pre-create the subscription database?|||In SQL Server Books Online, look up topic "CREATE DATABASE".

Add new table to transactional replication with push subscriber

Hi there,

With SQL Server 2005 after adding a new table to replication, the entire snapshot is redone when initializing the snapshot agent. Ive read that with the pull subsciption there is a proc you can run to only snap the new object. Is there any way (besides creating a new publication) to stop the whole snapshot being redone for a push subscription?

Thanks

It seems there is no this kind of proc to only add snap new object, no matter for pull or push subscription. New added schema in publication will cause subscription to reinitialized , unless you manually add the new added objects into subscriber side and choose @.sync_type = 'none' to skip the initial snapshot.

Hope it will help.

Thanks

Yunjing

|||

Thanks for reply.

but surely this cant be right. in SS2000 only new objcts added were initialized. we have a database with over 40 GB replicated data and cant redo the whole snap every time another article is added.

|||A new snapshot is generated such that any new subscriber that is added can reference an up to date snapshot. The entire snapshot is NOT sent to the subscribers. Only the new objects are sent to each subscriber. So, you only get an incremental initialize when you add new articles to the publication.

Add Article(table) to Trans Repl - Move to Subscriber

How to you add an Article (Table) to a Transaction Replication Publication,
and have it move to the Subscribers without having to re-generate the Whole
Snapshot?
What to I monitor to see this happen and how long to I wait to see it update?
Thanks and God Bless,
ThomBeaux
Thanks and God Bless,
ThomBeaux
"ThomBeaux" wrote:

> How to you add an Article (Table) to a Transaction Replication Publication,
> and have it move to the Subscribers without having to re-generate the Whole
> Snapshot?
> What to I monitor to see this happen and how long to I wait to see it update?
> --
> Thanks and God Bless,
> ThomBeaux
|||Will a new snpahot be created for the whole Publication?
Thanks and God Bless,
ThomBeaux
"Paul Ibison" wrote:

> Just running sp_addarticle and sp_addsubscription then running the snapshot
> agent should be fine. Unlike merge, just the new article will get created.
> exec sp_addarticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.source_table = 'tEmployees'
> exec sp_addsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

Add Article To Trans Rep

Greetings again,
I am trying to add a table to a publication that already has a subscriber and have that table replicate to the subscriber.
2 servers, win2000, SQL 2000 all sp's applied.
1 server is the publisher and the 2nd is both the distributor and subscriber.
I went through the publication's properties page (em-replication-right click- properties) and added the table to the published articles list.
Then on the good advice of Paul Ibson in an earlier post to this newsgroup I created a new snapshot of the table and had
the dist. agent apply it. All went well.
However, new data in the table is not being replicated.
I've exhausted BOL and the web searches. Any recommendations as to the exact process I should follow would be greatly appreciated.
Thank you for your time.
Joe,
have you run the distribution agent? Run sp_browsereplcmds in the
distribution database to see if your new rows are waiting there. If they are
then just running the distribution agent will fix it. If not, then perhaps
the log reader is disabled?
HTH,
Paul Ibison

add article to publication in Merge Replication

I added an article to one of my publication with sp_addmergearticle as a
no-sync. A subscriber replicated last night with the following error:
The merge process could not retrieve column information for table
'dbo.ChargeGroup'.
(Source: Merge Replication Provider (Agent); Error number: -2147201016)
Could not find stored procedure 'sp_sel_00BE6107195E4608967622C23A664DE2'.
(Source: CBD076\MGW (Data source); Error number: 2812)
Do I need to reinitialize all my subscribers? I'm trying to add the
article without having to manually touch each one of the subscribers since
all of them are remote.
Thanks
Tina
Yes you will have to. You must use the @.force_invalidate_snapshot switch
with a setting of 1.ie
sp_addmergearticle 'Publication','tableName','tableName',
@.force_invalidate_snapshot=1
The bad news is that you will have to regenerate your snapshot. The good
news is that only this new article/table will travel to the subscriber(s).
Before you do this, can you check a couple of things.
Do a
select *from sysmergearticles where
select_proc='sp_sel_00BE6107195E4608967622C23A664D E2' on your publication
database and the problem subscriber.
Do you get a row on the publisher and none on the subscriber?
Then do this on your publisher?
select name from sysmergearticles where
select_proc='sp_sel_00BE6107195E4608967622C23A664D E2'
using that name do this on your subscriber
select select_proc from sysmergearticles where name=the name you got on your
publisher.
If these don't agree you have to drop your publications and subscriptions
and start again. Were you using dynamic snapshots? I ran into this problem
when I deployed dynamic snapshots, but after fixing it the one time,
everything worked well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:e8hf7NGgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> I added an article to one of my publication with sp_addmergearticle as a
> no-sync. A subscriber replicated last night with the following error:
> The merge process could not retrieve column information for table
> 'dbo.ChargeGroup'.
> (Source: Merge Replication Provider (Agent); Error number: -2147201016)
> ----
--
> --
> Could not find stored procedure 'sp_sel_00BE6107195E4608967622C23A664DE2'.
> (Source: CBD076\MGW (Data source); Error number: 2812)
> ----
--
> --
> Do I need to reinitialize all my subscribers? I'm trying to add the
> article without having to manually touch each one of the subscribers since
> all of them are remote.
> Thanks
> Tina
>
|||I already added the article with force_invalidate_snapshot = 1 and
regenerated the snapshot.
The sp_sel_ does exist in my sysmergearticles table on the publisher. The
subscriber is remote so I haven't had a chance to check for the stored
procedure on the subscriber.
Yes, I am using dynamic filters. I sure hope I don't have to drop my
publications to fix this.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u9WkigGgEHA.556@.tk2msftngp13.phx.gbl...
> Yes you will have to. You must use the @.force_invalidate_snapshot switch
> with a setting of 1.ie
> sp_addmergearticle 'Publication','tableName','tableName',
> @.force_invalidate_snapshot=1
> The bad news is that you will have to regenerate your snapshot. The good
> news is that only this new article/table will travel to the subscriber(s).
> Before you do this, can you check a couple of things.
> Do a
> select *from sysmergearticles where
> select_proc='sp_sel_00BE6107195E4608967622C23A664D E2' on your publication
> database and the problem subscriber.
> Do you get a row on the publisher and none on the subscriber?
> Then do this on your publisher?
> select name from sysmergearticles where
> select_proc='sp_sel_00BE6107195E4608967622C23A664D E2'
> using that name do this on your subscriber
> select select_proc from sysmergearticles where name=the name you got on
your[vbcol=seagreen]
> publisher.
> If these don't agree you have to drop your publications and subscriptions
> and start again. Were you using dynamic snapshots? I ran into this problem
> when I deployed dynamic snapshots, but after fixing it the one time,
> everything worked well.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:e8hf7NGgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> ----
> --
'sp_sel_00BE6107195E4608967622C23A664DE2'.[vbcol=seagreen]
> ----
> --
since
>
|||You mentioned that you added it as no-sync.
Did you mean the subscription was added as a no sync subscription ?
If so then, the table needs to exist at the subscriber when merge is run.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> I already added the article with force_invalidate_snapshot = 1 and
> regenerated the snapshot.
> The sp_sel_ does exist in my sysmergearticles table on the publisher.
The[vbcol=seagreen]
> subscriber is remote so I haven't had a chance to check for the stored
> procedure on the subscriber.
> Yes, I am using dynamic filters. I sure hope I don't have to drop my
> publications to fix this.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u9WkigGgEHA.556@.tk2msftngp13.phx.gbl...
subscriber(s).[vbcol=seagreen]
publication[vbcol=seagreen]
> your
subscriptions[vbcol=seagreen]
problem[vbcol=seagreen]
a[vbcol=seagreen]
error:[vbcol=seagreen]
number: -2147201016)
> ----
> 'sp_sel_00BE6107195E4608967622C23A664DE2'.
> ----
> since
>
|||Hi Mahesh,
The table already exist at the subscriber.
Thanks
Tina
"Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
news:eHsJpXNgEHA.140@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> You mentioned that you added it as no-sync.
> Did you mean the subscription was added as a no sync subscription ?
> If so then, the table needs to exist at the subscriber when merge is run.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> The
switch[vbcol=seagreen]
good[vbcol=seagreen]
> subscriber(s).
> publication
on[vbcol=seagreen]
> subscriptions
> problem
as[vbcol=seagreen]
> a
> error:
> number: -2147201016)
> ----
> ----
the
>
|||I ran into something a little similar with dynamic snapshots.
I deployed one group of 20 subscribers one month, and then another month I
deployed a second 20. IIRC the first 20 encountered this error.
I had to start from scratch. The error I got was with missing sp_ins*,
sp_upd*, and sp_del* procs on the subscriber.
After the second deployement we never had the error again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> I already added the article with force_invalidate_snapshot = 1 and
> regenerated the snapshot.
> The sp_sel_ does exist in my sysmergearticles table on the publisher.
The[vbcol=seagreen]
> subscriber is remote so I haven't had a chance to check for the stored
> procedure on the subscriber.
> Yes, I am using dynamic filters. I sure hope I don't have to drop my
> publications to fix this.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u9WkigGgEHA.556@.tk2msftngp13.phx.gbl...
subscriber(s).[vbcol=seagreen]
publication[vbcol=seagreen]
> your
subscriptions[vbcol=seagreen]
problem[vbcol=seagreen]
a[vbcol=seagreen]
error:[vbcol=seagreen]
number: -2147201016)
> ----
> 'sp_sel_00BE6107195E4608967622C23A664DE2'.
> ----
> since
>
|||Thanks for all your input. I have many shoppes scheduled to replicate
Monday night. I'll assess the issue Tuesday morning and see where I need
to go from there. I'll let you know how it turns out.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23QzAfNTgEHA.2588@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I ran into something a little similar with dynamic snapshots.
> I deployed one group of 20 subscribers one month, and then another month I
> deployed a second 20. IIRC the first 20 encountered this error.
> I had to start from scratch. The error I got was with missing sp_ins*,
> sp_upd*, and sp_del* procs on the subscriber.
> After the second deployement we never had the error again.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> The
switch[vbcol=seagreen]
good[vbcol=seagreen]
> subscriber(s).
> publication
on[vbcol=seagreen]
> subscriptions
> problem
as[vbcol=seagreen]
> a
> error:
> number: -2147201016)
> ----
> ----
the
>

add an Oracle linked server as a subscriber

Hi,
I have an Oracle 9i client installed on SQL Server 2000, and I want to add a linked Oracle server to SQL Server so that I can use the linked Oracle server as a subscriber, I got an error says 'OLEDB error, OLEDB error trace: initialize returned 0x80004005
', and I can not view the tables or views in the Oracle linked server, can anybody help me on this?
Hong
80004005 is generic access denied you need to use a login and password on
your linked server which maps to SYSDBA or SYSTEM on the Oracle server.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Hong Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:E7D43F27-B100-4CE1-A32C-B084E8EC2C96@.microsoft.com...
> Hi,
> I have an Oracle 9i client installed on SQL Server 2000, and I want to add
a linked Oracle server to SQL Server so that I can use the linked Oracle
server as a subscriber, I got an error says 'OLEDB error, OLEDB error trace:
initialize returned 0x80004005', and I can not view the tables or views in
the Oracle linked server, can anybody help me on this?
> Hong

Add a column to published table without replicating to subscriber

I would like to add a column to a published table but not have that column replicated to subscribers. I can accomplish this via the UI by adding the column and then unchecking it. This adds the column to the publisher table but does not replicate it to the subscriber.

I am looking for a programmatic method to add a column to the base table and unmark it for replication.

Thanks for your assistance.

Here is a TSQL example (I suppose you mean SQL Server 2005):

1. First turn replication_ddl property off for your publication

exec sp_changepublication @.publication = 'pubname'
, @.property = 'replicate_ddl'
, @.value = '0'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 0

2. Execute DDL statement to add a new column:

alter table authors add newcol8 int not null default 0

3. If you want to turn schema/DDL replication on for subsequent add columns, then turn it back on:

exec sp_changepublication @.publication = 'pubname'
, @.property = 'replicate_ddl'
, @.value = '1'
, @.force_invalidate_snapshot = 0
, @.force_reinit_subscription = 0

Hope that helps,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Another option you can have is vertical partitioning with which you can filter out which columns you want to publisher and which ones you do not want to.

When you mention that you could achieve this by doing in the UI, this is what is internally happening. The table is enabled for vertical partitioning and the newly added column is removed from the partition when you uncheck this column

Look in BOL for:sp_mergearticlecolumn, sp_articlecolumn, vertical_partition parameter of sp_addarticle/sp_addmergearticle