Verify SQL Server Mirroring failover is successful

We have database mirroring configured as "high safety without automatic failover" on 23 databases. Both partners contain principal and mirror databases. I created two scripts that would failover either mirror or principal with either a graceful or forced switch.

How would I check the databases to ensure they are online and traffic is being redirected? I am trying to keep this all T-SQL and hopefully could just add something to the scripts I have already created that would run the check.

From serverfault Ddono25

• If you're trying to do it in T-SQL, that means connecting through a single SQL Server. I'm guessing you're running a set of monitoring scripts from a different third server to watch the other two. In that case, you could set up a pair of linked servers or use the OPENROWSET command to query a server on the fly. Try one server, then try the other, and if one connects, you're in business.

  There's a couple of problems with this - first off, your monitoring scripts will hang for 30 seconds when the primary server is down. They'll wait to time out, and during that time, you're not getting any monitoring done. Because of that, I'd recommend using something other than T-SQL for monitoring. There's a bunch of multithreaded alerting systems you can use like ServersAlive that will check multiple servers for you at once.

  The second problem is that you said, "traffic is being redirected." I want to make sure you understand how mirroring works - it doesn't automatically redirect queries. The brains of mirroring lives in the client's connection string, which must have both the primary and secondary server names in the string. It's up to the client whether or not traffic fails over to the second server, and you can't monitor every client from a central point. (Well, you could query the DMVs on the second server and verify that every expected client is connecting, but that gambles on every client needing to connect at the same time.)

  Ddono25 : Brent - Thanks for the response! Sorry for the confusion about the "traffic is being redirected". I am deploying this for SharePoint 2010 and found that the 'Mirroring-Aware' feature is configured for high-availability mirroring with automatic failover only. Or that is the only configuration supported? More studying is definitely needed. Side-Note: Huge fan, read your blog every day and am reading through your book right now. Thanks again for continuing to take time out to answer questions like these!

  Brent Ozar : Thanks, glad you like the blog! Yes, if you want automatic failover, mirroring is the only game in town. You can use high performance mode for mirroring, which doesn't commit transactions on both servers simultaneously and doesn't allow for automatic failover, but then human beings have to get involved when there's a server failure.
  From Brent Ozar