yb751 (6/21/2012)
Michael Valentine Jones (6/21/2012)
In conjunction with this, they could have the web application query the table using the snapshot isolation level (or set the database to read committed snapshot) so that the web app will still be able to see the data that was committed before the update transaction started.
That would eliminate even momentary blocking, and be much simple to implement than a partitioned table.
Note: You can enable the database for SNAPSHOT isolation while the database is online, but cannot be enabled for READ COMMITTED SNAPSHOT while there are users connected.
Another good option. I'm interesting in persuing the partition method simply to give me an excuse to learn more about it. However if it blows up in my face, I'm sure I'll run back to this. 😀
Since one of your main concerns was "highly available", I think you might want to reconsider.
With the partition switching, there will still be unavoidable blocking, since you are making a schema change.
Withe snapshot isolation, the data will continue to be available without blocking.