Alter a table without re-initializing replication

Question

Alter a table without re-initializing replication

Apollo74x

Ten Centuries

Points: 1250
More actions
August 12, 2009 at 10:00 am

#208559

Is this even possible? If so, then if someone can shoot me a link that would be great.
I'm no guru when it comes to replication but in our environment, we have non-clustered indexes on the replicated database that are different from the primary database. The replicated database is primarily used for reporting so the indexes are built to accommodate those widely used reports.
In the past when we've re-initialized replication, we would then have to recreate those indexes. I would like to avoid this step especially since the one table that is being altered is not even a primary table.
Any suggestions?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

In SQL2005, You can use an ALTER statement and that does not resnapshot. It will just alter the table in the other end. If you are using SQL 2000, you will have to use the replication command itself. But both of them from my limited knowledge does not do snapshots or reinitialization.

-Roy

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

OK, just to see if I get this. Am a developer, not hardcore dba.

Does this mean that you can add a new column to the target DB F.ex. last changed Date.

Add a trigger to the targetDB.TargetTable that writes getdate() on inserts updates?

We do not have access to the source db, since this is hosted out-house.

Please advise, this would be a GREAT help.

br david

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

You can do it.... BUT

If you do not have access to source table that means there is a difference between Publisher and the subscriber table. This could cause problems when source table is altered in the future.

What you can do is to alter the stored procedures used by replication that update/insert data to that table to handle your new table. But every time an alter is done to the source, you will have to make sure that you have a back up of the data in your target (Subscriber) before a snapshot is generated. This is applicable when you do a re initialization.

The stored procs that you might have to alter will have a naming convention like this.

dbo.sp_MSins_Tablename

dbo.sp_MSUpd_TableName

Please test it properly before you start trying it out on your production system.

-Roy