Z
Zen
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.
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.