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


Replication without need of Snapshot


Replication without need of Snapshot

Author
Message
heino.zunzer
heino.zunzer
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 104
Hi,

I hope someonecan help me with this.

We have a production server with a VLDB on it (~ 7 Billion rows, ~ 4 TB).
We are now setting up a (new) Test environment and for specific reasons we want some of the LIVE OLTP data to be replicated via transactional replication.
I only want to replicate new data coming in, not the data that is already there.

So I don't want to generate a Snapshot (also because it would be way too large). I just want the records from one table coming in after I switch on the replication to be replicated to the (initially empty) test server (schema and everything of course already set up).

Will this work if use this to create my publication:


exec sp_addpublication
@publication = N'Test'
, @description = N'Publication of database ''Test'', Table testTab.'
, @sync_method = N'concurrent'
, @retention = 0
, @allow_push = N'true'
, @allow_pull = N'true'
, @allow_anonymous = N'false'
, @allow_subscription_copy = N'false'
, @add_to_active_directory = N'false'
, @repl_freq = N'continuous'
, @status = N'active'
, @independent_agent = N'true'
, @immediate_sync = N'false'
, @allow_sync_tran = N'false'
, @autogen_sync_procs = N'false'

, @allow_queued_tran = N'false'
, @allow_dts = N'false'
, @replicate_ddl = 1
, @allow_initialize_from_backup = N'false'
, @enabled_for_p2p = N'false'
, @enabled_for_het_sub = N'false'



Will this create a publication without the need of a snapshot and will the replication work as I need it?

I'd be very thankful if someone could confirm this for me.

And of course I'll try it on a smaller database in my dev-lab before hand. Just want to know if I am on the right track.
s_osborne2
s_osborne2
SSC Eights!
SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)

Group: General Forum Members
Points: 997 Visits: 2292
Hi,

I'm not sure whether that would work or not. If you've got a smaller database to test it on then go for it, you've got nothing to lose if it's just a test db.

We have a DB that's in the region of 1TB and we replicate parts of that. We initialise it from a backup and then drop the objects we're not replicating from the subscriber. If you went for this method it would rely on you having 4TB free space on your destination server of course...

Thanks,

Simon



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87079 Visits: 45267
A replication publication has to be initialised. Your choices are init from snapshot or init from backup, but there's no option of 'do not initialise'

If you're just replicating one table in a large database, then I recommend init from snapshot, as it will just snapshot the tables involved in the replication.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


heino.zunzer
heino.zunzer
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 104
Thanks for the answers.

Maybe I wasn't clear enough on what I intend to do.
It is not about replicating only a few tables instead of all.

It is about replication only parts of one table. To be more specific:

The table Test1 has about 6 Billion rows. I need to replicate this table, but only new records coming in, not the already existing 6 Billion rows.

The target database will later remove everything older than a month but new (live) records should keep coming in through transactional replication. So the target database will keep a rolling 30 days in the table.

I will test anyway, of course. Just wondering if a solution with replication is the right approach here.
s_osborne2
s_osborne2
SSC Eights!
SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)SSC Eights! (997 reputation)

Group: General Forum Members
Points: 997 Visits: 2292
Hi,

OK, so couldn't you just add the table you want to replicate and then use a row and column filter? I'm not sure how you'd do that in script but it's available through the GUI. You could always then generate the scripts for the publication and see how it's done in code and then script it as necessary.

Thanks,


Simon



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87079 Visits: 45267
heino.zunzer (1/10/2013)
Just wondering if a solution with replication is the right approach here.


Doesn't sound like it. I'd be considering service broker here.

Replication replicates everything, inserts, updates and deletes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


heino.zunzer
heino.zunzer
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 104
That (Ins, Upd, Del) wouldn't actually be a problem because the table only has inserts, but I get your point.

I will look into Service Broker.
heino.zunzer
heino.zunzer
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 104
Hi guys,

after some testing and som trial-and-error in my test lab I found a way to do replication without snapshot or backup initialisation.

The trick is basically
a) not to initialise the snapshot
b) set the subspription's synchronisation type to 'replication support only'
c) (if needed) Set the distribution agent's profile to ignore consistency errors

On my blog you can find a detailed description on how to do that.
http://wp.me/p2fAyJ-4w

Thx again for the discussion and suggestions.

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