Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

reinitialize subscription Expand / Collapse
Author
Message
Posted Sunday, January 24, 2010 7:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, Visits: 659
Can anyone tell me what are the instances when we need to reinitialize subscription?

-Lucky
Post #852722
Posted Sunday, January 24, 2010 8:30 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 3:34 AM
Points: 549, Visits: 1,554
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"
Post #852815
Posted Monday, January 25, 2010 3:12 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, 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
Post #852897
Posted Monday, January 25, 2010 3:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
Only Insert, Update, Delete commands are traversed from Publisher to Subscriber
Post #852901
Posted Monday, January 25, 2010 11:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:06 PM
Points: 198, Visits: 1,129
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.
Post #853178
Posted Monday, January 25, 2010 11:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:04 AM
Points: 3,313, Visits: 6,718
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
Post #853191
Posted Tuesday, January 26, 2010 8:13 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 7:12 AM
Points: 366, Visits: 455
Thanks Roy
Post #854077
Posted Tuesday, January 26, 2010 9:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, 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
Post #854097
Posted Wednesday, January 27, 2010 6:34 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:06 PM
Points: 198, Visits: 1,129
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.
Post #854328
Posted Wednesday, January 27, 2010 7:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 12:39 PM
Points: 310, 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
Post #854402
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse