Using SMS with SQL 2000 Named Instances

Because we are running SQL 2000 in a cluster, we were forced to use named instances by the setup utility. This resulted in a cluster virtual server named SQLIT, for instance, to have a named instance of SQL 2000 named SQLIT_Instance.

This is usually fine and dandy since you would typically use the server name and instance name whenever asked for the SQL Server (i.e., use SQLITSQLIT_Instance as the SQL Server).

However, when SMS 2.0 installs (or you reset the site), it checks the server’s services to see if the MSSQLServer service is installed and running. It is not Named Instance-aware and uses the server name as you’ve typed it to go out on the network and determine the state of SQL Server. Since the server is actually called SQLIT, SMS won’t be able to find it if you use SQLITSQLIT_Instance. If you use just SQLIT, then, once SMS has verified SQL Server, SMS won’t be able to connect to the database server since there is no default instance on SQLIT, just the SQLITSQLIT_Instance.

What to do?

Well, the Client Network Utility that comes with SQL 2000 solves this problem for you. If you click on the Alias tab, you can create an alias for your named instance that does not use the new format.

Click on Add to create the new alias. Type in the server name as the alias, then type in the server name/instance name combo in the Server name text box under connection parameters. Leave the network library as Named Pipes (since SMS requires the use of this library). Click OK. Click OK.

Now, when you complete the SMS install (or site reset), you can specify the alias name as the SQL Server. SMS will use the alias name (which we’ve conveniently made the server name) to check for SQL Server installation. Then, when it drops in to the database to start to set that up, the DB library will refer to this alias and connect to the correct server and named instance.

This little bit of trickery works well for SMS and may also work well of other older applications that can’t handle named instances for whatever reason.

Latest Articles