I’m proud to be participating in SQL University’s High Availability and Disaster Recovery Week this week (April 18, 2011) along with Robert Davis (blog | Twitter) and Geoff Hiten (blog | Twitter). This is the first of my posts.

One of the biggest causes of downtime in my experience is how an application or user connection deals with a name (or IP address) change after a server switch. Many applications are not very tolerant of a server or name change, and that is not the fault of the backend: it’s a lack of planning in the application to be able to cope with changes on it. The backend and its administrators can only compensate for so much before it is the application’s problem. This is why application developers cannot assume availability happens by magic server dust. It’s their problem, too. If the applicaiton handles a server switch poorly, it will extend downtime.

This  first of two posts will discuss how to properly name clustered instances of SQL Server. The next post will cover some of the other situations such as database mirroring and log shipping where server names may not be the same.

As a quick recap, one of the biggest benefits of a clustered SQL Server instance is that the instance keeps the same name (and IP address) no matter what node it is currently residing on. The application will have to account for the failover, which is a stop and start of SQL Server, with things like retry logic or coding the application to be cluster-aware. However, the name will always remain the same. This is good news.

However, what I see all the time with a lot of customers is that they get confused as to what names to put where and where they actually map to. This post will demystify that.

When you have a clustered instance of SQL Server, there are a lot of names in play. You have:

• The name of each node, each of which is unique in the domain.
• The name of the Windows failover cluster which is also unique in the domain.
• The name of the SQL Server failover clustering instance (FCI) which is unique in the domain. This is true whether installing a default or a named instance. This is what applications will use to connect to.
• If installing a named instance, you will also have the named part of the instance which is unique in a given Windows failover cluster, but not necessarily in the domain. Along with the FCI name, this will be used by applications if a named instance is installed.
• You also need to be concerned with the value for the Instance ID, which I will get to in a little bit.

Confused yet? I hope not. Even if you are, here’s a simple example:
You are configuring a two node cluster for SQL Server. The node names are DENNIS, TOMMY, and JY and are in the STYX domain.  When you configure the Windows failover cluster, you give it a name of ROBOTO. When you configure the first instance, you configure it as a default instance with a name of EQUINOX. You then configure two more instances, CRYSTALBALL and GRANDILLUSION. This all seems straightforward, but getting there is part of the issue.

Assuming you’re going to use the interface for SQL Server Setup, the screen below from SQL Server 2008 R2 is the one that will get you into trouble if you do not enter things correctly. The screens are similar in both SQL Server 2008 RTM and Denali. While not totally the same in SQL Server 2005, the concepts are the same.
Figure 1. Instance Configuration dialog in SQL Server 2008 R2 Setup

I’m going to break down each value relating to names that can be entered.

SQL Server Network Name
SQL Server Network Name is exactly what it sounds like: it’s what Windows cares about and is used in a variety of places as shown below. The value for Network Name can be changed after installing a clustered instance of SQL Server by modifying it in the Windows failover cluster. This may cause a brief outage since it touches so many things and clients would need to have DNS updated accordingly. Depending on how long the time to live is for a DNS entry, it could be quite some time if left to automatically refresh.

1. The value is used as the basis of the corresponding network name in the Windows failover cluster.
Figure 2. Network Name for the default instance EQUINOX in PowerShell

Figure 3. Network Name for the default instance EQUINOX at the Group Level in Failover Cluster Manager

Figure 4. Network Name details for the default instance EQUINOX in Failover Cluster Manager

2. The network name for the clustered instance in the Windows failover cluster is used for the virtual computer object (VCO) corresponding to the clustered SQL Server in Active Directory.
Figure 5. The EQUINOX VCO in Active Directory

3. The network name for the clustered instance in the Windows failover cluster is used in DNS.

Figure 6. EQUINOX in DNS

Named Instance
Instance behavior is a bit different on a cluster. When you connect to a clustered instance of SQL Server, you do not connect to a node name nor do you connect to the Windows failover cluster name. You connect to the name you give it for the Network Name and if using a named instance, the named instance. For example, the EQUINOX instance is a default instance so applications and end users would use EQUINOX. It looks like Figure 7 in Failover Cluster Manager. Note that there is nothing next to the SQL Server or SQL Server Agent resources denoting what kind of instance it is; this is indicative of a default instance.

Figure 7. Default clustered instance in Failover Cluster Manager

To further see this is a default instance, look at the Properties tab of the SQL Server resource as shown in Figure 8. You will see the VirtualServerName (virtual server is older naming convention for the combination of clustered resources that have a name, IP, and disk resource that was retired when Microsoft shipped the Virtual Server product) value which is the same as the network namer and an InstanceName value of MSSQLSERVER. The SQL Server Agent resource will also show something similar, as seen in Figure 9.

Figure 8. Properties for the SQL Server resource for a default instance

Figure 9. Properties for the SQL Server Agent resource for a default instance

Things are a bit different for a named instance. In Figure 10, you will see the list of resources for the instance CRYSTALBALL. Note the (BALL) next to both the SQL Server and SQL Server Agent resources.

Figure 10. Named clustered instance in Failover Cluster Manager

Similar to a default instance, if you look at the properties for the SQL Server and SQL Server Agent resources (as shown here in Figures 11 and 12), you will see the VirtualServerName maps to the network name as expected and the value entered for Named Instance is reflected in the InstanceName value.

Figure 11. Properties for the SQL Server resource for a named instance

Figure 12. Properties for the SQL Server Agent resource for a named instance

Assuming you will be installing a named instance of SQL Server in your cluster, the named portion must be unique for the particular Windows failover cluster you are installing the instance into. You can reuse the named portion in another Windows failover cluster.

Warning: Be careful – if you screw up the named instance name, you cannot change it post-install as you can the network name. To correct the problem would be an uninstall and reinstall, which is clearly something you do not want to do.

Where I find customers get confused is that they will enter the same name for the Network Name as they will for for Named Instance. For example, EQUINOXEQUINOX. Don’t do that. Both parts should be unique, such as CRYSTALBALL. Why is this important? Technically you can use the CRYSTAL value for connecting in some places which may make it seem like a default instance. However, when you want to go configure things like replication, you need to know the proper full named instance. This happened to me recently when working with a customer. I was configuring replication on their clustered instance and they didn’t tell me it was a named instance and replication was giving me errors. I went into Failover Cluster Manager and determined the right name for the instance. You can easily figure this out yourself by using the sample screens above.

Instance ID
Instance ID is not a new, but yet a new, concept. Prior to SQL Server 2008, SQL Server when installing multiple instances enumerated the directories used for the dedicated binaries by number. Clearly that was not the most descriptive thing to do since you could not necessarily tell which binaries you were looking at. In SQL Server 2008 and later, this value can be set by you during Setup. It cannot be changed after, so you definitely have to get this right the first time. If you are installing a default instance, the default value for Instance ID is MSSQLSERVER and if you are installing a named instance, it is the name you specified for the named instance (for example, THEATER for the instance shown above).

However, even that on a cluster is not necessarily the best thing to do since you generally don’t access an instance via MSSQLSERVER or BALL, and the names are different than the nodes (for example, you wouldn’t connect using DENNISBALL). So what I recommend is that you use an Instance ID that is reflective of the name you are actually going to use, such as CRYSTAL_BALL. Figure 13 shows what things look like under the Program FilesMicrosoft SQL Server folder with properly named Instance IDs for clustered instances.

Figure 13. Instance IDs for multiple clustered instances

Summary
Names matter when you configure your clustered instance. Knowing the difference between the different configurable values versus the ones in the operating system will help you get things deployed properly the first time.