You can watch the demo that I worked on here: http://www.mssharepointconference.com/pages/keynote.aspx

Skip through to 42 minutes to see the 10 minute demo.

Watch as SharePoint 2010 handles the network cable to the SQL Server being disconnected and fails over to a secondary SQL Server machine thanks to SQL "Denali" Always On.