Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication without need of Snapshot Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 5:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 7, 2014 5:20 AM
Points: 17, 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.
Post #1405351
Posted Thursday, January 10, 2013 5:52 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:36 AM
Points: 711, Visits: 2,211
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
Post #1405364
Posted Thursday, January 10, 2013 5:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1405367
Posted Thursday, January 10, 2013 6:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 7, 2014 5:20 AM
Points: 17, 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.

Post #1405399
Posted Thursday, January 10, 2013 7:04 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, December 19, 2014 9:36 AM
Points: 711, Visits: 2,211
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
Post #1405412
Posted Thursday, January 10, 2013 7:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
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 2008, MVP
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

Post #1405466
Posted Thursday, January 10, 2013 9:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 7, 2014 5:20 AM
Points: 17, 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.
Post #1405516
Posted Sunday, January 13, 2013 7:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 7, 2014 5:20 AM
Points: 17, 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.
Post #1406465
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse