3

I have come across an idea recently when I had to migrate a ton of in-house applications to a new SQL server. The more I look at it, the more it sounds perfect, but I wanted to ask the community for feedback about it. It is using SQL0216 with AlwaysOn.

What would be the drawbacks of telling my developers to use a DNS entry to point to their respective databases in their ConnectionStrings?

Example: DNS:App1_SQL points to Servr1, DNS:App2_SQL points to Servr1. Server=App1_SQL;Database=App1;Trusted_Connection=True; Server=App2_SQL;Database=App2;Trusted_Connection=True;

This way, when I migrate databases, I can do it one application at a time, making sure everything works before moving on. No code recompile needed, nor any configuration files to handle. I simply have to copy the database over and update the application's DNS entry.

If a particular application is using external database references, I trained my developers to always use Synonyms. This way, I create a Linked Server to the old Database, and update the Synonyms accordingly upon migration.

Other than having to manage a ton of DNS entry one-by-one, I still consider it a way to relieve some stress related to database migration, being able to do it by small chunks instead of a whole server in one shot.

Now, what are the caveats that I don't see? Because it sounds too easy and beneficial not to be a very standardized practice, yet I have never come across this suggestion ever. Would Kerberos be an issue?

Thanks in advance

5

What would be the drawbacks of telling my developers to use a DNS entry to point to their respective databases in their ConnectionStrings?

Technically, now, one of the drawbacks is that they aren't going to change their connections strings... when, say, they should be adding in connection specific keywords such as MultiSubnetFailover.

You'll also have a ton of DNS items to regulate, which sounds easy - and in general, it is. I do, though, tend to see it lead to network name duplicates though.

This way, when I migrate databases, I can do it one application at a time, making sure everything works before moving on. No code recompile needed, nor any configuration files to handle. I simply have to copy the database over and update the application's DNS entry.

This is actually pretty common, especially in environments that use tons of 3rd party applications where the connection string information isn't known or licensing code prevents the renaming of a connection (ugh).

Because it sounds too easy and beneficial not to be a very standardized practice, yet I have never come across this suggestion ever.

See above. Many of the companies I know have an alias policy for that reason. It's also how AGs work for the listener as it is a network layer indirection.

Would Kerberos be an issue?

If you're using a CNAME, it'll just point to the actual host so you won't have any issues there. If you put in a new A record then it will cause an issue and you'd need to setup the SPN for the new A record. Otherwise, with a CNAME, the SPN should be the name of the host.

  • Does the client actually parse the canonical name from the DNS response and use that for the Kerberos SPN? I thought it just took (and cached) the first IP of returned A records. – David Browne - Microsoft Feb 24 '18 at 15:02
  • 1
    @DavidBrowne-Microsoft Depends on the DNS infrastructure. In general if it gets a CNAME, that will point to the A or AAAA record to get the IP. Whatever the actual name of the host is, would be found at that point, but all A/AAAA records should be returned. – Sean Gallardy Feb 24 '18 at 15:25
  • Right, but does the client actually use the name on the A record to calculate the SPN (or in NTLM)? – David Browne - Microsoft Feb 24 '18 at 15:40

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.