Solved

Default SQL instancr

Posted on 2014-04-20
5
247 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

777 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