Web Development

Use the DNS CNAME for database connectivity


The DNS CNAME record is probably the greatest tool as an administrator that we can use to facilitate moves and changes in the backend when using Active-Directory integraded DNS. Using the CNAME record for database connectivity is a new strategy I have started to use to facilitate server moves simply with the DNS change. Take the following example:

FQDN Server Name:  DATASERVER1.AMCS.TLD

Associated CNAME:   CORPDB.AMCS.TLD

Now, DATASERVER1 is a Windows cluster for Microsoft SQL Server with a disaster recovery site with the databases mirrored. If that is needed to be used, the CORPDB record points to DATASERVER2 to redirect client traffic to that database server. But, the management does not stop there. This accomodates for a single change in DNS to point the server in question to the remote data center (RDC) database engine as a different FQDN name. But, I've been taking this a step further with CNAME use. For each database, I have been creating a CNAME record that points to the relevant server or DNS CNAME that the database is currently housed upon. Further, I make these CNAME records self-documenting in their nomenclature so that looking at the DNS records tells you everything needed. Here is an example:

Associated CNAME:   DB-DATABASENAME-STATE.AMCS.TLD

FQDN server name of target host:  CORPDB.AMCS.TLD

In this example, DB indicates a database, DATABASENAME would be the application name and STATE would refer to the implemenation level (Live, Test, etc.). All of these relevant records starting with DB- helps in alphabetical sorting in the DNS console. By using CNAME records in this fashion, all client connectivity is pointed to the DNS record instead of the server name. This permits a move of the server and a move of the individual database to be transparent of any configuration outside of DNS record management. By pointing all client connectivity to the CNAME record, configuration changes are not required (other than clearing a DNS cache) on the clients accessing the databases.

About

Rick Vanover is a software strategy specialist for Veeam Software, based in Columbus, Ohio. Rick has years of IT experience and focuses on virtualization, Windows-based server administration, and system hardware.

11 comments
thelmahorton94
thelmahorton94

DNS CName reconstruction substandard protocol alliances. I believe that this would be more easily solved with the input of a predetermined formula or download acumen. What do you think? And is Tech capable of producing such an instrument for DNC CName hand shakes?

b4real
b4real

One note that I have run into since this post is that some applications do not support this practice. Namely SMS 2007 for System Center. This is unfortunate and poor design in my opinion.

ernestm
ernestm

This is indeed a best practice and allows you to reprovision boxes and instances in a complex environment without incurring the huge amount of downtime and effort you usually need to change a bunch of distributed config files in app servers, specific applications, db clients, and more. If your programmers are doing enough network turns that the DNS lookup time is a concern, they are retarded - the cross-network connect time will be way worse than the lookups. Also, you do know that DNS lookups are cached on... everything, right? Implement this immediately, then instead of wasting your time with busy-work, you can address meaningful performance problems (like those apps that go across the network 100 times a second).

jmhmaine
jmhmaine

Using either a FQDN or CNAME slows the application opposed to using an IP. The reason is that the application has to resolve the name for each call to the database. Another approach is use SQL Aliases to abstract the server name. The issue is that if you are dealing with a large number of clients that this approach requires more work because you need to update all the clients, opposed to a DNS server for a CNAME record.

Jaqui
Jaqui

that are not slower are calls to localhost. for every other instance, the ip will always return a connection faster than a name based call. since localhost is an automatic 127.0.0.1 call it is a known host with no real lookup overhead.

b4real
b4real

The resolution does take more time initially, but once it is resolved, the traffic rate will be indistinguishable by using CNAME, IP, NetBIOS name, FQDN, etc.

b4real
b4real

When the database engine is hosted centrally, and the application servers are over the network on database servers, putting in the IP is very limiting.

jmhmaine
jmhmaine

Actually, it still is more work to use any name system to resolve a server location opposed directly using an IP. Each time an application calls a server based on DNS, WINS or CNAME, the OS will have to look either at the local cache or to a central server to resolve. If you're dealing with 100s of calls a second between to a DB server, that can add up.

b4real
b4real

Yes, it would, but moving the server becomes much more tedious. And configuration by IP, unless, the DB is on the same server as the app. When the DB is on a remote server, using a name is much better from a management perspective, and once the name is resolved, performance is indifferent.

Jaqui
Jaqui

internal network it should be a static route permanently in the table, so one quck lookup and it's got the data to get to the server. dns / name based, it does the quick lookup, then starts checking in the name systems for routing information, then adds the routing to the table and gets the connection route and goes off to actually give the dbe the query. name based only makes sense if the database is not in the same office.

dunworthdl
dunworthdl

...for the administrator(s). Your argument is technically correct, name lookups add processing overhead. However, the use of name lookups in an application deployment is an engineering decision that will involve compromises between performance and administration. In a situation where you have a few app servers accessing the database, you would obviously use IP addressing because you would see performance benefits and the administrative overhead of changing the address of the database server is relatively low. In a traditional client-server deployment where you may have 100 or 1000 or 10000 clients accessing the database(s), using DNS (or some other naming service) is a requirement. Any performance decrease attributed to the lookup will not be quantifiable because it's spread across the entire client base.