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.
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.
An alternative approach is to setup a SQL Server Native client alias on the host where the custom app is running. This may not be practical if you have a server farm with loads of webservers, or heaps of fat clients (unless you automate it) but is has it's place.
It's also the only way to get apps point to a sql server named instance when it's not defined in the connection string.
I do domething similar. When I started at my current company they used an A record for each database DSN: db1.domain.com for example. I created CNAME records instead so that I could point each database DSN to a single A record (for the SQL server) via the CNAME. Now if I change servers I simply change one A record and I'm back in business.
So you'll want to use CNAME records and not A records. If you use A records you'll have to delete and recreate each one when you change servers.
There is a nice article at Tech Republic about this subject.
In fact the author describes a strategy that I am in the process of implementing in which you setup a seperate CNAME for each database -- not just the server.
This then allows you the flexibilty to move a single database from one SQL Server to another without having to touch any clients or code!
https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/networking/?p=460