How Applications Connect

Each application needs to identify the database server it will be connecting with to retrieve data. Applications do this by using a connection string. A typical connection string might look something like:

Server=SSEDB01; Initial Catalog=AdventureWorks; 
 Integrated Security=SSPI;

In this example, the database server is identified with a machine name, in this case SSEDB01. Now a connection string doesn’t have to have a machine name. It could be an IP address, an OBDC DSN name, a DNS alias name, etc. The name just needs to be something that can be resolved to an IP address. Name resolution can be done a number of different ways.

If your SQL Server machine is located within a domain, it can be registered with the domain creating a DNS name. When a machine is registered with DNS then a client or application can connect to it using the machines registered name, which is how the connection string above works. Even better, with DNS you can create a DNS alias, which is a logical name to represent your SQL Server machine. By using a DNS alias name in your connection string, DNS translates the name to an IP address behind the scenes when a connection to the database server is made. This allows you to only need to remember a meaningful name of where to connect, instead of a cryptic numeric string of an IP address, or machine name. When you use a DNS alias name in the connection string you can create a connection strategy that insulates applications from the physical location or machine name of the database server.

Using DNS to Identify Location of Application Database

When using DNS to identify the location of the application database you can use the name of the domain machine in the connection string, but this method is not that flexible. What happens when you want to change the name of the physical SQL Server machine? If you use the machine name then each time it changes you need to modify the application connection strings to reference the new machine name. This might not be so bad when you only have a single application connecting to a database server. However, if you have a lot of applications and databases on a single machine then this means a lot of connection strings will need to be changed anytime you rename your server. Therefore using the machine name in your connection string is not flexible to environment changes over time.

A better approach is to creatively use DNS alias names to resolve where an application database lives. So instead of using the machine name to identify the location of the database machine for all applications, you should consider creating a meaningful, unique DNS alias name that resolves to the IP address of your database server. In my example above instead of coding SSEDB01, which is a machine name, I would be better off using a DNS name like SQL2005PROD. In this case, the name SQL2005PROD would be defined in DNS to have the same IP address as the physical machine SSEDB01. Using a DNS alias puts some meaning behind the name. Here by using SQL2005PROD you can tell this name is associated with the production SQL Server 2005 machine. So by defining this DNS alias for my production SQL Server 2005 machine my connection string above would now look like this:

Server=SQL2005PROD; Initial Catalog=AdventureWorks; Integrated Security=SSPI;

This connection string and the one above will resolve to the same IP address.

So why would using a DNS name in the connection string be a good idea? One reason would be to have a descriptive name, but that is not the only reason. Say your database server contains many different databases and supports 50 different applications. Now say SQL Server machine SSEDB01 has a hardware error of some kind. Moreover, you have a backup machine SSEDB02 that you can quickly restore all the databases from SSEDB01 to support those 50 different applications, because you have been shipping your SSEDB01 backups to this machine for safekeeping. Plus, you know you can restore all of the SSEDB01 databases on SSEDB02 quicker than it would take to resolve the hardware problem with SSEDB01. If you coded all of your connections strings for those 50 applications to the machine name SSEDB01 then you would have to modify all of the connections strings to use SSEDB02 in order to have them point to your new fallback server (SSEDB02) for your recovery to be complete. Modifying 50+ connection strings might take a fair amount of time and be error prone. Now if instead you had used a logical name like SQL2005PROD as a connection name in all of those 50+ connection strings, then you would only need to make one change to re-point all of your applications to the new fallback server, SSEDB02. That one change would be to DNS, to change SQL2005PROD to point to the IP address of SSEDB02, instead of SSEDB01. Once you make this change, each application would automatically no longer connect to SSEDB01, and would instead connect to SSEDB02, without changing any of those 50+ connections strings. By making this small application change to the connection design, to use a logical name for a SQL Server machine, instead of a physical server name, or IP address, the amount of work required to re-point all applications and potential problems to point applications at a new SQL Server box is greatly reduced.

How to Use DNS to Help with Capacity Management

So how can using a DNS alias name in your connection string help with capacity management? Say your environment has a number of different production SQL Server machines. Each machine supports many applications. Let’s also assume that your database growth for some applications is fairly linear, but a fair number of application databases don’t have a predictable growth rate. Those databases grow at an unpredictable rate, sometimes they don’t grow at all, and other times they increase or decrease in size exponentially. Because of this volatile growth rate for some databases there are some servers that have very little space, other servers run out of space frequently, while still others have too much free space. So how can DNS help out with managing these kinds of disk space capacity issues?

It is not always easy to add more disk space to a database server when the databases have ballooned up to the capacity of the disks drives of a server. It might take months to acquire the additional hardware and schedule a timeframe to extend the disks space capacity of a server. Therefore, if you have an environment with disk space capacity issues you need a way to plug and play databases to manage this kind of capacity problem. By “plug and play”, I mean you need a method where you can copy databases from one server to another quickly and change that IP address of where applications get their data with minimal effort. By using DNS, you can re-point your applications to the new location for databases quickly. Of course, you need to design your application connection strategy to handle this kind of database movement scenario.

In the prior example of how to use DNS, I talked about having a single DNS name that is logically associated with the physical SQL Server machine IP address. Using this strategy doesn’t work if you only want to move a single database from one server to another because of a disk space capacity issue. So instead of having a single DNS name for all databases on a server, you need to develop a logical DNS naming strategy that has a unique name for each application.

Say you have a database server that contains the databases for the Order, Accounting, Personnel, and Billing systems. For these four different applications, there are four different databases: Order, REV, HR, and Billing. In this situation you would define four different DNS entries one for each of your production applications, where the DNS names would be something like, ORDER, REV, HR, and BILLING. All of the connection strings for each application would then use the appropriate DNS entry from the above list to make sure the application was pointing to the current physical machine where the database lived for the applications. When you need to move one of the databases off to another server because of capacity issues all you would need to do is move the database to the new server, and then change the DNS entry to point to the new database server. One change to DNS and all the connections for that application are re-pointed to the new location.

You can use the logical DNS naming methodology to handle other situations as well. Say you have a development, quality assurance and production environment for each application. In this case, you can append an environment designation to your DNS names. So, for your BILLING application you might have BILLINGDV for development, BILLINGQA for quality assurance, and BILLINGPR for production. Or, say you have high CPU on one of your databases servers, then by using DNS you could quickly move one or more databases off the heavy hit server to underutilized servers and then re-point the DNS entries for the moved databases to new servers. Doing this provides you a low-tech solution to load balance CPU usage of your database servers.

Management by Design

Each environment has its own unique requirements. If your environment has multiple applications with many databases on a single server, then designing your connection strategy to minimize issues that might come up over time makes sense. Next time you are bringing up a new machine and migrating your applications over to it, consider whether or not using logical DNS names might solve some issues associated with managing your environment.