March 8, 2010 at 1:37 pm
Hi all...I just got handed the job of deploying TFS/DBPro packages to our servers. I have had no problems in this new position UNTIL I tried to deploy an ALTER TABLE statement to a replicated table!! I BROKE REPLICATION!! Yikes!! So, of course I have an URGENT request to get some stuff up on TESTBED and it is more alter table statements, mixed in with some other view/proc changes. I am a replication NEWBIE and I need your help!
So...I have been doing some reading today, but wanted to see if I can get some quick answers from you all that have done this before and save me some time that I desparetly need. So, thank you in advance....
My question is this: I found this code (see below)....can I incorporate it WITHIN my other alter script, or do I need to break out the replicated tables being altered and put it in a script by itself??
Next question: Is this syntax done for each table replicated and so I would just append this same code over and over until my changes are complete??
Thank you for any help, I appreciate it!! :w00t:
March 8, 2010 at 1:40 pm
One more piece of info I wanted to share....I opened up the replication script for TESTBED and it has in there the variable @replication_DDL = 1 (I think that was it...). I understand this to be that it would replicated altered tables....but it didn't.....so any thoughts????? Thank you again!!
March 8, 2010 at 1:55 pm
If i understood correct, you are trying to alter a table which is involved in replication. You cannot alter table or do schema changes if the schema changes option is set to false in publisher properties. Change schema changes setting to true if you want to make changes at publisher table and propagate to subscriber, or remove the table from publisher do schema changes and then add back the table to publisher and re initialize subscriber.
EnjoY!
March 8, 2010 at 1:57 pm
Angelindiego (3/8/2010)
My question is this: I found this code (see below)....can I incorporate it WITHIN my other alter script, or do I need to break out the replicated tables being altered and put it in a script by itself??
I see no code, do you have the code?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 8, 2010 at 1:58 pm
Hi There,
Got couple of questions before anyone can give any good info..
1. What kid of replication do you have?
2. You state that you have some code in the message. Cant seem to find it. Can you post it again?
3. What kind of Alter statement was it? (Adding column, removing column etc)
-Roy
March 8, 2010 at 1:59 pm
Angelindiego (3/8/2010)
One more piece of info I wanted to share....I opened up the replication script for TESTBED and it has in there the variable @replication_DDL = 1 (I think that was it...). I understand this to be that it would replicated altered tables....but it didn't.....so any thoughts????? Thank you again!!
That should replicate changes to the subscriber if implemented properly.
exec sp_changepublication @publication = 'pubname'
, @property = 'replicate_ddl'
, @value = '1'
, @force_invalidate_snapshot = 0
, @force_reinit_subscription = 0
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 8, 2010 at 2:00 pm
OOPS, I forgot to paste in the code that I found...sorry!!
exec sp_dropsubscription @publication = 'tTestFNames'
, @article = 'tEmployees'
, @subscriber = 'RSCOMPUTER'
, @destination_db = 'testrep'
exec sp_droparticle @publication = 'tTestFNames'
, @article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @publication = 'tTestFNames'
, @article = 'tEmployees'
, @source_table = 'tEmployees'
exec sp_addsubscription @publication = 'tTestFNames'
, @article = 'tEmployees'
, @subscriber = 'RSCOMPUTER'
, @destination_db = 'testrep'
March 8, 2010 at 2:02 pm
March 8, 2010 at 2:07 pm
You said Broken Replication, what is the error message? Check the constarints too.
EnjoY!
March 8, 2010 at 2:14 pm
In SQL 2005, you can modify tables that are being replicated. For this first you will have to change the publication properties for Replicate Schema Changes to TRUE. Once that is done, you can just do alter table scripts. You do not have to drop the subscription or drop the article.
Once you set that change, you can just give the Alter table. (Some pre-requisites are there)
You can do
Alter Table TableName Add ColumnName int NULL
The pre-requisite is it has to be either a NULL column or should have a default set to it.
You can change the size of a column with no issue if you are doing through the script. But if you try to change it through the SSMS GUI, it will give an error since the GUI tries to drop the table and add the table back when altering.
One thing to watch out for is you cannot add an Identity column to the publisher
-Roy
March 8, 2010 at 2:15 pm
ok, for today's issue....I have a deployment that includes 3 alter tables (2 with add columns, 1 with a drop column and an add column) and add a default constraint to each. Also, 4 views that are being altered (but those don't cause issues). I am just wondering if it can all be done in the same script, or do I need to break out the alter tables stuff????
March 8, 2010 at 2:19 pm
Here is a small piece of advice. Which ever method you choose to do, first test it on a testing environment. You can use your script that you put. That is drop Subscription, drop the article, Alter the table and then add it back to replication or Set the Replicate Schema Change to TRUE and then just give ALTER Table commands through your SSMS Query window.
Dont try to do it in production until you have tested the scripts.
-Roy
March 8, 2010 at 2:19 pm
It doesn't matter, it is your call!
EnjoY!
March 8, 2010 at 2:29 pm
March 8, 2010 at 2:34 pm
It is in the Publication/Properties/Subscription Options. Or as the script says @replicate_ddl = 1.
But by default the setting should be true. That is enabled. I wonder why it is disabled in your setting.
-Roy
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply