CODEWITHSUNDEEP
sql-serversql-server-2005
JohnIdol
JohnIdol

Reputation: 50067

How to create default instance after creating a named instance?

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!

Upvotes: 13

Views: 73505

Answers (5)

Moe Eshaq
Moe Eshaq

Reputation: 31

If you have installed a named instance. You cannot change the name to default. You can however: * make the named instance listen to the default port. * OR create aliases (with name like MSSQLServer or the IP address, as a trick) to make your clients work. * If this workaround doesn't help then your best bet is to uninstall SQL Complete and re-install again but chances are that the new installation might take the named instance's name again even if you selected 'default instance'. You can verify this in the SQL services to see if it is still taking the old name. In this case, the best way (which worked for me) is to install a new instance with the explicit name of MSSQLServer which is known to be the default instance name.

Keep in mind also that SQL takes the computer name and uses it as an alias.

Upvotes: 0

Terence
Terence

Reputation: 21

Thanks. This worked for me with sql server 2008 r2 express as well.

I had a default instance of 2005 installed, and only a named instance for a subsequent install of 2008.

I uninstalled 2005 (Important: Copy any 2005 databases to a separate folder first).

Then I reinstalled 2008. You can get the installation exe from microsoft if you don't still have it.

  1. Start installation and select "new installation or add feature".
  2. Select option button for "new installation or add feature".
  3. Accept default instances feature. NEXT.
  4. Select default instance option. NEXT. (note instance ID is MSSQLSERVER).
  5. Continue to accept defauls unless you have a reason not to.

Upvotes: 2

Michael
Michael

Reputation: 1390

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.

Upvotes: 5

anon
anon

Reputation:

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.

Upvotes: 2

Alan
Alan

Reputation: 6669

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.

Upvotes: 7

Related Questions