April 14, 2013 at 7:03 pm
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
April 14, 2013 at 9:24 pm
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
April 14, 2013 at 10:39 pm
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.
April 14, 2013 at 11:02 pm
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
April 15, 2013 at 1:53 am
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
April 16, 2013 at 4:20 am
Hi, you required only shanpshot.
Rajesh Singh
DBA(HCL Comnet)
+91-0560888360
April 16, 2013 at 9:57 am
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
April 16, 2013 at 1:35 pm
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
April 16, 2013 at 5:08 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply