SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


add article to replication without snapshotting entire DB.


add article to replication without snapshotting entire DB.

Author
Message
alastair Jones
alastair Jones
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 7

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.


colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4683 Visits: 715

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



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
alastair Jones
alastair Jones
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 7

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.


alastair Jones
alastair Jones
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 7
forgive the newbie - where do I get the job name from?
alastair Jones
alastair Jones
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 7

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


Steve-3_5_7_9
Steve-3_5_7_9
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1464 Visits: 1599

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.





colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4683 Visits: 715

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



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Apollo74x
Apollo74x
SSC-Enthusiastic
SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)SSC-Enthusiastic (156 reputation)

Group: General Forum Members
Points: 156 Visits: 423
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?
Suresh B.
Suresh B.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1942 Visits: 5326
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
ps.
ps.
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3251 Visits: 3668
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



Pradeep Singh
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