Connection Strategy for Multiple Database Environments

As machines get more powerful and less expensive people are
using a single machine to host more than a single SQL Server database. So, over
time, a SQL Server machine might support more and more databases. But
eventually you will need to replace your hardware, your database server will
fail due to a hardware problem, or your multi-database machine might become
saturated with activity from multiple applications, eventually causing
performance of all applications to suffer. So what are you to do when one of
these situations occurs? How can you minimize the work required to re-point
your applications to a new database machine, or split your environment into
multiple database machines for performance reasons? In this article, I will
look at one way to design your database connection strategy to simplify
changing application connections so you can plug-n-play databases with less
administrative overhead when the need arises.

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.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles