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 New Table and EXCLUDING from merge replication

New to merge replication - sql 2000
I simply wish to add a table to the publisher database and this table
will never be replicated. Is there anything I need to do to exclude it?
Thanks,
Craig
Craig,
no problems here - creating the table using Enterprise Manager or using
Create Table will not automatically add it to the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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 new column to a table involved in merge replication

Hi everyone,
I would like to know if there is some way to add a new column to an
existing table in a database published via merge replication that does
not require reinitialization of the articles.
Thanks.
Aramid
Aramid,
have a look at sp_repladdcolumn in BOL. This can also be done through the
GUI in Enterprise manager. In SQL Server 2005 the same effect is achieved
directly using Alter Table statements (provided the subscriber is also SQL
2005).
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding a new article to Transactional replication

Hi,
I added a new article to an already existing publication via EM,
and reintialized it. When the snapshot agent ran the change was
replicated, but the snapshot agent took the snapshot of all the
articles in the publication about 85 of them, instead of just
replicating the change.
IS there a way to reintialize an article in the publication, so
snapshot agent would only snap that one article instead of the whole
publication?
Does this also apply to adding a new column or dropping a column from a
publication?
Thanks for your help.
GG
for sql 2000 replicating to sql 2000 subscribers, only the article should be
replicated. I am not sure what happened in your case.
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
"GG" <gdabbara@.gmail.com> wrote in message
news:1117574970.901699.85980@.f14g2000cwb.googlegro ups.com...
> Hi,
> I added a new article to an already existing publication via EM,
> and reintialized it. When the snapshot agent ran the change was
> replicated, but the snapshot agent took the snapshot of all the
> articles in the publication about 85 of them, instead of just
> replicating the change.
> IS there a way to reintialize an article in the publication, so
> snapshot agent would only snap that one article instead of the whole
> publication?
> Does this also apply to adding a new column or dropping a column from a
> publication?
>
> Thanks for your help.
> GG
>
|||You mention that you reinitialized it - this is the reason why all articles
were created by the snapshot agent. After adding the article, you just need
to run the snapshot agent and then the distribution agent to get the single
article.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your responses. I will try just starting the snapshot agent
instead of reintializing the whole publication. I was under the
impression that snapshot agent doesn't know about the change unless I
reintialize.
Thanks
GG
|||Adding an article is normally taken care of when running the snapshot. There
are some exceptions - if the initial publication was a nosync one, then the
snapshot of the new article won't generate anything by default. However
assuming this is niot the case you'll be fine.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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:

Adding a colum to a merge replication

Hi,
I have a question on how to add a column in a merger replication?
Actualy I know how to add a column in a publication for a merge replication
What I need to know is how to create a foreign Key for that column
Exemple :
01- I have a table myTable that is already in my publication as an article
02- I want to add the column employeID int NULL
03- I also need to create a foreign key on that column to another column of
another table that is also in the publication
Is that possible ?
I have a doubt because a forign key is an alteration over a table and not
over a column and I think we can not make an alter Table on a table that is
in a publication with Sql 2000
SO is there a way we can create a foriegn Key for a new column in a table
that is already in a publication ?
Thanks in advance !
Pls have a look at sp_addscriptexec.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Adding a article causes ALL articles to be refreshed

I have setup Transactional replication in SQL 2005 between two servers and have about 200 tables being replicated. The problem is that every time, I add or drop a table to replication, and start the Snapshot agent, it re-initializes every article and re-loads every article. This process takes 1 hour to complete and CPU usage goes to 100% during that time.

This behaviour seems very different from SQL 2000 where I would start Snapshot agent and only the relevant tables were added/dropped.

Has that functionality changed from 2000 to 2005? Am I not doing something right?

Thanks,

Amir

This is the default behavior for anonymous subscribers. IIRC through the GUI for named subscribers a complete snapshot will be generated. However if you use sp_addarticle only the mini snapshot for the one table will be generated.|||

I have tried using sp_article. The problem is that it gives me the error:

Cannot make the change because a snapshot is already generated. Set @.force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

And in order to avoid the error, if I use the Option of @.force_invalidate_snapshot=1, then again I am back to where I was before and it re-initializes everything.

BTW, What does IIRC mean?

Thanks,

Amir

|||

Any more thoughts on this? If my replication is setup and running, is there NO WAY to add "one" table to the publication without having to do the Bulk copy on ALL tables? Adding a table to replication seems one of the most basic maintenance tasks. I'm still surprised that MSFT has made that basic task so difficult in 2005.

added article to current transactional repl. How to replicate additional article?

Hi, I already have transactional replication setup between two servers
(production server to reporting server).
I have created a new table witha primary key and have added this table as
an article to the publication.
How can i get my setup to take an initial snapshot of the table and apply it
to the reporting server?
Any help most appreciated!
thanks
John
John,
you will need to run the snapshot agent then synchronize. The whole process
from start to finish is:
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Start snapshot agent, start distribuition agent.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
Are you saying that I have to take a snapshot of the whole database all over
again?
I thought I could take a snapshot of just the article that has been newely
added to the publication?
In actual fact, the 'Last Action' column on my agent (subsicriber' is saying
"The initial snapshot for article 'myTable' is not yet available"
Many thanks!
John
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OQ2XvSVEGHA.4000@.TK2MSFTNGP10.phx.gbl...
> John,
> you will need to run the snapshot agent then synchronize. The whole
> process from start to finish is:
> exec sp_addarticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.source_table = 'tEmployees'
> exec sp_addsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> Start snapshot agent, start distribuition agent.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||John,
if you add a new table and subscription to that table, running the snapshot
agent won't create a complete snapshot - just the new article.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Add the NOT FOR REPLICATION Option to Existing Tables

Hello,

How to add the NOT FOR REPLICATION Option to Existing Tables( IDENTITY column,FOREIGN KEY constraints )- TSQL.

Thanks,

Kanna.

alter table dbo.testtable

alter column [id] add NOT FOR REPLICATION

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 replication component

Hello,
I have a publication that is pushed from a SQL 2005 Standard box to
about 40 SQL express subscribers. One of the servers went down and was
rebuild and I reran the same install script as I ran on the other
servers ( I made the script). I pushed out the same subscription as
before to it, and it is running smoothly (transactional). I then
needed to set up a snapshot subscription to all servers, and this
server was the only one that would not allow me to. When I try and set
up a new subscription to it I get this message:
"Replication components are not installed on this server. Run SQL
Server SETUP again and select the option to install replication.
(Microsoft SQL Server, Error: 21028).
So I thought I would just redo the setup and only pick the replication
component, but you cant, it makes you pick the whole database engine
tree to get it, and then the install says that it is already installed
and won't let you continue. Same happens if I go through add/remove
programs and use the add component link.
Any thoughts or ideas on this error. Right now the only solution I
have is reinstall from scratch.
OK, that error message was totally wrong. Since adding it through the
UI was not working, I added another subscription through the UI and at
the end generated a script file instead of actually finishing it. I
then changed the script file to point at my stubborn server that
supposedly did not have replication components, and the script ran fine
and the subscription was created, and worked.
PT
|||Can you try to create a local publication on this server? I am not sure if
this message is erroneous or not.
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 T." <weluvpaul@.hotmail.com> wrote in message
news:1168454660.767978.320110@.p59g2000hsd.googlegr oups.com...
> Hello,
> I have a publication that is pushed from a SQL 2005 Standard box to
> about 40 SQL express subscribers. One of the servers went down and was
> rebuild and I reran the same install script as I ran on the other
> servers ( I made the script). I pushed out the same subscription as
> before to it, and it is running smoothly (transactional). I then
> needed to set up a snapshot subscription to all servers, and this
> server was the only one that would not allow me to. When I try and set
> up a new subscription to it I get this message:
> "Replication components are not installed on this server. Run SQL
> Server SETUP again and select the option to install replication.
> (Microsoft SQL Server, Error: 21028).
> So I thought I would just redo the setup and only pick the replication
> component, but you cant, it makes you pick the whole database engine
> tree to get it, and then the install says that it is already installed
> and won't let you continue. Same happens if I go through add/remove
> programs and use the add component link.
> Any thoughts or ideas on this error. Right now the only solution I
> have is reinstall from scratch.
>

Add or Modify Keys in Merge Replication

I have several articles in a SQL Server 2000 merge publication where I need to do one of the following:
1. Delete a existing key and its column, and create a new one with a new column.
2. Add a new key using existing columns.
3. Add a new key using a new column to be added.
Since this is merge replication, I cannot drop the articles involved using sp_droparticle and then use sp_addarticle after making the modifications.
The publication has several active subscriptions.
Any suggestions and/or procedures for above 3 items would be very much appreciated.
Thanks.
Bill
Bill,
have a look at these three procedures to do what you require:
sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
For your three cases, something like:
1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
sp_repladdcolumn
2. sp_addscriptexec (alter table x add constraint ...)
3. sp_repladdcolumn
For the sp_addscriptexecs don't forget to do the same process on the
publisher, and be sure to get the syntax correct, otherwise there'll be a
continuous error.
HTH,
Paul Ibison
|||Thanks you, Paul! I will give these solutions a try.
"Paul Ibison" wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||Having done one of these (my situation calls for nuber 1) would I have to
reinitialise the snapshot?
On Mon, 5 Jul 2004 20:04:21 +0100, Paul Ibison <Paul.Ibison@.Pygmalion.Com>
wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn
> ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
|||"Paul Ibison" wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||Paul:
I tried using sp_addscriptexec to test the deletion of an existing key. However,
when the subscriber synchronizes, the script fails to run. It keeps saying the
script file or directory does not exists. However, it clearly does exist in the
path that is shown in the error.
The synchronization takes place over the internet. When I have run sp_addscripexec
I have tried each of the following in specifying the location of the script file on
the publisher/distributor:
-- "C:\<directory>\<sub directory>\<script file name>"
-- "\\<server name>\<share name>\<sub directory>\<script file name>"
-- "\\<IP of the server>\<share name>\<sub directory>\<script file name>"
-- "\\<URL of the server>\<share name>\<sub directory>\<script file name>"
The last one does not work when I run sp_addscriptexec. However, the first three
do. After sp_addscriptexec runs, it adds the script file to the snapshot location
on the server. I am running sp_addscriptexec at the publisher through a remote
desktop connection.
When the subscriber synchronizes, in the merge agent session details, the
synchronization is successful and data changes are merged, however, one of the
details shows the error that "osql" cannot find the file or directory at:
"\\<server name>\C$\<share name>\<snapshots folder>\<some long date & time
string>\<script file name>". The "<server name>" is the name of the
publisher\distributor.
I must be missing some step but I find nothing in BOL that helps. I also searched
Micorsoft.com and used Google to look for the osql error, but find nothing.
Do you or does anyone else have any suggestions or ideas to try?
Thanks in advance.
Bill
"Paul Ibison" wrote:

> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||are you on the network? If you are not connected to the network or the
internet you will get this error.
If you are running workstation or professional you may have exceeded the
number of simultaneous connections for your os.
You may need to issue a net stop server and then a net start server and try
again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:43B07A06-4DBC-48C1-A815-E11BA7103C04@.microsoft.com...
> Paul:
> I tried using sp_addscriptexec to test the deletion of an existing key.
However,
> when the subscriber synchronizes, the script fails to run. It keeps
saying the
> script file or directory does not exists. However, it clearly does exist
in the
> path that is shown in the error.
> The synchronization takes place over the internet. When I have run
sp_addscripexec
> I have tried each of the following in specifying the location of the
script file on
> the publisher/distributor:
> -- "C:\<directory>\<sub directory>\<script file name>"
> -- "\\<server name>\<share name>\<sub directory>\<script file name>"
> -- "\\<IP of the server>\<share name>\<sub directory>\<script file name>"
> -- "\\<URL of the server>\<share name>\<sub directory>\<script file name>"
> The last one does not work when I run sp_addscriptexec. However, the
first three
> do. After sp_addscriptexec runs, it adds the script file to the snapshot
location
> on the server. I am running sp_addscriptexec at the publisher through a
remote
> desktop connection.
> When the subscriber synchronizes, in the merge agent session details, the
> synchronization is successful and data changes are merged, however, one of
the
> details shows the error that "osql" cannot find the file or directory at:
> "\\<server name>\C$\<share name>\<snapshots folder>\<some long date & time
> string>\<script file name>". The "<server name>" is the name of the
> publisher\distributor.
> I must be missing some step but I find nothing in BOL that helps. I also
searched
> Micorsoft.com and used Google to look for the osql error, but find
nothing.[vbcol=seagreen]
> Do you or does anyone else have any suggestions or ideas to try?
> Thanks in advance.
> Bill
>
> "Paul Ibison" wrote:
,[vbcol=seagreen]
a[vbcol=seagreen]

Add new user?Dial-up replication?

after I replicated two project server database in two sql server, I don`t add new user or modify old user in project server,Why??
and another my problem:
How replication with Dial-up connection(I replicated with LAN)??
Please help me
Thanks
Bita
Message posted via http://www.sqlmonster.com
I don't understand your first question. Are you saying you are unable to
modify any old user or add a new one? If so what is the error message you
are getting.
In reference to your second question, consult this kb article
http://support.microsoft.com/default...b;en-us;241149
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Bita Joghataie via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:46b960085fdb4a61a0d40f13df7c622f@.SQLMonster.c om...
> after I replicated two project server database in two sql server, I don`t
add new user or modify old user in project server,Why??
> and another my problem:
> How replication with Dial-up connection(I replicated with LAN)??
> Please help me
> Thanks
> Bita
> --
> Message posted via http://www.sqlmonster.com

Add new tables to replication

Using SQL Server 2000 with sp4, I have setup a merge replication with a snapshot, currently if we do a schema change we have to:
-break replication,
-stop the subscription,
-make our modifications to the publisher,
-then create a backup of the database,
-push the backup to all of the subscribers,
-restore the database to the subscriber,
-reinitialize the subscription and push the subscription to the subscribers without pushing the contents of the initial snapshot

the reason why we do it this way is that the various servers are located hundreds of miles apart from the publisher and even with broadband connections, re-initializing the subscription always fails.

this is incredibly cumbersome and time consuming and i know there must be a better way to do this.

i need to add two tables to an existing database that is currently being replicated via merge replication, is there an easier way to make the schema changes and have them replicate to all the subscribers?

Rather than making a schema change to an existing table article in the merge publication, it seems like you are trying to add an incremental article to an existing table article. Also from what I see in the scenario, it seems like the subscriptions are set up to be NO SYNC (SP_addmergesubscription @.sync_type='none'). The NO SYNC means you are populating the subscriber server with the initial data manually via backup/restore.

In order for you to add a new table or two to the existing merge publication you may not have to break replication. Here are some steps to try:

1) Create the new tables at the publisher. These new tables must include a rowguid column (i.e. [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL) because merge will use this for change tracking. This scenario will not work without rowguid columns on the new tables.

2) Populate the table

3) Add the new table as a table article in the existing merge publication. You will be warned appropriately that the full snapshot needs to be rerun.

4) Run the full snapshot agent job.

5) BCP out the data from the new table article to a file and send it to the remote subscriber server

6) Create the new table schema at the subscriber

7) BCP in the data on the subscriber server

8) Execute the merge agent. This is required to have the merge triggers built on the subscriber for change tracking. *** Do not do any DML changes on the subscriber server until the merge agent is executed at least one time.

9) Now you should be able to execute DML on the new table at both the publisher and subscriber and sync the changes.

Try this out on a test system to verify it works. I tried on my system and it accomplished what you wanted.

Tom

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

Add new tables to replication

I have setup transactional replication with no snapshot. I created a new
table on publisher side and I would want the table also to be replicated.
Here is what i did-
- drop the subscription
- create/dts table to subscriber
- create insert update stored procedures at subscriber(i have created them
during testing)
- add article to publication
-recreate subscription
Is there any other way to do it as I donot want my subscription to be
dropped each time as it has some other tables that are replicated
continuously...
Regards,
Ravi
Ravi,
to avoid dropping the subscription: as you did before...
> - create/dts table to subscriber
> - create insert update stored procedures at subscriber(i have created them
> during testing)
> - add article to publication
Then
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
, @.sync_type = 'none'
ie add the subscription to the particular article.
HTH,
Paul Ibison, SQL Server MVP (www.replicationanswers.com)

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 new table to sql server 2005 transaction replication

I am trying to add a new table to tansaction replication on sql server 2005.
The problem I am having is that if I restrt the snapshot agent, it is doing
all the tables in the publisher. Ho can I make the snapshot agent do only the
new table?
Steps I have taken:
1. Add new table to publisher and the publication.
2. Star the snapshot agent
This is all done using the SSMS Gui.
Thanks,
George Gopie
George,
can you check to see if you have enabled anonymous subscribers. If you have,
then this behaviour is normal and you could reset it (using
sp_changepublication) to create a snapshot of only the one table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Use sp_addarticle for this. If it is a pull subscription you will need to do
a sp_refreshsubscriptions
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
"georgeg" <ggg@.hotamil.com> wrote in message
news:5D774662-3B4D-4B58-9091-36F05F8CDB46@.microsoft.com...
>I am trying to add a new table to tansaction replication on sql server
>2005.
> The problem I am having is that if I restrt the snapshot agent, it is
> doing
> all the tables in the publisher. Ho can I make the snapshot agent do only
> the
> new table?
> Steps I have taken:
> 1. Add new table to publisher and the publication.
> 2. Star the snapshot agent
> This is all done using the SSMS Gui.
> Thanks,
> --
> George Gopie
>
|||Hilary,
I have used the sp_addarticle, and sp_refreshSubscriptions succesfully.
sp_addarticle
@.publication = RYS_SPRS77_PUBL,
@.article = InstrumentCheckResult,
@.source_object = InstrumentCheckResult
sp_refreshsubscriptions
@.publication = RYS_SPRS77_PUBL
However, when I checked the ReplicationMonitor
I see message: The initial snapshot for article InstrumrntCheckResult is not
yet available
Do I still need to run the snapshot agent again?
Thanks,
George Gopie
"Hilary Cotter" wrote:

> Use sp_addarticle for this. If it is a pull subscription you will need to do
> a sp_refreshsubscriptions
> --
> 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
>
> "georgeg" <ggg@.hotamil.com> wrote in message
> news:5D774662-3B4D-4B58-9091-36F05F8CDB46@.microsoft.com...
>
>
|||Yes, it should only generate a snapshot for the new article.
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
"georgeg" <ggg@.hotamil.com> wrote in message
news:D26B8402-F678-43AA-9144-98C9A5172991@.microsoft.com...[vbcol=seagreen]
> Hilary,
> I have used the sp_addarticle, and sp_refreshSubscriptions succesfully.
> sp_addarticle
> @.publication = RYS_SPRS77_PUBL,
> @.article = InstrumentCheckResult,
> @.source_object = InstrumentCheckResult
> sp_refreshsubscriptions
> @.publication = RYS_SPRS77_PUBL
> However, when I checked the ReplicationMonitor
> I see message: The initial snapshot for article InstrumrntCheckResult is
> not
> yet available
> Do I still need to run the snapshot agent again?
> Thanks,
> --
> George Gopie
>
> "Hilary Cotter" wrote:

Add new article to transactional replication

This one is driving me crazy...
We are using Transactional Replication with the subscribers initialized from
a backup (no snapshot). It's working great, but now we need to add several
new articles to the publication and have them picked up in the subscribers.
The only way I can get this to work is to drop the subscriber, backup the
published database, restore the previously made backup over the destination
databases and then re-create the subscriptions. There has to be a better
way?
I've tried using the sp_reinitializesubscription, but that doesn't work
since we aren't using a snapshot. I've tried skipping the restore, and just
creating the tables in both the source and destination databases. Doing
this, the subscription is re-created sucessfully, but data in the new
articles is not actually being replicated.
Replication isn't my forte, and the documentation on initializing with
backups is pretty sparse, so any advice is appreciated.
Use sp_addarticle followed by sp_addsubscription with @.sync_type =
automatic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com