3

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.

4
  • Note: This would only be accessible on the private network via Active Directory. Jul 22, 2010 at 0:28
  • I think that's the point. Also, AD has nothing to do with it. It may be an AD integrated DNS zone but it's still DNS. DNS client resolvers don't know and don't care where the DNS zone\records are stored.
    – joeqwerty
    Jul 22, 2010 at 0:31
  • 1
    Since you're in the code already anyway, is it an option to push the server names (CNAMEs, still) into configuration files instead? It'd allow greater flexibility since there's no server name hard-coded into the application at all.
    – Paul Kroon
    Jul 22, 2010 at 1:33
  • @Paul: That is very important and exactly what I am doing as they hard coded the strings in every function that accesses the sql server. I'll keep my mouth shut about that :P Jul 22, 2010 at 2:44

4 Answers 4

4

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.

1
  • A fine and true idea. I pushed for much the same at my old company when it came time to retire our old Oracle servers.
    – sysadmin1138
    Jul 22, 2010 at 0:36
4

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.

3

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.

1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .