We use DNS names on our SQL clients to avoid changing SQL connection properites whenever a SQL Server is changed or a database is moved to another server. To resolve the situation, we simply change the DNS entry to use the IP address of the new server. This keeps us from altering client setup when changes occur. This has worked fine in our non-clustered, single instance per server environment.
Is there a way to enable this type of DNS resolution while installing SQL Server on an active-active two-node cluster, each node of which contains a default instance and a named instance? In other words, server A and server B are clustered. We would like to have a default instance and a named instance on each server. One instance on server A would fail over to server B if server A fails. One instance on server B would fail over to server A if server B fails.
Unfortunately, DNS allows for IP address mapping, but not port mapping. Does anyone know of any way to enable DNS name resolution for multiple instances of SQL Server on one server?
You don't have to do any of that. You point the clients at the name of the SQL Server. The cluster takes care of the resolution to the physical piece of hardware that SQL Server is running on. When you install SQL Server in a cluster, you get a name for the SQL Server that is visible at the cluster level. There is no visibility of the instance name at the purely machine level. If you install four instances of SQL Server to the cluster: SQL1, SQL2, SQL3, and SQL4, you then reference them as SQL1, SQL2, SQL3, and SQL4. It is irrelevant to your client application which physical piece of hardware you tell the cluster to run those particular services on. They will be resolvable by those names irregardless of where they physically reside.
There are two references available to understand SQL Server clustering. The first one is a DVD orderable from Microsoft which contains about nine hours of content about high availability. The second resource is a three-day hands on course taught by Solid Quality Learning which is the high availability courseware developed by the SQL Server team which spends about one day on clustering as well as having labs for clustering.
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.