Add column to subscriber table

Question

Add column to subscriber table

Lori Neville

SSC Veteran

Points: 227
More actions
October 16, 2009 at 8:46 am

#212390

Hi!
I am replicating a table from one database to another database. If I add a column to the subscriber table, will that mess up replication? It is a transaction publication and I do not want this column on the publisher table. When the publisher adds records to the subscriber table, the column value would just need to be NULL.
Thanks in advance for your help!
Lori

Viewing 15 posts - 1 through 15 (of 17 total)

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Hi,

Since this was not implemented by me earlier, I did try after looking at your question and found no issues on the subscriber when added I a new column. Transactional Replication worked smoothly.

But a word of caution, setup in test environment and monitor if for substantial time before coming to a conclusion.

I hope someone can give their valuable opinion on this.

Even I would like to know if any one has run into issues in such scenario..

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 2

I'm a bit surprised that you could add a column without getting an error about the table being replicated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

happycat59 One Orange Chip Points: 29388 More actions · Answer 3

Replication is quite happy to have different table definitions on the subscriber.

You will need to be careful if you need to deliver a snapshot to the subscriber - you will need to work out what should happen to the data in the table. I assume that you will be updating the extra column(s) on the subscriber. Hence, you may well wish to retain the data. By default, when a snapshot is delivered to the subscriber, the table is dropped then created with the definition from the published and the data copied into it. You will need to manage that process for yourself (although there are a few options for adding your own scripts when delivering a snapshot)

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

Thanks for the info, HappyCat... I haven't had to worry much about replication in the past...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thecodeguys Ten Centuries Points: 1076 More actions · Answer 5

You might employ scripts that run before and after a snapshot applies. I have never tried this yet, but in the Snapshot Agent properties you can set up scripts that run before and after.

So you could save the table to a holding tank before the snapshot, apply the snapshot, and after the snapshot completes, readd the column to the sub, and run an update to backfill the column.

jbuchan SSC-Addicted Points: 422 More actions · Answer 6

I'm currently trying to add a column to the subscriber only through merge as we need to modify the data before it populates it.

i'm trying to add the column but i'm getting the following error.

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_altertable, Line 350

The data definition language (DDL) command cannot be executed at the Subscriber. DDL commands can only be executed at the Publisher. In a republishing hierarchy, DDL commands can only be executed at the root Publisher, not at any of the republishing Subscribers.

Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 182

The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.

Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

Do I need to disable something before adding it?

Thanks in advance

jbuchan SSC-Addicted Points: 422 More actions · Answer 7

I've found the trigger

MSmerge_tr_altertable.

which I think is blocking it

happycat59 One Orange Chip Points: 29388 More actions · Answer 8

My post definitely applies to transactional replication.

I have just tried to add a column to a merge publication. First at the subscriber...got the same error you did. Second at the publisher...it works as you want. updates that include the pre-existing and the new column work correctly.

Not sure why you can't make a change at the subscriber. It might be worth checking out the various paramter to sp_addmergearticle - there might be a way - no guarentees though. (e.g. drop the subscription, add the column, add the subscription but specify no action if the table exists).

You can test this on a single server with a couple of test databases fairly quickly.

jbuchan SSC-Addicted Points: 422 More actions · Answer 9

happycat59 (5/11/2010)
My post definitely applies to transactional replication.
I have just tried to add a column to a merge publication. First at the subscriber...got the same error you did. Second at the publisher...it works as you want. updates that include the pre-existing and the new column work correctly.
Not sure why you can't make a change at the subscriber. It might be worth checking out the various paramter to sp_addmergearticle - there might be a way - no guarentees though. (e.g. drop the subscription, add the column, add the subscription but specify no action if the table exists).
You can test this on a single server with a couple of test databases fairly quickly.

Bugger poor reading on my part. I worked it out in the end and that was to disable MSmerge_tr_altertable

DISABLE TRIGGER MSmerge_tr_altertable ON DATABASE

alter table tablethingy add column columnthingy varchar(max) null

ENABLE TRIGGER MSmerge_tr_altertable ON DATABASE

This works fine but you have to remember to perform the actions if you are re snapshotting.

happycat59 One Orange Chip Points: 29388 More actions · Answer 10

Make sure you thoroughly test what you have done. The trigger exists for a reason (someone at Microsoft put it there for a reason). If something goes wrong, you may have trouble convincing Microsoft that they should support your configuration.

david.blythner SSC Enthusiast Points: 143 More actions · Answer 11

Hello

Just to make sure I get this right, I will first describe my case.

I am BI Manager on top of a SAP Installation. We use MS SQL 2005 replication in order to get a db i can query without slowing performance on the live system. This works great.

BUT I would like to do incremental update, meaning that I need a datefield somehow showing me when a row was last changed Updated.

Normally I would add a column with a trigger on the publisher inserting getdate() when updated or inserted.

The thing is, I do not have access to change our sap tables.... So I need to do it on the subscriber only... Can this be done?

happycat59 One Orange Chip Points: 29388 More actions · Answer 12

yes, you certainly can do it.

Replication is quite happy to use custom stored procedures to apply the insert/update/delete transactions to the subscriber.

So, what you will need to do is

- create your target schema with the additional columns you need to track changes

- create the custom sprocs with code to populate the extra columns

- create your publication and specify the use of the custom sprocs

If I were doing this I would create a code generator to do all of this using the table definitions from the publisher.

david.blythner SSC Enthusiast Points: 143 More actions · Answer 13

Many thanks

Is there a step by step somewhere out there?

Otherwise I need to begin from scratch with trial and error.

Perhaps an example of some sort?

br david

happycat59 One Orange Chip Points: 29388 More actions · Answer 14

Not sure whether there are any step-by-step guide out there. It is really a matter of using the article properties to set the name of the sproc you are using. The most important bit is being able to create the schema and the sproc to update the audit info on the subscriber. You should consider writing a script to create you publication. Start by scripting out an existing publication (perhaps with just one article) and then extend that to all of the tables you need