January 10, 2013 at 5:12 am
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.
January 10, 2013 at 5:52 am
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
January 10, 2013 at 5:58 am
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
January 10, 2013 at 6:46 am
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.
January 10, 2013 at 7:04 am
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
January 10, 2013 at 7:52 am
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
January 10, 2013 at 9:07 am
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.
January 13, 2013 at 7:47 am
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.
Thx again for the discussion and suggestions.
Cheers.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply