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.
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.)
