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