Post reply

Alter a table without re-initializing replication

  • 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?

  • August 12, 2009 at 12:39 pm

    #1038213

    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

  • July 15, 2010 at 3:04 pm

    #1193792

    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

  • July 16, 2010 at 6:54 am

    #1194069

    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

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

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