SQL Server instances are great for allowing multiple instances on a single windows host, but unlike with Oracle instances this does cause a very big headache when configuring applications that consume these servers, instances and its databases. In the Oracle world there are many different mechanisms for name resolution and one of the most common is the use of the TNSNAMES file which effectively maps the database (think SQL instance) to the host. The Oracle database name is a unique identifier and does not have an “instance” postfix name like SQL server, so this lends itself very easily to application/ database name separation. In addition the Oracle instance is not tied to a particular hostname and therefore can be moved onto another physical host with very little reconfiguration required in order for applications to communicate to this new host location. To be more specific, when using TNSNAMES resolution, all that would be necessary from the application perspective to reconfigure them to reflect a new database host would be to edit this file on all the application servers ensuring that this hostname is changed to reflect its new name for this specific database.
Is something similar possible on SQL Server? I don’t recall ever seeing any “enterprise” solution to this problem and have racked my brains over one. The biggest problem is the way in which SQL Server uses instance names. In order to communicate to an instance name, first the hostname needs to be resolved. Once this hostname is resolved the client will then communicate with the host and attempt to resolve the second part of the name, the instance name. Once this final part has been resolved then the client can communicate directly to the instance. So when the transport mechanism is TCP/IP the client will be communicating with a unique TCP port number which the Server instance will be running on.
The instance name of the SQL server is unique only upon the physical host that it is currently running on, therefore it is perfectly possible to have many different hosts each running with an identical instance name. Much the same way as many different hosts may each have a default instance running. Therefore any solution first needs to be able to resolve to a different physical host (and be changed with ease) and secondly needs to be able to associate itself to an instance name. If the instance name can be made irrelevant then when a database instance is migrated onto a new host, a unique (and possibly different from the source) instance name can be given.
I did first think of using DNS aliases to solve this problem, but this does not address the instance name only the hostname. In fact I could not think of any mechanism at all to communicate to an instance name (and obfuscate them from the application), that is until I started thinking about SQL Server client aliases. These are not something I have really spent a great deal of time using, but provide for a similar kind of technique to Oracle TNSNAMES. Even more interesting or surprising is that they allow for long names. For instance “my server 1” might map directly to a SQL host instance “srv1\ins1”. The limitation of this solution is that it is strictly client based, and so as it stands not an enterprise solution. In order to create or change client aliases, it is necessary to use the Client Network utility however if you delve a little deeper you will find that all this is doing is simply adding a registry string entry. Knowing this makes deployment very easy, because it means that all that is necessary is to maintain a registry file, updating deleting and inserting new entries where necessary and using Active Directory to deploy these registry entries across the enterprise. This means that very rapidly you can switch over an application to point to a new host/ instance with very little effort.