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.