none
How to add articles to replication without generating a snapshot? RRS feed

  • Question

  • I am trying to figure if there is a way to add articles to an existing trasactional replication without generating a full snapshot. I want to say I was able to do this in SQL Server 2000. I know in SQL Server 2000 when I dropped and added an existing article, I was able to generate a snapshot for just the one article being added. After the snapshot was run it would say "A snapshot of 1 article was generated." Is this functionality not available in 2005?

     

    I am using custom replication procedures to populate custom tables and I cannot generate the full snapshot because it takes about 2 hours to finish. The DB is approximately 35GB in size.

     

    Any suggestions will be greatly appreciated.

     

    Sunny.

    Tuesday, December 9, 2008 6:52 PM

All replies

  • This is possible in SQL 2005.  Here is an example.

    reate database paul
    GO
    create database PaulSub
    GO
    Use Paul
    GO
    create table table1(pk int not null primary key, charcol char(20))
    GO
    sp_replicationdboption 'paul','publish','true'
    GO
    sp_addpublication 'paul',@status='active'
    go
    sp_addpublication_snapshot 'paul'
    GO
    sp_addarticle 'paul','table1','table1'
    GO
    sp_addsubscription 'paul','table1',@@servername,'paulsub'
    GO
    --ensure the snapshot is deployed
    select * from paulsub.dbo.table1
    --creating table2
    create table table2(pk int not null primary key, charcol char(20))
    GO
    sp_addarticle 'paul','table2','table2'
    GO
    sp_refreshsubscriptions 'paul'
    GO
    sp_addsubscription 'paul','table2',@@servername,'paulsub'
    GO
    --ensure the snapshot is deployed
    select * from paulsub.dbo.table2
    --the message I see in the distribution agent is for 1 table - table2.


    Tuesday, December 9, 2008 7:30 PM
    Moderator
  • Is there a publucation configuration option that controls the deafult behaviour? I was able to add an article to the existing SQL2005 publicaton before, run the snapshot agent on the distibutor and it would only do that one table.
    This time I did exactly that and witness the entire snapshot being regenirated.

    Thanks,
    Igor
    Tuesday, January 20, 2009 7:59 PM
  • This is possible in SQL 2005.  Here is an example.

    reate database paul
    GO
    create database PaulSub
    GO
    Use Paul
    GO
    create table table1(pk int not null primary key, charcol char(20))
    GO
    sp_replicationdboption 'paul','publish','true'
    GO
    sp_addpublication 'paul',@status='active'
    go
    sp_addpublication_snapshot 'paul'
    GO
    sp_addarticle 'paul','table1','table1'
    GO
    sp_addsubscription 'paul','table1',@@servername,'paulsub'
    GO
    --ensure the snapshot is deployed
    select * from paulsub.dbo.table1
    --creating table2
    create table table2(pk int not null primary key, charcol char(20))
    GO
    sp_addarticle 'paul','table2','table2'
    GO
    sp_refreshsubscriptions 'paul'
    GO
    sp_addsubscription 'paul','table2',@@servername,'paulsub'
    GO
    --ensure the snapshot is deployed
    select * from paulsub.dbo.table2
    --the message I see in the distribution agent is for 1 table - table2.

    This doesnt work for me .After executing this

    sp_addsubscription 'paul','table2',@@servername,'paulsub'

    i get an error 'Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533
    Specify all articles when subscribing to a publication using concurrent snapshot processing."

    Has this worked for you? Please let me know on how to do so. Thanks


    • Proposed as answer by alex chianuri Wednesday, September 12, 2012 3:42 PM
    • Unproposed as answer by alex chianuri Wednesday, September 12, 2012 3:42 PM
    Monday, February 27, 2012 6:47 PM
  • What version of SQL Server are you running?

    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 27, 2012 8:07 PM
    Moderator
  •                  Hi ,

          I was learn from mssqltips.com . And they given very clear steps on that.

             http://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/ 

          try to utilize above link....

             -------------------------------------------------------------------

            Regard's | Ramu A

    • Proposed as answer by RamuAnnamalai Thursday, September 13, 2012 4:41 AM
    Thursday, September 13, 2012 3:52 AM