Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 2, 2001

Using SMS with SQL 2000 Named Instances

By Jeff Huston

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 SQLIT\SQLIT_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 SQLIT\SQLIT_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 SQLIT\SQLIT_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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM