Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding new article to existing publication


Adding new article to existing publication

Author
Message
UKT
UKT
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 27
I have my databases running on SQL Server 2005.

I have Transactional replication setup between 2 databases - source being my transaction db and the target being the reporting database.

I am going to add a new article to the existing publication, and my question over here is when I add the the new article do I have to generate a new snapshot and then reintialize the subscription.

And also when I am generating the new snapshot will I be able to access both the source and the target databases.

Thank you all in advance.
winash
winash
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1606 Visits: 1883
Adding/Dropping articles from existing publications: http://technet.microsoft.com/en-us/library/ms152493(SQL.90).aspx

When you are generating the snapshot you will be able to access both the publisher and subscriber - from what I remember the snapshot agent takes a schema lock on the tables for which the snapshot is being generated and if the snapshot generation process takes a very long time then you might see blocking issues on the publisher.



UKT
UKT
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 27
Thank you very much.

So, will it generate snapshot only on the new article or all the articles which are getting replicated.

Thank you again for the reply.
winash
winash
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1606 Visits: 1883
The snapshot will be generated for all articles being replicated (i.e. all the existing articles plus any new articles being added to the publication) - however the distribution agent will only create the new article(s) and copy in the data for the new article(s).

If you check on replication monitor - in the Snapshot Agent tab you'll see the snapshot for all articles being generated and if you go to the distribution agent (the "Distributor To Subscriber History" tab) you'll see that only the schema and data for the new articles are applied.



MysteryJimbo
MysteryJimbo
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1924 Visits: 15339
If you add the article using scripts and then generate the snapshot, the snapshot agent should only add the new articles and limit the impact to both the publisher and subscriber to just the new article.

http://support.microsoft.com/kb/830210

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

exec sp_addarticle
@publication = N'', @article = N'',
@source_owner = N'dbo', @source_object = N'',
@destination_table = N'',
@type = N'logbased', @creation_script = null, @description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_<table>',
@del_cmd = N'CALL sp_MSdel_<table>',
@upd_cmd = N'MCALL sp_MSupd_<table>',
@filter = null, @sync_object = null
GO


exec sp_addsubscription
@publication = N'',
@subscriber = N'',
@destination_db = N'',
@subscription_type = N'Push', @sync_type = N'automatic',
@article = N'all', @update_mode = N'read only', @frequency_type = 64, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4,
@frequency_subday_interval = 5, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @active_start_date = 0,
@active_end_date = 0, @offloadagent = 0, @dts_package_location = N'Distributor'
GO

Welsh Corgi
Welsh Corgi
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8534 Visits: 4893
MysteryJimbo (10/6/2010)
If you add the article using scripts and then generate the snapshot, the snapshot agent should only add the new articles and limit the impact to both the publisher and subscriber to just the new article.

http://support.microsoft.com/kb/830210

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

exec sp_addarticle
@publication = N'', @article = N'',
@source_owner = N'dbo', @source_object = N'',
@destination_table = N'',
@type = N'logbased', @creation_script = null, @description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F3, @status = 16, @vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_<table>',
@del_cmd = N'CALL sp_MSdel_<table>',
@upd_cmd = N'MCALL sp_MSupd_<table>',
@filter = null, @sync_object = null
GO


exec sp_addsubscription
@publication = N'',
@subscriber = N'',
@destination_db = N'',
@subscription_type = N'Push', @sync_type = N'automatic',
@article = N'all', @update_mode = N'read only', @frequency_type = 64, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4,
@frequency_subday_interval = 5, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @active_start_date = 0,
@active_end_date = 0, @offloadagent = 0, @dts_package_location = N'Distributor'
GO


Could you please clarify that?

When I add an article thru the GUI it generates a full snapshot?



http://dba.stackexchange.com/questions/12725/add-article-to-transactional-publication-without-generating-new-snapshot


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
JeremyE
JeremyE
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4081 Visits: 4042
There are 2 options that need to be set to false for the publication in order to add an article and only snapshot that article vs the entire publication.

immediate_sync and allow_anonymous


This article explains it well: https://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/
Welsh Corgi
Welsh Corgi
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8534 Visits: 4893
JeremyE (10/2/2015)
There are 2 options that need to be set to false for the publication in order to add an article and only snapshot that article vs the entire publication.

immediate_sync and allow_anonymous


This article explains it well: https://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/


Thank you Sir! :-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Welsh Corgi
Welsh Corgi
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8534 Visits: 4893
Is it possible to add more than 1 article at a time without having to do a full snapshot?

I did it before and had nixed results.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Welsh Corgi
Welsh Corgi
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8534 Visits: 4893
I'm replicating from 3023R2 to 2914.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search