SQL Server 2005 -- name of the dataserver

Question

SQL Server 2005 -- name of the dataserver

sheppc1214

Right there with Babe

Points: 753
More actions
February 3, 2009 at 6:30 am

#155218

I recently took a hands-on course in SQL Server 2005 admin. I received the Developers edition on a CD which I installed at home on my HP desktop. I did a local system install and I named the SQL server ==> SQLSVR2005. I was hoping to see this name only but the name showed up as machine name\SQLSVR2005. I was hoping to just refer to my dataserver as SQLSVR2005. Why was it named that way? What did I do wrong. It would have been nice to do the following ... sqlcmd -U sa -S SQLSVR2005 etc.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

What you did was create an named instance. By default the server name (your computer) will be the Database instance name. When you specify a name, that means you dont want the Server name but an Instance in it.

You can do everything you want on that instance like you do normally. The only thing is that you have to specify the name as YourMachineName/InstanceName

-Roy

Lowell SSC Guru Points: 323482 More actions · Answer 2

when you install, you have the choice on installing a default instance or a named instance

if you chose default instance, the SQL server is the name of the machine....

sqlcmd -U sa -S YOURMACHINESNAME

this is great, because if you rename your machine, you rename your default instance as well....so if your machine was named DELLJSR4X01 or something, and you want to rename it to DATACENTER or SQLSERVER2005 or whatever, no problem.

a named instance is different as you saw, it's machine name\NAMEDINSTANCE. you can't renamed a named instance...you have to guard your data, uninstall that instance, and re-install with a new name. renaming the machine just renames part of the named instance...from machinename\SQL2005 to newmachine\SQL2005.

this is useful because you can have different versions of SQL running at the same time, i.e. SQL2000 instance, SQL 2005 instance and a SQL 2008 instance, all on the same machine.

A dedicated machine for production typically wouldn't have that, but a dev machine would .

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sheppc1214 Right there with Babe Points: 753 More actions · Answer 3

Thanks very much for your clarifications. It makes a whole lot of sense now.

Its great to have folks like you on SQLServerCentral!!!!

sheppc.

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 4

You can create aliases for your SQL Server instances. This is done through the SQL Server Configuration Manager so if you wanted you could create an alias called SQLSVR2005 which maps to your SQL instance. Then you can refer to this alias in connection strings.

2 Tim 3:16 SSCarpal Tunnel Points: 4481 More actions · Answer 5

Also, make sure the "Browser" service is started in SQL Config Mgr. By default, you can connect to a default (machine name only) instance using port 1433. However, a "Named" instance will start up with a different port number every time this is where the "Browser" service comes in. The Browser service will listen in on SQL traffic and pass back the port number for the named instance you want to connect to and then your connection will connect to the proper instance. Tis allows for mulitple instances on the same machine.

Tim White