As one of the high availability options in database used by SharePoint 2010 application, we can set the SQL Server database with SQL high availability mirroring (synchronous mode) or SQL failover cluster. To get in more detail on SQL high availability mirroring, on the SharePoint application side, it's automatic detection if there's failover happened on the mirrored database. Sharepoint will always connect to the prinicple database, as mirror database won't be accessible.
You can refer to my previous article on how to set failover instance on sharepoint database or refer to the following article Plan for availability (SharePoint Server 2010)
But, what about the sql server mirroring side once the failover happen? will it be able to re-syncronize back or do we need to setup the mirroring from the scratch once the failover happened ?
In high availability mirroring (mirroring with witness server), when failover happened because the principle server is down,the witness server and mirror server will form a quorum to set the mirror server to be the new principle server and the mirror state will be in disconnected state, and even after long times/sometimes, when the old principal come back online, it will check with its mirroring partner/new principal about the mirroring state thru mirroring LSN (Log Sequence Number) and will try to get the mirroring state to be in synchronized state by redo-ing the transaction logs thru mirroring LSN. Though the database role (principle/mirror) won’t change automatically.
You can get more details information on how SQL Server mirroring and failover work in the following article : Database Mirroring in SQL Server 2005