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

Simple Replication Question Expand / Collapse
Author
Message
Posted Sunday, April 14, 2013 7:03 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Sunday, October 19, 2014 7:20 PM
Points: 100, Visits: 501
I am doing some testing with Replication, I have not used much replication before.

I have set up a Subscription that uses Transactional Replication and works fine when I update or insert.

I added a new table, went to the Publisher and added the table as a new Article.
I could not see the new table in the Subscriber database.

In the end I did a "ReInitialize" at the Publisher with creating a new Snapshot.
I can see the new table and the errors have gone.

Is this the correct way to get the new article to the Subscriber?

When it does this new Snapshot does it consist of the ALL the Articles and data (could be big) or just and changing like the new table?

thanks



Post #1442152
Posted Sunday, April 14, 2013 9:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:12 PM
Points: 2,278, Visits: 3,798
UncleBoris (4/14/2013)
I am doing some testing with Replication, I have not used much replication before.

I have set up a Subscription that uses Transactional Replication and works fine when I update or insert.

I added a new table, went to the Publisher and added the table as a new Article.
I could not see the new table in the Subscriber database.

In the end I did a "ReInitialize" at the Publisher with creating a new Snapshot.
I can see the new table and the errors have gone.

Is this the correct way to get the new article to the Subscriber?

Yes, It wouldn't appear in the subsriber otherwise.


When it does this new Snapshot does it consist of the ALL the Articles and data (could be big) or just and changing like the new table?



It will include all the articles. It is possible to take snapshot of only the added article if you disable some options in publisher and then run snapshot agent.

--Run on your publisher database
EXEC sp_changepublication @publication = 'your publication name',
@property = 'allow_anonymous' ,
@value = 'false'
GO
EXEC sp_changepublication @publication = 'your publication name',
@property = 'immediate_sync' ,
@value = 'false'
GO


Mohammed Moinudheen
Post #1442156
Posted Sunday, April 14, 2013 10:39 PM
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: 2 days ago @ 8:56 PM
Points: 3,033, Visits: 2,636
You could try using "sp_addsubscription" and specifying the name of the article that contains the new table. Follow that with running the snapshot agent for the publication and the new table should appear on the subscriber.

Doing this means that replication only copies the data for the new table to the subscriber which should be quicker than using reinitialise which will copy the data for all tables in the publication.



Post #1442163
Posted Sunday, April 14, 2013 11:02 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:12 PM
Points: 2,278, Visits: 3,798
happycat59 (4/14/2013)
You could try using "sp_addsubscription" and specifying the name of the article that contains the new table. Follow that with running the snapshot agent for the publication and the new table should appear on the subscriber.

Doing this means that replication only copies the data for the new table to the subscriber which should be quicker than using reinitialise which will copy the data for all tables in the publication.


I don't think it works that way.


Mohammed Moinudheen
Post #1442164
Posted Monday, April 15, 2013 1:53 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: 2 days ago @ 8:56 PM
Points: 3,033, Visits: 2,636
mohammed moinudheen (4/14/2013)
happycat59 (4/14/2013)
You could try using "sp_addsubscription" and specifying the name of the article that contains the new table. Follow that with running the snapshot agent for the publication and the new table should appear on the subscriber.

Doing this means that replication only copies the data for the new table to the subscriber which should be quicker than using reinitialise which will copy the data for all tables in the publication.


I don't think it works that way.


Mohammed

try it first - this is how replication works. You can manage replication at various levels of granularity, depending on your needs.

Your method will work but, like I said, it will require the entire publication to be copied to the subscriber which may be undesirable



Post #1442193
Posted Tuesday, April 16, 2013 4:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 6:32 AM
Points: 12, Visits: 126
Hi, you required only shanpshot.

Rajesh Singh
DBA(HCL Comnet)
+91-0560888360
Post #1442671
Posted Tuesday, April 16, 2013 9:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:56 PM
Points: 835, Visits: 1,191
Below are all the steps you have to do.


1)

EXEC sp_changepublication
@publication = 'PublicationName',
@property = N'allow_anonymous',
@value = 'false'
GO

EXEC sp_changepublication
@publication = 'PublicationName',
@property = N'immediate_sync',
@value = 'false'
GO



2) Add table to publication :-

exec dbo.sp_addarticle 'PublicationName' ,@article='TableNeedsToBeAdded',@source_table=' TableNeedsToBeAdded',@destination_table='TableNeedsToBeAdded',
@force_invalidate_snapshot=1
Go

3) Refresh the subscription : -

exec dbo.sp_refreshsubscriptions 'PublicationName'
go

4) Now run the snapshot agent which will only update the object changed/ added new object
exec msdb.dbo.sp_start_job @job_name='JobName'
go


Post #1442830
Posted Tuesday, April 16, 2013 1:35 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Sunday, October 19, 2014 7:20 PM
Points: 100, Visits: 501
I followed the steps at this link "http://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/"
which only by coincidence seems to be by a previous poster on this thread.

The steps are similar to those steps DEVDB highlights but does not include the
exec sp_refreshsubscriptions 'publication' step.

It seems the if you add a new article via the GUI this step is not needed - but the end result is the same (also some of DEVDB's example code is deprecated)

A question: If setting "'allow_anonymous" and "immediate_sync" allows only the new article to be replicated instead of the entire snapshot. Then why would these settings "FALSE" not be the default?
I would have thought this would be the preferred option for most users.

thanks
Post #1442975
Posted Tuesday, April 16, 2013 5:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:12 PM
Points: 2,278, Visits: 3,798
A question: If setting "'allow_anonymous" and "immediate_sync" allows only the new article to be replicated instead of the entire snapshot. Then why would these settings "FALSE" not be the default?
I would have thought this would be the preferred option for most users.


Settings are false by default. Refer link : Replication settings

But I noticed that these settings get set to TRUE if we use the GUI for setting up replication.

When we use sp_addpublication sp, both are FALSE.


Mohammed Moinudheen
Post #1443053
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse