reinitialize subscription

Question

reinitialize subscription

luckysql.kinda

SSCertifiable

Points: 7816
More actions
January 24, 2010 at 7:03 am

#229372

Can anyone tell me what are the instances when we need to reinitialize subscription?
-Lucky

Viewing 10 posts - 1 through 9 (of 9 total)

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

Minaz Amin SSChampion Points: 11052 More actions · Answer 1

luckysql.kinda (1/24/2010)
Can anyone tell me what are the instances when we need to reinitialize subscription?
-Lucky

Would like to know what type of replication you have ?

According to BOL, Reinitializing a subscription involves applying a new snapshot of one or more articles to one or more Subscribers.transactional and snapshot replication allow individual articles to be reinitialized; merge replication requires all articles to be reinitialized.

Reinitialization occurs for one of two reasons:

You explicitly mark a subscription for reinitialization.

You perform an action, such as a property change( Alter a column or add a column or any schema changes to the articles invloved in replication) , that requires a reinitialization.

"More Green More Oxygen !! Plant a tree today"

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 2

When we need to make any schema change then we can issue ALTER command in the publisher and this should get replicated to subscriber. Is this not correct?

What exactly happens when subscription is reinitialized? I feel a new snapshot is created and this is traversed from publisher to subscriber.

-Lk

jshailendra SSCrazy Points: 2260 More actions · Answer 3

Only Insert, Update, Delete commands are traversed from Publisher to Subscriber

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 4

I believe that, in SQL Server 2005 and 2008, alter table commands are replicated to the subscribers for transactional replication. I believe that re-initializing the subscription actually drops and recreates the tables at the subscriber and populates them.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

jshailendra (1/25/2010)
Only Insert, Update, Delete commands are traversed from Publisher to Subscriber

That is applicable only in SQL 2000 or below. From SQL 2005 onwards, alter commands get replicated as well.

-Roy

jshailendra SSCrazy Points: 2260 More actions · Answer 6

jshailendra

SSCrazy

Points: 2260

January 26, 2010 at 8:13 pm

#1109032

Thanks Roy

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 7

Charles and Roy,

I have two small questions for you. These answers are very important for me.

Qu: 1:If alter command is replicated then any schema change would not require any reinitialization of subscriber. So what are the instances when do we still need reinitialization?

Qu: 2:Some days back I read an article which tells how to delete the rows from publisher but not from subscriber. This tells to create one SP on publisher and put the logic of deleting rows in it. Now replicate this and manually delete the logic of deleting rows from the SP at subscriber end. But I feel that when the SP runs at publisher end then the log for deletion should traverse to subscriber and would delete the rows. Does SP run then logs generate only for sp run and not for deletion of rows?

Thanks in advance.

-Lk

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 8

You should only have to re-initialize the entire subscription if, for some reason, it is marked for re-initialization. That could be because of an extended period during which transactions can't be sent to the subscriber. I can't think of any other reason, unless you want to do it. You can drop and add articles without re-initializing and make schema changes.

In SQL Server 2008, in the article properties, you can choose not to replicate delete statements. We don't have a SQL Server 2005 publisher, but I imagine it is probably the same.

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 9

Thanks Charles.

If any sp is executed at publisher then some logs would traverse through the distributor to subscriber. Would log contain the information of SP execution or execution of statements inside the sp? Could you tell me this? Thanks in advance.

-Lk