September 25, 2006 at 7:35 am
hi
I simply want to add an article to an existing publication WITHOUT creating a new snapshot of the entire database, but rather just a snapshot of the new items.
I can add the article just fine either through the EM or sp_addarticle, but when I come to start the snapshot agent, it copies out the enitre DB. This DB is 150GB and the subscriber is in France (im in UK) so im going to be old(er) and grey(er) by the time the snapshot is copied over.
how do I accomplish this simple, yet seemingly utterly impossible task (as I have been looking and posting all over the place.)
Thanks
Alastair Jones.
September 25, 2006 at 8:44 am
it's really very easy .. here's an example for sql2k adding a table to transactional replication:-
--
-- add table to publication
--
exec dbo.sp_addarticle 'mypublication' ,@article='mytable',@source_table='mytable',@destination_table='mytable',
@force_invalidate_snapshot=1
go
--
-- refresh the subscription
--
exec dbo.sp_refreshsubscriptions 'mypublication'
go
--
--
-- now run the snapshot agent which will only update the object changed/ added new object
--
exec msdb.dbo.sp_start_job @job_name='xxxx 'mypublication'
go
--
get the name of 'mypublication'
--
-- execute within the published database e.g. the database being replicated
-- to obtain the publication name
--
exec dbo.sp_helppublication
go
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 25, 2006 at 8:51 am
ill buy you a beer if it works - cant test it at the moment as my box is being hammered by a previous attempt that then snapped the entire dbase.
cheers
Alastair Jones.
September 25, 2006 at 8:57 am
forgive the newbie - where do I get the job name from?
September 25, 2006 at 9:23 am
As I cant qualify
exec msdb.dbo.sp_start_job @job_name='xxxx
with the actual job name, I run this section alone from the EM...and I get a snap of the entire database....
can you tell me how to fine the job name to start the agent from QM so I can see if this makes a difference...also im running sql 2005 - dont know if that makes a difference in the SP's to run...
Thanks
Alastair Jones
September 26, 2006 at 5:14 am
You can also just add the article through management studio. Publication properties -- Articles.
It prompts you that a new snapshot must be generated, but only the change(s) will be applied to the subscriber.
September 29, 2006 at 9:26 am
alistair - sorry for delay have responded!
you could probably do it through the GUI but it's not an auditable process within a controlled production environment so no use to me other than testing.
ps. this takes out an object
--do this in the publishing database
exec dbo.sp_dropsubscription @publication='mypublication',@article='myobjecttodrop',
@subscriber='MYSERVER'
exec dbo.sp_droparticle @publication='mypublication',@article='myobjecttodrop',
@force_invalidate_snapshot=1
exec dbo.sp_refreshsubscriptions 'mypublication'
go
--
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 17, 2009 at 1:53 pm
colin Leversuch-Roberts-108755 (9/25/2006)
it's really very easy .. here's an example for sql2k adding a table to transactional replication:-
--
-- add table to publication
--
exec dbo.sp_addarticle 'mypublication' ,@article='mytable',@source_table='mytable',@destination_table='mytable',
@force_invalidate_snapshot=1
go
--
-- refresh the subscription
--
exec dbo.sp_refreshsubscriptions 'mypublication'
go
--
--
-- now run the snapshot agent which will only update the object changed/ added new object
--
exec msdb.dbo.sp_start_job @job_name='xxxx 'mypublication'
go
--
Hi,
I tried to add an article withouth re-initializing replication yet it still began to bulk copy all of my data. I followed the instructions above. Any idea what I did wrong?
December 17, 2009 at 10:23 pm
Why is a complete snapshot being generated when a new article is added (SQL 2005)?
--------------------------------------------------------------------------------
This is expected behaviour if you have a merge or snapshot publication. If you have a transactional publication, a snapshot of all articles will always be generated if the immediate_sync publication property is set to true. Typically, the immediate_sync publication property is set to true if you allowed anonymous subscriptions while creating the publication through the CreatePublication wizard. To prevent the complete snapshot, run the script below:
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'immediate_sync',
@value = 'false'
GO
This works.
December 18, 2009 at 2:57 am
I came across with this article on adding an article to existing publication. Might help you.
http://deepakrangarajan.blogspot.com/2009/01/sql-2005-transaction-replication-adding.html
December 21, 2009 at 5:33 am
Suresh B. (12/17/2009)
--------------------------------------------------------------------------------
This is expected behaviour if you have a merge or snapshot publication. If you have a transactional publication, a snapshot of all articles will always be generated if the immediate_sync publication property is set to true. Typically, the immediate_sync publication property is set to true if you allowed anonymous subscriptions while creating the publication through the CreatePublication wizard. To prevent the complete snapshot, run the script below:
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'immediate_sync',
@value = 'false'
GO
This works.
Source:http://www.replicationanswers.com/Transactional.asp%5B/quote%5D
Gotcha -
So if I set immediate_sync to false, then changes are applied to the current snapshot?
December 21, 2009 at 5:50 am
Apollo74x (12/21/2009)
Suresh B. (12/17/2009)
Why is a complete snapshot being generated when a new article is added (SQL 2005)?--------------------------------------------------------------------------------
This is expected behaviour if you have a merge or snapshot publication. If you have a transactional publication, a snapshot of all articles will always be generated if the immediate_sync publication property is set to true. Typically, the immediate_sync publication property is set to true if you allowed anonymous subscriptions while creating the publication through the CreatePublication wizard. To prevent the complete snapshot, run the script below:
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'immediate_sync',
@value = 'false'
GO
This works.
Source:http://www.replicationanswers.com/Transactional.asp%5B/quote%5D
Gotcha -
So if I set immediate_sync to false, then changes are applied to the current snapshot?
No. Changes are not applied to the current snapshot.
It generates new snapshot of only those articles which are added newly to the publication.
For example, if you have an existing publication with 10 table and you add 2 new tables to it. Then run snapshot agent. It will generate snapshot of only those newly added 2 tables.
I have used it many times and it has worked for me.
February 19, 2010 at 7:34 pm
so if the immediate_sync was already set to false and i have transactional replication if i execute this
Exec sys.sp_startpublication_snapshot 'publication'
it will only do snapshot of new articles?
February 27, 2012 at 12:01 pm
Suresh B. (12/21/2009)
Apollo74x (12/21/2009)
Suresh B. (12/17/2009)
Why is a complete snapshot being generated when a new article is added (SQL 2005)?--------------------------------------------------------------------------------
This is expected behaviour if you have a merge or snapshot publication. If you have a transactional publication, a snapshot of all articles will always be generated if the immediate_sync publication property is set to true. Typically, the immediate_sync publication property is set to true if you allowed anonymous subscriptions while creating the publication through the CreatePublication wizard. To prevent the complete snapshot, run the script below:
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'immediate_sync',
@value = 'false'
GO
This works.
Source:http://www.replicationanswers.com/Transactional.asp%5B/quote%5D
Gotcha -
So if I set immediate_sync to false, then changes are applied to the current snapshot?
No. Changes are not applied to the current snapshot.
It generates new snapshot of only those articles which are added newly to the publication.
For example, if you have an existing publication with 10 table and you add 2 new tables to it. Then run snapshot agent. It will generate snapshot of only those newly added 2 tables.
I have used it many times and it has worked for me.
Apart from getting new articles, would it also capture schema changes for an existing article?
February 29, 2012 at 8:47 am
sqldba_newbie (2/27/2012)
...Apart from getting new articles, would it also capture schema changes for an existing article?
I believe it creates "snapshot" of only the new articles.
I believe it does NOT consider any "schema changes" for existing articles.
Schema changes are anyway replicated by transactional replication. Check your publication options. If it is disabled for some reason, enable it.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply