I have SERVERA and SERVERB configured for Database Mirroring starting with A as Principal and B as Mirror. I created a CNAME for SERVERA and use that in my connection string. I am not using the FailoverPartner attribute of the connection string, partly because I suspect some of my applications will not support it and partly because I'm trying to learn about CNAMEs.
My expectation upon failover from A to B was that: 1. connections would fail 2. I would need to change the CNAME to point to SERVERB. 3. I would have to ipconfig /flushdns on my clients. 4. Then all would be good.
Instead, I didn't have to do any of that. My application automagically started going to SERVERB, even though my CNAME is pointing to SERVERA. I've read that the connection string will obtain the mirror instance from SQL and that will supersede the FailoverPartner attribute of the connection string (and the CNAME, I guess), but those were all posts about when the FailoverPartner attribute was being used. Again, my connection string is not using that attribute.
Can someone explain or point me to a resource that indicates how my app knew to got to SERVERB even though the CNAME being used in the connection string pointed to SERVERA?
Setup is Windows 2008R2 / SQL 2008R2
Best Answer
This is by design. In the situation where your client is able to connect to the primary server but the database has been failed over to the secondary server, the primary server will inform the client of this. Because the server has no awareness of the CNAME you're using it will use the information it has from the mirroring configuration to direct the client to the current location of the database.
Where your CNAME would come into play would be in a situation where the primary server is no-longer contactable. The client will then need to time out on contacting the primary before attempting to connect to the secondary server configured in the connection string.
The following page from MS describes the behavior... https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/database-mirroring-in-sql-server