Solved

Default SQL instancr

Posted on 2014-04-20
5
242 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:Bing CISM / CISSP
Bing CISM / CISSP 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:ScottPletcher
ScottPletcher 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ssms - object execution statistics 12 37
SQL Server RDS clr assembly 4 36
INSERT INTO SELECT JOIN THING 2 26
c# code 19 59
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

757 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now