January 27, 2006 at 8:57 am
Hello Folks,
My database environment has a replication set-up and this database is updated every week with new code values and also schema changes. Schema changes are basically adding new columns to tables and new tables which are in the replication. I am able add these new columns to related tables on both servers but I don't know how to bring the new columns into replication. I ilustrated a scenario to my environment.
Servers: SERVER1 (Main Database Sever), SERVER2 (Replication Database Server)
Database Name: DB1 on both servers.
Table Name: TABLE1 which is in the replication
Now, a new column name NEWCOLUMN1 needs to be added to TABLE1 and it can be done using ALTER TABLE TABLE1 ADD NEWCOLUMN1 VARCHAR(10) on the both servers
But from here I don't know what to do to keep this on replication. Similarly sometimes I need to add a new table to the database then I don't know how to add that new table to replication set-up. I really appreciate your thoughts and suggestions on this.
Thanks for your time and help.
BK
January 28, 2006 at 8:51 pm
After adding the new column only at the publisher you need to reinitialize the article, so that SQL will genereate a new snapshot wich will include the added column in the creation script.
Just execute
USE DB1
GO
EXEC sp_Reinitsubscription @Publication = 'PubName', @article = 'Table1', @subscriber = 'Server2', @Destination_DB = 'DB1'
Run the snapshot agent, wich will run for only one article and then (in case the distribution agent is not already running), run the distribution agent to push the changes at the subscriber.
The distributor agent will apply the new snapshot at the subscriber and recreate the table with the new column, wich is already included in the replication schema.
Now, if the table is too big and you don't wont to apply a new snapshot, because it will drop and recreate the table at the subscriber, then you should consider using the sp_repladdcolumn stored procedure. This will add the new column at the publisher and replicate the change to the subscriber with out the need to generate a new snapshot.
But, Microsoft says the you first need to stop all transactions at the publisher, because transactions with the table without the new column and with the new column included can get mixed and generate an error. You can solve this by manually modifying the stored procedure used by replication at the subscriber (Generally sp_MSins_tablename, sp_MSupd_tablename) by adding a new parameter wich simulated the new column and adding a default value of null to the parameter.
Example,
ALTER PROCEDURE sp_MSins_Table1
@Col1 INT, @Col2 VARCHAR(10), @NewCol VARCHAR(20) = NULL
AS
....
If you need more help with this just ask!
Hope it helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply