How to create default instance after creating a named instance?

Question

when installing SQLServer2005 developer edition (not express) I created a named instance:

MYSERVERNAME/MYINSTANCENAME

Is it possible to

1) change the name of my named instance to default:

MYSERVERNAME

OR

2) create a new default instance (MYSERVERNAME as above).

any help would be appreciated!

Answer 1

Hm, when I use the "Change" button in Add/Remove Programs, SQL Server 2005 setup offers me the instance selection screen, and even allows me to list the existing instances. (I'm running XP SP2, by the way.)

I selected the "To install a new component, click here" link in the setup program, then selected SQL Server Database Services on the "Components to Install" screen to arrive at the instance selection screen that allows me to install a new default instance.

Answer 2

Going off of Alan's answer, when you install a new instance as the default instance, take note of the directories it is using to store data and log files (or create a default location like D:\MSSQL\Log and D:\MSSQL\Data).

You can then detach the databases from the named instance and move the files to the new data and log directories and re-attach them in SSMS.

Answer 3

A default instance has a default instance name, but captures the computer's name, and uses it as an alias. Even if you change the computer name, the default instance will capture the new computer name, and use it as its alias. But the default instance name remains the same (like MSSQLSERVER).

This is why a default instance cannot be renamed. The default instance name is fixed, and it captures the computer name and uses it for its alias. Thus, only one (1) default instance can exist on a computer, because the default instance has a fixed name, and it always captures and uses the computer name for an alias. In other words, the default instance name is joined at the hip with the computer name (as an alias). Neither can be changed programmatically. Changing the computer name simply changes the alias attached to the default instance name.

If a default instance already exists, all other SQL installations on that computer will be forced to use a named instance (an alias, which cannot be the computer name).

Now in your case, during your first SQL installation, you chose to install it as a named instance, not a default instance. Thus you had no default instance, and thus you used an alias that was different from the computer name. You've never installed a default instance, therefore the installation did not capture the computer name for its alias.

The major thing about default instances is that they capture and use the computer name as an alias, and there is absolutely no way to programmatically change a default instance name. However, you can change its alias by changing the computer name. But the computer name is still joined to the default instance name.

An alias simply points to an SQL installation, so that the alias may use its engine. If the installation was not designated as a default instance, it simply means the installation is not attached at the hip with the computer name as its alias.

I hope this helps, but if this is clear as mud, please forgive me.