?
Solved

Default SQL instancr

Posted on 2014-04-20
5
Medium Priority
?
261 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
[X]
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
5 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 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 400 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 800 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 400 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 800 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

752 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