September 4, 2010 at 9:22 am
Hi all,
I did not found a section for sql 2008 replication so i posted here because i think it should be similar to sql 2005.
I did setup a transactional replication using the "initialize with backup" option and everything is working fine
but i do have some questions to details that i still don't understand how those works and i really hope i can get some answers
For example:
1) What is the purpose of the snapshot agent job when replication is done with "initialize with backup"?
I really don't understand what snapshot agent job do when the "initialize with backup" option is used.
2) Do i need snapshot folder if i can't reinitialize the subscription?
For example after my replication was created and working fine, i right click on publication in SSMS,
then choose "use a new snapshot" and "generate the new snapshot now" but i don't see any activity.
3) Can replication with "initialize with backup" be created without the snapshot agent job and the snapshot folder?
4) How do i add an article to the existing replication? Do i need to reinitialize from the backup file again after an article is added?
Thanks
September 6, 2010 at 2:34 am
You can find some useful information about the Initialize with Backup at http://gnawgnu.blogspot.com/2009/11/sql-2008-transactional-replication-and.html
) What is the purpose of the snapshot agent job when replication is done with "initialize with backup"?
>> As far as I know the snapshot agent doesn't play any part when replication is setup with this option. However I don't think you can do anything to stop the snapshot agent job from being created. The "initialize with backup" is an alternate way of setting up replication (like "replication support only") and the snapshot agent is part of the general replication setup.
2) Do i need snapshot folder if i can't reinitialize the subscription?
>> If you are going to re-initialize using the initialize with backup option then you don't need the snapshot folder.
3) Can replication with "initialize with backup" be created without the snapshot agent job and the snapshot folder?
>> It can be - but again - as far as I know I don't think you can setup replication without having the snapshot agent being created by default (I might be wrong). Also - if you don't explicitly specify a snapshot folder when setting up replication then the setup defaults to the "ReplData" folder under the SQL Server installation folder.
4) How do i add an article to the existing replication? Do i need to reinitialize from the backup file again after an article is added?
>> You can add an article to an existing replication setup that has been reinitialized from backup. The steps are:
a) Copy over the table and existing data into the subscriber
b) Run the sp_addarticle SP to add the article to the publication
c) Run the sp_refreshsubscriptions to refresh the subscription to pick up the new article
I'd recommend you test out this approach to see if it works. Also if there is activity on the publisher that affects the new table being added after the table has been copied over to the subscriber and before it is added to the publication (huh? :blink:)then you have to manaully synch in those changes.
A recommended best practice is to script out your entire replication setup so that you can run it again in case you need to. In our case we tend to add/remove articles in our script and setup replication again in case we need to make changes (this is mainly because we had issues trying to add an article to an existing subscription or to have an extra publication that is initialized via a snapshot into which we can add articles).
Also - keep an eye out for the potential performance implications of having the Initialize with Backup option on after the synching of the subscriber is done. We faced performance problems with this option enabled and once we turned it off after the initial synch was done (using sp_changepublication) things went fine. This is also touched upon in http://msdn.microsoft.com/en-us/library/ms152560.aspx
September 7, 2010 at 10:37 am
Great!, Thank you so much for an explanations and links.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply