4

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

Paul White
  • 94,921
  • 30
  • 437
  • 687
Philippe
  • 517
  • 6
  • 15

1 Answers1

6

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.

Sean Gallardy
  • 38,135
  • 3
  • 49
  • 91