Sql-server – n easy way to trigger automatic failover of a single mirrored database

SQL Server's native database mirroring includes something really slick: automatic page repair. If the principle detects a corrupt page on disk, it fetches a clean copy of the page from the mirror automatically without taking the database down. You don't get that level of database repair with any third party product. That database awareness is one of the reasons I prefer the native tools - well, that and I just don't trust third party filter drivers on database servers. I've seen too many blue screens of death due to third party filter driver bugs.

Bad news: database mirroring is deprecated in SQL 2012. This means it's still around for this version, but will be removed in the future.

Fantastic news: SQL Server 2012 introduced AlwaysOn Availability Groups to replace mirroring, and they're awesome. You can actually query or back up from the mirror, and you get up to four mirrors. Some can be synch and some can be asynch. This gives you all kinds of flexibility over database mirroring, and it blows away the third party tools.

After spending a portion of the day dealing with moving mirrored databases from server to server in our DEV environment in preparation for our move to SQL Server 2012, I came across this MSDN document that explains that ENDPOINT security is determined solely based on the type of account SQL Server is running under. If SQL Server is using a domain account, then that account automatically has access to the endpoint. If SQL Server is using a built-in account such as [Network Service] or [Local System] etc, then the endpoint must be configured with a security certificate, and only holders of the certificate can access the endpoint.

The relevant section from the document:

Determining the Authentication Type for a Database Mirroring Endpoint

It is important to understand that the SQL Server service accounts of your server instances determine what type of authentication you can use for your database mirroring endpoints, as follows:

If every server instance is running under a domain service account, you can use Windows Authentication for your database mirroring endpoints. If all the server instances run as the same domain user account, the correct user logins exist automatically in both master databases. This simplifies the security configuration for the availability databases and is recommended.

If any server instances that are hosting the availability replicas for an availability group run as different accounts, the login each account must be created in master on the other server instance. Then, that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance.

If your server instances use Windows Authentication, you can create database mirroring endpoints by using Transact-SQL, PowerShell, or the New Availability Group Wizard.

Note:

If a server instance that is to host an availability replica lacks a database mirroring endpoint, the New Availability Group Wizard can automatically create a database mirroring endpoint that uses Windows Authentication.

If any server instance is running under a built-in account, such as Local System, Local Service, or Network Service, or a nondomain account, you must use certificates for endpoint authentication. If you are using certificates for your database mirroring endpoints, your system administrator must configure each server instance to use certificates on both outbound and inbound connections.

There is no automated method for configuring database mirroring security using certificates. You will need to use either CREATE ENDPOINT Transact-SQL statement or the New-SqlHadrEndpoint PowerShell cmdlet. For more information, see CREATE ENDPOINT (Transact-SQL).

Remus Rusanu (in his answer) correctly identified how to determine the security in place on a mirroring endpoint; as in:

SELECT ep.name, sp2.name AS Grantee, sp.name AS Grantor, p.permission_name, ep.state_desc
FROM sys.server_permissions p 
    INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id
    INNER JOIN sys.server_principals sp ON p.grantor_principal_id = sp.principal_id
    INNER JOIN sys.server_principals sp2 ON p.grantee_principal_id = sp2.principal_id
WHERE p.class_desc = 'ENDPOINT' AND ep.type_desc = 'DATABASE_MIRRORING'

However in my case on SQL Server 2005 the original mirroring endpoints do not show up in the results of this query - I assume this is because they default security allows CONNECT access to the account SQL Server is running under (as explained above).

So the canonical answer to the question, how do you know who has CONNECT access to an ENDPOINT, is the sum of the query above, and the account SQL Server is using, along with any possible certificates you've configured with access to the endpoint.