none
Archive Transnational replication Question RRS feed

  • Question

  • Hello,

    I have a database that is been replicated like a archive data, where we put old data (15 years old)

    Recently my boss ask me to add 4 columns to the archive data. my question is :

    due my source database (publication)  has only the last 3 years and my subscription has all the data (15 years) , when I add those 4 columns and re initialize the replicate , the data will be lost?

    replication type : Transnational 

    Sunday, January 31, 2016 1:47 PM

Answers

  • Hi Luis,
    Please check if the if the "replicate schema changes" enabled on our publisher in transactional replication using the following code.

    use your_publication_database
    select replicate_ddl  from syspublications where name = 'Name_of_your_publication'

    If the replicate_ddl is 1, the "replicate schema changes" is enabled, and in this case, we can add new columns to a table and including columns in an existing publication without reinitializing subscription. When we add new columns to a table, by default, columns are then replicated to all Subscribers, and new columns will be null for historical records, there is no data lost.

    If  the replicate_ddl is 0, you can enable the  "replicate schema changes" using the following code, then add columns to the table and including columns in an existing publication without reinitializing subscription and data losing.

    EXEC sp_changepublication 
        @publication = N'my_publication_name', 
        @property = N'replicate_ddl', 
        @value = 1;

    Otherwise, if you reinitialize subscription, you will lost the data in subscription.
    For more information of adding columns to a table in transactional replication, please review this article .

    Regards,
    Ice Fan 


    Ice Fan
    TechNet Community Support


    Monday, February 1, 2016 9:32 AM
  • If you reinitialize your subscription, it will exactly match the source, which means yes you would only have 3 years.  Replication is not intended for archiving of data as you are using it.  It is meant to match the publisher exactly.

    However, if you are "replicating schema changes" you can simply add the columns in the published database and it will add them to the subscriber.  Make sure they are NULLable.  You do not need to reinitialize to add new columns.

    Monday, February 1, 2016 1:34 PM
  • Yes your archived data will be dropped. It would be best for your to drop the subscription, make the schema changed on the publisher and the subscriber and then add the subscription back, but do not select the initialize data option.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, February 1, 2016 4:13 PM
    Moderator

All replies

  • Hi Luis,
    Please check if the if the "replicate schema changes" enabled on our publisher in transactional replication using the following code.

    use your_publication_database
    select replicate_ddl  from syspublications where name = 'Name_of_your_publication'

    If the replicate_ddl is 1, the "replicate schema changes" is enabled, and in this case, we can add new columns to a table and including columns in an existing publication without reinitializing subscription. When we add new columns to a table, by default, columns are then replicated to all Subscribers, and new columns will be null for historical records, there is no data lost.

    If  the replicate_ddl is 0, you can enable the  "replicate schema changes" using the following code, then add columns to the table and including columns in an existing publication without reinitializing subscription and data losing.

    EXEC sp_changepublication 
        @publication = N'my_publication_name', 
        @property = N'replicate_ddl', 
        @value = 1;

    Otherwise, if you reinitialize subscription, you will lost the data in subscription.
    For more information of adding columns to a table in transactional replication, please review this article .

    Regards,
    Ice Fan 


    Ice Fan
    TechNet Community Support


    Monday, February 1, 2016 9:32 AM
  • Thx for your answer, I ran the  first query and returned 1, so I can add the columns in the replication windows, but an alter shows up , I can re-initialize with problem right?


    Monday, February 1, 2016 12:59 PM
  • If you reinitialize your subscription, it will exactly match the source, which means yes you would only have 3 years.  Replication is not intended for archiving of data as you are using it.  It is meant to match the publisher exactly.

    However, if you are "replicating schema changes" you can simply add the columns in the published database and it will add them to the subscriber.  Make sure they are NULLable.  You do not need to reinitialize to add new columns.

    Monday, February 1, 2016 1:34 PM
  • Yes your archived data will be dropped. It would be best for your to drop the subscription, make the schema changed on the publisher and the subscriber and then add the subscription back, but do not select the initialize data option.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, February 1, 2016 4:13 PM
    Moderator