how to transfer new table during Replication

Question

how to transfer new table during Replication

su_kumar11

SSCommitted

Points: 1675
More actions
August 12, 2008 at 12:12 am

#196950

Hi,
Can any one tell me how to transfer a new table during replication
when we have all ready configured for replication on other server?
Pls help me out as possible as...
regards
Sunil..............

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

Depends on the type of replication. Also on what size your database is. I assume it's inconveniently large, true? ALso, what version of ss? what rev level?

Red8Rain Hall of Fame Points: 3312 More actions · Answer 2

if repl is already setup, u can add a new table if you like via the gui or syntax. Since you are asking, you should do it via the GUI under replication monitor. connect to your distributor and add this new table.

make sure the table is on the publisher and subscriber.

-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.

Christopher Favero Old Hand Points: 349 More actions · Answer 3

Oberion (8/20/2008)
if repl is already setup, u can add a new table if you like via the gui or syntax. Since you are asking, you should do it via the GUI under replication monitor. connect to your distributor and add this new table.
make sure the table is on the publisher and subscriber.

so if the subscriber has some published tables that have added columns

i.e.

PUB SUB

col1 col1

col2 col2

col3 col3

col4

is there a way to add the new table in the pub and replicate the DDL over without doing a new snapshot and blowing away the subscriber's added columns?

steve smith-401573 SSChampion Points: 10889 More actions · Answer 4

In snapshot replication, no. In Merge replication, depends on how you've set up your articles in the merge, but normally (simple cases) the tables need to match up. Similarly for trans replication. You would be best served to make a live backup copy of your table on the database (or perhaps a different database, just in case) and then test to see what happens.

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

Christopher Favero (8/26/2008)
Oberion (8/20/2008)
if repl is already setup, u can add a new table if you like via the gui or syntax. Since you are asking, you should do it via the GUI under replication monitor. connect to your distributor and add this new table.
make sure the table is on the publisher and subscriber.
so if the subscriber has some published tables that have added columns
i.e.
PUB SUB
col1 col1
col2 col2
col3 col3
col4
is there a way to add the new table in the pub and replicate the DDL over without doing a new snapshot and blowing away the subscriber's added columns?

So, you have Table_1 in which the Subscriber Table_1 has more fields than Publisher Table_1 ? And now you want to add Table_2 to replication ?? If it's transactional, I think you can add Table_2 to the Publication, and it will create a snapshot of just Table_2 and send it over to the subscriber. Table_1 will be unaffected.

You can't do this in snapshot replication because the subscriber tables get re-created each time. I don't know about merge.

In scenarios like this, I set up a little test in my own personal DBA_Test database & experiment before touching the real thing.

Christopher Favero Old Hand Points: 349 More actions · Answer 6

yep I am testing it in a dev environment right now and so far it looks like the new table forces a new snapshot to be created and that then blows away the extra column in the destination db.

And I am using transactional repl as well not sure if i said that b4.

I am going over the scripts to see if there is a way to add the table via tsql and tell it to only capture the new snapshot like you said... unfortunately so far it has only ended with the same results of the destination table losing the extra column.

:angry:

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

Do I understand correctly that Table_A has the additional columns, and that you want to add Table_B as a newly replicated table ?

I've done the following when making changes to replicated tables such as adding a column but not wanting to affect other tables. Script out the "publication create" code and the "publication delete" code. Then identify the code just for the table affected. Drop the table from replication using just that portion of the script, make changes to the table, then add it back in using just the portion of the script to add the table to the publication.

noeld SSC Guru Points: 96590 More actions · Answer 8

Christopher Favero (8/27/2008)
yep I am testing it in a dev environment right now and so far it looks like the new table forces a new snapshot to be created and that then blows away the extra column in the destination db.
And I am using transactional repl as well not sure if i said that b4.
I am going over the scripts to see if there is a way to add the table via tsql and tell it to only capture the new snapshot like you said... unfortunately so far it has only ended with the same results of the destination table losing the extra column.
:angry:

Make sure that in the article properties you select "Delete Data" instead of "DROP Object". on the "Destination Object Properties".

* Noel

Christopher Favero Old Hand Points: 349 More actions · Answer 9

I did not get a chance to try this today but I will be giving it a whirl first thing tomorrow and post back success or Fail.

Thanks for the info guys!