4

Can the default instance name be something other than (local) or (machinename)? For example, if my server is called Apple, and I install SQL Server 2008 and choose to create a default instance and name it Orange, will this work? Will I then be able to use a connection string that is "Database=Orange" from another server?

The end goal: I have a lot of DBs on a server Apple, and want them to all be moved to a new server Orange - is there a way of doing this without breaking all the connection strings that currently reference Apple?

I'm guessing the answer to all of this is no and that makes sense, but I'm hoping I'm wrong.

0

2 Answers 2

4

There's no way I know of to do this. The default instance is always the name of the computer that is running SQL Server.

However, you can create an alias for the server on the client machine. You can create a registry key on the client like this:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
"APPLE"="DBMSSOCN,ORANGE"

This will point any calls to Apple to the Orange server.

2
  • 2
    Technically, all instance names are of the form MACHINENAME\INSTANCENAME, and the default instance's name is always MSSQLSERVER. Referring to an instance by the machinename is just an abbreviated form for when the instance name is MSSQLSERVER. There is no way to change that. Brian's technique to remap names would work to re-point all of your connection strings to the new server, but keep in mind that this has to be done on each and every client that accesses the database. Feb 22, 2010 at 19:09
  • Spot on Ed - you are entirely correct! :) Feb 22, 2010 at 19:15
2

This is totally possible using DNS or client aliases. We actually prefer to give each instance a dedicated DNS alias to use (say APP1.COMPANY.COM is a CNAME record to SERVER1.COMPANY.COM) so that when I want to move SERVER1.COMPANY.COM the default instance installed on this server I can just install on the new server, backup and restore the databases, then point the CNAME record from SERVER1.COMPANY.COM to SERVER2.COMPANY.COM and the client never knows the difference. In the case of a named instance it would work the same way, the client would be using APP1.COMPANY.COM\INST1 to point to SERVER1.COMPANY.COM\INST1 and the same case would be true.

To make sure that the logins are moved you will want to look into using sp_help_revlogin (search for it) to export/import the instances users, or restore the master database.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .