Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple Replication Question


Simple Replication Question

Author
Message
UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 697
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
M&M
M&M
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: 2595 Visits: 3898
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

M&M
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3565 Visits: 3107
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.



M&M
M&M
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: 2595 Visits: 3898
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.

M&M
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3565 Visits: 3107
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



rajeshsdba
rajeshsdba
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 127
Hi, you required only shanpshot.

Rajesh Singh
DBA(HCL Comnet)
+91-0560888360
Neeraj Dwivedi
Neeraj Dwivedi
SSC Eights!
SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)SSC Eights! (887 reputation)

Group: General Forum Members
Points: 887 Visits: 1319
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
UncleBoris
UncleBoris
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 697
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
M&M
M&M
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: 2595 Visits: 3898
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.

M&M
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