Default SQL instancr

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?
Ikky786Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bbaoIT ConsultantCommented:
basically you cannot simply rename a SQL instance once installed. yoy may however uninstall the instance and reinstall it with a different instance name.
Mark WillsTopic AdvisorCommented:
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.
Scott PletcherSenior DBACommented:
>> 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.
Mark WillsTopic AdvisorCommented:
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.