Wednesday, March 7, 2012

Fast recovery in SQL Server 2005

I had a question that no one seemed to be able to answer at the launch event in Detroit. When SQL Server lets me on the database after undo (like Oracle), how does it handle the pages that need to be redo? In Oracle, if the process is not at the page that needs redo, it gets that page and brings it to the front of the recovery queue. Obviously, if someone needs the page before it is recovered, it cannot be given to the user, but that need puts that block (or page, not sure how granular it is) right at the start. In SQL Server 2005, what happens? Are the pages just not available until they are redone? And if someone wants data on that page before, will SQL Server 2005 go to it earlier than one that isn't being requested, or are they just queued up? And if they are queued up, what is the algorithm for that. Thanks.SQL2005 makes the database available after 'redo' phase is complete and after acquiring locks for the transacitons that need to be 'undone'.

Here is from BOL:
"The undo (roll back) phase is the last phase of a restore sequence. The undo phase ends a restore sequence by rolling back any uncommitted transactions....."

Thanks,|||Additional info. Here is cut/paste from High Availability presentation.

Restart Recovery has two phases:
?REDO: rolls forward committed transactions
?UNDO: rolls back any incomplete transactions
?On the Enterprise Edition of SQL Server 2005, users are allowed access after REDO
So the point is REDO phase is done first.

Thanks,|||So I guess my question is those people that want pages for which locks are held for undo, when do they get to get to those pages. When the whole undo is finished for all locked pages, when their particular page of interest is recovered? As well, if you locked 5 pages for undo, and SQL Server processed undo in page order 1,2,3,4,5 -- but you had TX's waiting on 5 and the process to undo was on page 2, could SQL Server bump to page 5 and undo that one, and then go back to 3 to service that waiting user faster?

I'm just trying to understand the model compared to Oracle, that's all. We're trying to prove the enterprise-like feature that 2005 has. And even if it isn't the same, I just need to arm myself. This is better than what DB2 has ..|||Undo is done in the reverse order of the transaction log generation. No, the undo will not go to page 5 and then go to page 3 if there is some transaction wainting to access page 5.

Thanks

No comments:

Post a Comment