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)

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

Add a table to a merge replicated database WITHOUT forcing a snapshot

Hello,
I've tried to catch up on all the posts in this area, and I can't really see
where this has been asked. If I have repeated a previous question, I do
apologize.
We are trying to add new tables to an existing merge replication by running
a create table statement on a publication database and then using
sp_addmergearticle with the @.creation_script parameter pointing to the sql
file to run on the subscriber as we are setting the identity fields
manually. When the procedure runs, it detects that the change requires a new
snapshot. We cannot afford the time it takes to create an entirely new
snapshot (could take days) so we are looking for the ways to speed up the
process.
We also tried to create tables directly by entering information into the
system tables but we have had trouble with generating insert, update and
delete triggers to replicate data.
We would like to know whether
1. There is a way to invalidate a snapshot for new tables only?
2. There is another way of creating new tables on a replicated
database.
Any help you could provide would be greatly appreciated! Thanks for your
assistance.
Sincerely,
Marshall
The standard ways to achieve this are:
(a) to create a new publication which contains the new table
(b) or to 'initialize' the table (by creating a complete snapshot)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I guess that's the problem. Neither of these solutions is possible for us.
We are using Merge Replication in a high availability solution and cannot
afford to have the system down for the amount of time it will take to either
1. Generate a new publication with the additional requisite tables, or
2. Add the tables and generate a new snapshot.
What you have described are indeed the standard ways to accomplish the task,
however, due to our environment we really need to do something faster. We
are trying to create these additional tables in the same publication and
without the overhead of recreating the snapshot, which is totally
unnecessary except for the internal triggers required to keep the tables
synchronized (they are, after all, empty after they are created.) Isn't
there some way we could just create the triggers required for the new tables
and apply those to the subscriber? We're really looking for a solution that
takes less than thirty minutes!
Thanks in advance for any suggestions!
Marshall
|||Creation of new publication with one empty table in it should take much less than 30 minutes... If it's not a problem to have multiple publications, then it is the way to go.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"WideOpenWest" <MBCDev> wrote in message news:e6GdnTskHapNZPreRVn-jg@.wideopenwest.com...
Paul,
I guess that's the problem. Neither of these solutions is possible for us.
We are using Merge Replication in a high availability solution and cannot
afford to have the system down for the amount of time it will take to either
1. Generate a new publication with the additional requisite tables, or
2. Add the tables and generate a new snapshot.
What you have described are indeed the standard ways to accomplish the task,
however, due to our environment we really need to do something faster. We
are trying to create these additional tables in the same publication and
without the overhead of recreating the snapshot, which is totally
unnecessary except for the internal triggers required to keep the tables
synchronized (they are, after all, empty after they are created.) Isn't
there some way we could just create the triggers required for the new tables
and apply those to the subscriber? We're really looking for a solution that
takes less than thirty minutes!
Thanks in advance for any suggestions!
Marshall
|||I never thought of doing something like that; it would probably work, but it
is standard procedure for us to add tables when we do software releases.
After some time, we're going to have publication after publication, which is
ugly and probably a significant performance threat (each publication will
need its own agents.)
Is there some way we could use the information generated in the snapshot of
that new publication to add the tables to the original publication? Perhaps
this is not so orthidox, but we really need to find a solution to this
issue.
Another idea that has been suggested is to add several hundred generic,
empty tables to the new publication. Then, every time we need a new table
in a release we add the columns to one of those generic tables and begin
using it. The problem here is that we'd have several hundred generically
named tables! Is it possible to rename a table across a merge replication?
This is ugly also.
Help!
Thanks,
Marshall
"Kestutis Adomavicius" <kicker.lt@.noospaam_tut.by> wrote in message
news:%235KQnK63FHA.2424@.TK2MSFTNGP10.phx.gbl...
Creation of new publication with one empty table in it should take much less
than 30 minutes... If it's not a problem to have multiple publications, then
it is the way to go.
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"WideOpenWest" <MBCDev> wrote in message
news:e6GdnTskHapNZPreRVn-jg@.wideopenwest.com...
Paul,
I guess that's the problem. Neither of these solutions is possible for us.
We are using Merge Replication in a high availability solution and cannot
afford to have the system down for the amount of time it will take to either
1. Generate a new publication with the additional requisite tables, or
2. Add the tables and generate a new snapshot.
What you have described are indeed the standard ways to accomplish the task,
however, due to our environment we really need to do something faster. We
are trying to create these additional tables in the same publication and
without the overhead of recreating the snapshot, which is totally
unnecessary except for the internal triggers required to keep the tables
synchronized (they are, after all, empty after they are created.) Isn't
there some way we could just create the triggers required for the new tables
and apply those to the subscriber? We're really looking for a solution that
takes less than thirty minutes!
Thanks in advance for any suggestions!
Marshall