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 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply