June 28, 2006 at 12:55 pm
I know this is a dumb question, but I need a sanity check. We have a procedure here that whenever we add a column (or any other schema change) we must:
Isn't this overkill? In my test setup I can just add columns while the agents are running and they just replicate. Am I missing something important?
June 28, 2006 at 1:28 pm
I use the following to add a column to transaction replication.
sp_repladdcolumn @source_object = '<table name>',
@column = '<new column name>',
@typetext = '<datatype> <NULL or NOT NULL> <DEFAULT 1>',
@publication_to_add = '<publication name>',
@force_reinit_subscription = 1
The typetext is the datatype of the column and whether or not you will allow nulls and finally if you would like to set a default value for your column. For instance if you add a column the is a bit datatype that does not allow null and has a default value of 1, the syntax would be @typetext = 'bit NOT NULL Default 1'. You must use the last statement @force_reinit_subcription = 1. This will force the subscription to be re-created. After you run the stored procedure, you will need to go to replication monitor for the publication and right click on the snapshot and select start agent. This will create a snapshot of only the tables (articles) that you have changed.
David
June 28, 2006 at 1:47 pm
Yes, but if I don't want to force a snapshot, then I can to this on my test instance:
sp_repladdcolumn 'TableName','ColumnB','int NULL','all',null,0,0
I see it actually go from my publisher to my subscriber without having to run the majorly resource-intesive snapshot. But is there a risk with having an out-of-date snapshot like that?
Thanks for the input.
June 28, 2006 at 1:54 pm
When the snapshot runs it will only create a snapshot of the table with the new column. It will not create a snapshot of your entire database.
I am not sure why your test server replicates the new column without reinitializing the snapshot. I had that happen once but it has never happen again. I always has to reinitialize the snapshot. The snapshot should not be resource intensive unless the the table that you are changing has alot of rows.
David
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply