SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


reinitialize subscription


reinitialize subscription

Author
Message
luckysql.kinda
luckysql.kinda
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: 1570 Visits: 659
Can anyone tell me what are the instances when we need to reinitialize subscription?

-Lucky
Minaz Amin
Minaz Amin
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2582 Visits: 1750
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
luckysql.kinda
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: 1570 Visits: 659
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
jshailendra
Say Hey Kid
Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)

Group: General Forum Members
Points: 666 Visits: 455
Only Insert, Update, Delete commands are traversed from Publisher to Subscriber
Chuck Hottle
Chuck Hottle
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 1211
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
Roy Ernest
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8283 Visits: 6891
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
jshailendra
Say Hey Kid
Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)Say Hey Kid (666 reputation)

Group: General Forum Members
Points: 666 Visits: 455
Thanks Roy
luckysql.kinda
luckysql.kinda
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: 1570 Visits: 659
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
Chuck Hottle
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 1211
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
luckysql.kinda
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: 1570 Visits: 659
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
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