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

Process for Dropping & Readding articles in Trasnsreplication Expand / Collapse
Author
Message
Posted Monday, July 28, 2014 5:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 22, 2014 9:07 AM
Points: 2,405, Visits: 996
Hi,

I have a Publisher (Pub_A) with two subscribers (Sub_A/Sub_B), both are transactional replication with pull subscriptions.

The publication has ten articles. I have a requirement to drop two articles, add a column to each, then add them back in to the publication. Just looking for a sanity check for the correct process.

1. Drop subscription SubA & SubB
2. Drop articles at PubA
3. Make changes
4. Add articles to PubA
5. Add pull subscriptions on SubA/SubB
6. Generate snapshot for new articles only.


Thanks

qh


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1596785
Posted Monday, July 28, 2014 11:13 AM This worked for the OP Answer marked as solution


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: Yesterday @ 7:56 AM
Points: 3,941, Visits: 7,163
Seems about right but would strongly encourage you to verify that the anonymous/immediate_sync options have been set properly, if they are not set to 0, when you trigger the snapshot...it will create snapshots for all articles (not just the two you want).

You can check this by running
/* On the distributor run to verify the publishers - make CERTAIN allow anonymous/immediate_sync are all ZEROS */
SELECT publisher_id, publication_id,
publisher_db ,
publication , allow_anonymous, immediate_sync
FROM distribution. dbo.MSpublications WHERE publication_type = 0 --> 0 for transactional, 1 for snapshot

/* If IMMEDIATE_SYNC is not zero, run the following on the ditributor */
EXEC sp_changepublication @publication = 'YourPub', @property = 'allow_anonymous', @value = 'false'
GO
EXEC sp_changepublication @publication = 'YourPub', @property = 'immediate_sync', @value = 'false'
GO



______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1596979
Posted Tuesday, July 29, 2014 3:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 22, 2014 9:07 AM
Points: 2,405, Visits: 996
Cheers MDJ.

qh


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1597205
Posted Thursday, July 31, 2014 8:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
Will data be modified during the process and are you just adding a column?

If the data is static and will remain static then you can skip the snapshot generation step depending on the size of the table.
Post #1598295
Posted Friday, August 1, 2014 8:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 22, 2014 9:07 AM
Points: 2,405, Visits: 996
MysteryJimbo (7/31/2014)
Will data be modified during the process and are you just adding a column?

If the data is static and will remain static then you can skip the snapshot generation step depending on the size of the table.


No, data will remain static and snaphots are not used here.

All sorted now, thanks.

qh


SQL 2K acts like a spoilt child - you need to coax it round with lollipops.
Post #1598711
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse