THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Bad database practices: allowing apps to connect to the server hostname

It’s common to see a client application referencing the hostname of a SQL Server instance in its connection string. For instance, assume that you have a server whose hostname is NYCSQL01, and you install a default SQL Server instance on it. Naturally, you can connect to the SQL Server instance as NYCSQL01, and that’s the name you give to your clients for their apps to connect to.

 

Why is this a bad practice?

 

It’s a bad practice when you have to move the database that the client application is using to a different host. You may think that happens rarely enough to be an issue. But in the real world, moving a database to a different server can happen for a variety of reasons, and is not a rare event. If you reference the server hostname in a connection string of a client app, such a move would force you to modify the client app’s connection string. And that can be a nightmare!

 

Ideally, no connection strings should be hardcoded in an application, and they should be easily editable. In practice, this may not be the case. But even if you have no problem editing the connection strings, finding out all the connection strings that reference the hostname and making sure you are not missing anything may not be an easy task. In fact, it’s often a huge challenge. And then, when a database is used by many apps, coordinating all the client-side changes is yet another big headache.

 

The right thing to do is to shield your client apps from any change in the server hostname so that you can move a database to a different server without having to make any change on the client app side. To accomplish this, you can’t allow your client apps to connect with the server hostname. In other words, you need to remove your client apps from any dependency on the server hostname.

 

A common solution is to create a network alias that will redirect the client connection to the same IP address as that of the hostname, and enforce the rule that a client app can only connect to SQL Server via a network alias. With this configuration, when you need to move a database to a different host, you just have to update the network alias to point to the new host, and the client app can be left untouched.

 

A number of points are worth noting.

 

First, this is not just an issue with the hostname of a standalone server. The virtual network name of a clustered SQL Server instance should be treated the same. That is, no client app should connect using the virtual network name of the clustered SQL Server instance. A network alias to that virtual network name should used instead. The reason is the same because you may need to move a database from that clustered instance to a different instance, and you may not be able to use the same virtual network name.

 

The second noteworthy point is that it may be handy, though not always necessary, to create a separate network alias for each user database (or a group of tightly coupled databases) so that each user database can be moved independently.

 

The final point to note is that it is convenient to manage the network aliases in a clustered instance by exposing them as network name resources. This makes it easy for the DBAs to know exactly what network aliases are defined and to check if they are functioning properly. For this reason alone, it may be worthwhile to run a SQL Server instance on a single-node cluster instead of a standalone server.

 

Different environments adopt the network alias approaches differently. Some environments were forced to require network aliases because some databases (not necessarily SQL Server databases) need to failover between data centers on top of storage replication, and the client apps need to deal with that failover. There is no choice but to use a network alias. Other environments are more proactive in enforcing network aliases, and are reaping the benefits of that best practice. Still some environments are just limping along with unnecessary client dependencies on server hostnames without realizing there is a much better alternative. I hope you are not in this last category.

 

Published Monday, December 28, 2009 4:32 PM by Linchi Shea

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Louis Davidson said:

Nice article. This is actually something I have been trying to work on in our environment for a while. What finally made it make sense was when a server was swapped out and the new server took a while to get set up. So the server name was changed but the old server still existed, but it had a new name, and changing names back would take too long...

If it had just been a DNS name, it would have taken minimal time...

December 28, 2009 5:16 PM
 

C Rummel said:

Are there ways (through scripts, tools, AD, etc.) to enforce, audit or otherwise verify clients are only connecting using the alias instead of the hostname, assuming both are in DNS?  Let's say you have NYCSQL01 and NYCSQL02 and want clients to use an alias of just NYCSQL so that you can flip DNS for maintenance or DR, etc.  You don't want to realize only too late that you missed changing that one app...

December 28, 2009 5:43 PM
 

Linchi Shea said:

C Rummel;

I'm not aware of any simple way to enforce that clients only connect with the network alias. But you don't even have to tell them NYCSQL01 and NYCSQL02 are hostnames that they can connect to. You tell them only that their apps should connect to NYCSQL or whatever the alias they should use. The applications should NOT be using NYCSQL01 or NYCSQL02, period. And you may want to indicate that these hostnames are subject to change. Just like they can ping NYCSQL and find the IP address and connect to the IP address directly, if they so insist. But they do that at their own peril. There is not much you can do, if they insist on shooting themselves.

December 28, 2009 10:35 PM
 

gbn said:

It depends on your environment.

In my current shop a server migration would involve an instance name and port number change too, which still means a connection string change. I've used this concept previously though to great effect with default instances.

December 29, 2009 2:58 AM
 

Linchi Shea said:

gbn;

It does not have to be default instances. But it would simplify things greatly to stick to the default ports. Note that in a cluster a named instance can use the default port as well. One more reason to run SQL instances in a cluster, even if it's a single node.

December 29, 2009 9:22 AM
 

Ewan said:

Hi Linchi

Great article. I had always assumed that multi-active clusters couldn't use this method, although since they're listening on different Virtual IPs, it should have been obvious that they could. Anyway, I've tested this on a non-prod SQL2005 cluster, and my SQL2005+ clients (including sqlcmd) can connect happily to both active instances on the cluster while omitting the instance name.

However, osql and SQL Query Analyzer cannot connect... As far as I can make out, DBNETLIB will always connect to the SQL browser to determine the port number for the instance (even if the port is specified in the connection string). SQL2005 doesn't do this. The connections I tested are all to the same target server, from the same workstation. I can work around this by adding an alias to the local PC. But then, that defeats the whole object of the exercise.

Do you know of any way around this for SQL2000 clients? They will remain with us for some time to come.

Ewan

January 4, 2010 6:28 AM
 

Aaron Bertrand said:

About two months ago, I began planning a cluster migration for one of our primary SQL Server 2005 clusters

January 4, 2010 11:54 PM
 

merrillaldrich said:

Linchi - I have also been trying to solve this issue, but with named instances on clusters I must be missing something :-). Do you set up a cluster with, say, 4 named instances, but set all the instances to use port 1433 to make DNS aliases work? Or is there some other magic to resolve named instances (i.e. make DNS alias "FOODB" direct clients to cluster instance "SERVERNAME\INSTANCE2," where the client knows nothing about the instance name)

January 6, 2010 11:44 AM
 

Merrill Aldrich said:

I haven't been able to post for a bit; my group had a large project come up involving consolidation and

January 6, 2010 11:49 AM
 

dp said:

And this is accomplished via the hosts file or sql aliases?

January 7, 2010 1:55 AM
 

humbleDBA said:

Hi Linchi,

Great article.

We too have worked on this to make the job of moving DBs to other host SQL Servers a bit easier for everyone - DBAs and Devs. We've experimented recently with DNS CNAMEs and this seems to work really well. We can even do this using non-default ports and named instances. So far we have found this to be very flexible for our needs and for the needs of the developers.

January 27, 2010 6:19 AM
 

pazman said:

--------------

  merrillaldrich said:

Linchi - I have also been trying to solve this issue, but with named instances on clusters I must be missing something :-). Do you set up a cluster with, say, 4 named instances, but set all the instances to use port 1433 to make DNS aliases work? Or is there some other magic to resolve named instances (i.e. make DNS alias "FOODB" direct clients to cluster instance "SERVERNAME\INSTANCE2," where the client knows nothing about the instance name)

---------------

Did you ever find a resolultion to this? I need to connect to a named SQL cluster instance from an AIX message broker and it doesn't support the \ in the instance name. Unfortunately CNAME record also doesn't support  \ so I'm not sure how to go about things.

February 17, 2010 6:08 AM
 

Ewan said:

Linchi

What are the implications for SPNs? Do you need to define those manually to allow delegation to work? (and change them manually in case of migration?) Having clustered network name resources doesn't do "everything" in case of failover unfortunately (from my testing so far). It would be nice if there is something obvious I'm missing...

Ewan

March 2, 2010 10:56 AM
 

Ewan said:

I'll just go ahead and answer my own question:

You'll need to manually create the alias SPNs in AD for delegation to remain functional when connected to the alias as opposed to the SQL virtual server.

Create one alias for the CName and one for the FQDN

setspn -A MSSQLSvc/<DBAlias>:1433 <serviceaccount>

setspn -A MSSQLSvc/<DBAlias.FQDN>:1433 <serviceaccount>

Happily, these SPNs will follow the cluster resource around, so this only needs to be done once, and not modified when the alias moves.

To Merrill and Pazman, the suggestion is correct. Just configure each named instance to listen on 1433. Since each clustered installation has a different IP address, the sockets are distinct and there is no conflict. You can then use the network name (without the instance name) to conenct to the instance.

Hope this helps someone

Ewan

March 4, 2010 4:31 AM
 

michele said:

humbleDBA,

Can you please tell me how you did it? We dont have cluster environment but we are moving diff servers/default instances to one big server with multiple name instance. We want to use the DNS alias but I couldnt find solution on how to set up for name instances.

Thanks!

March 9, 2010 6:17 PM
 

Joel said:

Grate article but is there anyone who know who to solve this in a cluster environment with named instances. I have tested without luck :-)

/Joel

December 10, 2010 4:48 AM
 

MacAries said:

There should be an app server that every app connects to and it redirects to the correct database according to some simple settings that the dba administers

December 10, 2012 5:10 PM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement