Hi,

Since I don't know which group would be a right newsgroup to post for this
problem and most likely that it would be involved with expertise coming from
residents of different  newsgroups, hope multiple-group posting is ok.

Solving this problem means a lot to me and my team. Thank you very much for
any suggestion or advice you may have.

I have a set of related tables A, B, C in my sqlserver db (my code is in C#)
and I want to keep multiple snapshots through out.  A, B, C are large tables
and growing.

Scenario:
8:00:00am: A=a0, B=b0, C=c0; notation a0,b0,c0 are the data of version 0.
8:00:02am: A=a1, B=b1, C=c1; userFoo has made some change to the content
8:00:32am: my code detects that there is idle to the tables, that is there
is no change to it for 30 seconds, I want to take a snapshot of a1,b1,c1 and
store it in tables X,Y,Z for further processing that can take up 10-20
minutes. Copying or replicating the tables can take 5-10 minutes. After that
I plan to save the *result* of processing (denormalizing etc) X,Y,Z into
another set of tables and my other code would read off there to efficiently
serve data-mining for userBar.
8:00:40am: A=a2, B=b2, C=c2; userCar just made some more changes, notice
that this can happen while we are still taking the snapshot of a1,b1,c1.
That is the core of the problem.
8:01:10am: 30 second idle detected but I want to release only a version at
most for every 20 minute, I'll pass
8:01:20am: A=a3, B=b3, C=c3; userFoo just made another change
8:01:50am: pass another idle
8:21:20am: if a1,b1,c1 has been released, it's time to release new version,
repeat what needs to be done at 8:00:32am.

Would any experienced folk explain to me if possible to take the snapshot of
db like at 8:00:32am and the db still accepts changes userCar made at
8:00:40am?

I was thinking of using replication, as it seems to send commands over to
another database incrementally, I can detect idle at the subscriber and if I
can programmatically stop the subscriber for 10-20 minutes until I get a
good&full snapshot then I'll enable it back to so it can receive the
incremental update waiting in the queue. Is that even possible?
Programmatically and temporarily stopping a replication process is something
I never heard of.

Any other methods, suggestion, or advice?  Thank you very much again for
looking into this.

You might use transactional replication for this and trigger the
distribution agent when appropriate. The problem would be that the filter
would have to be dynamically changed to prevent new changes coming through.
Alternatively you might want to look at database snapshots or log shipping
both of which would effectively filter the data and prevent concurrent data
coming accross.
  Cheers,
           Paul Ibison SQL Server MVP, www.replicationanswers.com
             (recommended sql server 2000 replication book:
             http://www.nwsu.com/0974973602p.html)

(Followup set to microsoft.public.sqlserver.server only)

On Mon, 24 Apr 2006 11:07:19 -0700, Zen wrote:

Hi Zen,

Are you using SQL Server 2005? If so, check out the new database
snapshot feature. I think that this is exactly what you're looking for.

You can read about database snappshots in Books Online. If you need more
information, let me know!

returning SCOPE_IDENTITY() in Insert command with C# DataSets
TableAdapters and true N-Tier
Error accessing MS Access database with Linked Tables on fileshare
A problem with the datagrid
VB.NET create JET database without ADOX