My company has asked me to develop a server migration plan to reorganize their network and make it easier to perform IT tasks. They currently have three MSSQL servers. The current plan is to migrate all of the databases over to a single MSSQL server and then setup redundancy. I am still working out this part of the plan.
They develop a lot of custom applications for internal use that polls a lot of data off of these databases. Unfortunately, they have had a lot of programmers in the past which have hard coded the MSSQL server names into the various applications and stored procedures. I am currently working through all of the code to correct this problem.
Here is my question. Instead of entering the new MSSQL server's name into all of the different applications, what about using DNS A/CNAMEs for each database (i.e. database1.domain.com, database2.domain.com). This would allow the IT administrator to easily change the physical location of the DB and update the CNAME without getting a programmer involved. In my mind, this would make administration much easier and would allow for the scalability the company is expecting later this year.
We currently use Windows 2003 Server Standard and MS SQL 2005 Standard.
What is your opinion on this approach? Your help is greatly appreciated.
Best Answer
This is a Good IdeaTM. You can also use CNAMES along with a DNS Search Suffix to relocate all of the old hardcoded server names to the new MSSQL servers as well.