2

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

  • DNS records have a Time To Live. I don't remember all the details, but when you change the CNAME record, the clients will get the updated record after the TTL expires and if you have AD Integrated DNS zones, the records replicate to other servers – Alen Aug 9 '18 at 13:57
  • Yep, but I never changed the CNAME. It's still pointing to SERVERA. – Henry Lee Aug 9 '18 at 14:06
  • is it a newer version of .NET? Most of my experience with this is with sql 2005 and java and we had to change the cname – Alen Aug 9 '18 at 14:14
  • Looks like the driver is SQL 2008, according to this post: blogs.msdn.microsoft.com/sqlcat/2010/10/25/… – Henry Lee Aug 9 '18 at 15:11
  • Few possibilities. The app might be getting the partner info from SQL and automatically redirecting. Whoever controls the app may have changed the connection string themselves or has some script to build a dynamic string. And last, have you verified that you're really connected to B? – Alen Aug 9 '18 at 17:58
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.