Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Default SQL instancr

Posted on 2014-04-20
5
249 Views
Last Modified: 2014-05-12
when I FIRST install SQL will that become the default instance forever on that particular server .

Lets say I install another copy of SQL , can I then change the default instance to this copy. Or is it that the FIRST installation of SQL ALWAYS remains the default instance?
0
Comment
Question by:Ikky786
5 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 40011435
when I FIRST install SQL will that become the default instance forever on that particular server .
You don't have to install as a default instance.  You can install as named instance instead.
0
 
LVL 37

Assisted Solution

by:bbao
bbao earned 100 total points
ID: 40011481
basically you cannot simply rename a SQL instance once installed. yoy may however uninstall the instance and reinstall it with a different instance name.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 200 total points
ID: 40012030
If you first installed as default, then yes, it remains default.

Your 2nd copy must then be installed as a named instance if you already have a default instance.

There can only be 1 default instance, and no, it cannot be renamed without uninstall and reinstall.

Of course you don't need a default instance, they can all be named instances (including just the first).

Connection strings must reflect the correct instance.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 40012544
>> when I FIRST install SQL will that become the default instance forever on that particular server . <<
Yes, until/unless you uninstall it.


>> Lets say I install another copy of SQL , can I then change the default instance to this copy. <<
No.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 200 total points
ID: 40012730
just to be clear... When you FIRST install SQL is doesn't have to be installed as the default instance. Because it is the first install, doesn't mean that being first it will automatically become the default instance. You can install it as a named instance.

That also means that your 2nd copy could become the default instance if there isn't already a default instance.

You don't actually need to have a default instance at all if you don't want - they can all be named instances.

You do need to understand the why's and wherefores of named v default before you install.

Very basically, the idea behind a "default" instance (ie not named) is so your apps can simply connect to sql server by referring to the server name (ie the machine where SQL was installed). The "default" instance communicates via port 1433. so your connection would be something like "mymachine" being the server name (or the IP address)

When you have named instances, the standard setting is to have dynamic ports. Once a connection is made, then that port number is used for all remaining communications.

The SQL Server Browser Service is the beast responsible - if you like, an SQL DNS type resolver (or more accurately SQL instance name resolver). So, once connected to "mymachine\mysqlinstance" it then uses that dynamic port from that point forward. The instance name is no longer needed because it now knows the "port" and that becomes "fixed" for the duration.

Dynamic Ports do make it easy to a degree... But you don't have to use dynamic ports - in fact it can sometimes catch you out across a firewall.

So, we often configure SQL Server with known / specific ports. 1433 is obvious enough, but with multiple SQL instances it does need to be managed (in my opinion).

If you were to configure your instances such that the ONLY ONE instance was using port 1433, then that will behave as the default instance.

While your full connection string for a named instance would normally be something like "mymachine\mysqlinstance,whateverportnumber" after configuring specific ports, it then simply becomes "mymachine,whateverportnumber" and the sql instance name is not needed. For a default instance, that connection would be "mymachine,1433"

If your actual default (ie unnamed instance) was configured to use port 1444 and your named instance configured to use 1433 then you have essentially changed roles of the SQL Server instances. So, take control of your port numbers and you can essentially trick sql server into using a named instance as a default instance.

Hope that made sense...
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question