locked
Instances RRS feed

  • Question

  • What is instance.

    Maximum how many number of instances can be created in Sql Server?

    And what are the major difference between Default Instance And Named Instance?


    Thanks in Advance.



    Wednesday, April 16, 2014 12:41 PM

Answers

  • According to below, you can have 50 instances, except for workgroup where 16 is supported. Unless on a cluster where 25 are supported:

    QL Server Database Engine object

    Maximum sizes/numbers SQL Server (32-bit)

    Maximum sizes/numbers  SQL Server (64-bit)

    Columns per foreign key

    16

    16

    Columns per primary key

    16

    16

    Columns per SELECT statement

    4,096

    4,096

    Columns per INSERT statement

    4096

    4096

    Database size

    524,272 terabytes

    524,272 terabytes

    Databases per instance of SQL Server

    32,767

    32,767

    Filegroups per database

    32,767

    32,767

    Files per database

    32,767

    32,767

    File size (data)

    16 terabytes

    16 terabytes

    File size (log)

    2 terabytes

    2 terabytes

    Instances per computer

    50 instances on a stand-alone server for all SQL

    Server editions. SQL Server supports 25 instances

    on a failover cluster.

    50 instances  on a stand-alone  server. 25 instances  on a failover  cluster.

    Rows per table

    Limited by available storage

    Limited by available  storage

    DEFAULT INSTANCE

    A DEFAULT INSTANCE  has much the same profile that SQL Server installations have had in past; you install SQL Server and then connect using the computer name of the server. Your Windows Server can only have one computer name, so you can only use it to connect to one SQL Server instance. This is called the default instance.

    NAMED INSTANCE

    If you install additional instances of SQL Server, these are referred to as NAMED INSTANCE. You connect to them using the <computername><instancename>format. For example, if you have a server named JOHN and you install a named instance named SQL1, you would connect to that instance using jOHNSQL1. As you can see the above table you can have upto 50 instances right? so If you try to install another named instance called SQL2, you would connect using JOHN SQL2 and on.

    Check Microsoft website for more deep info. 

    (http://msdn.microsoft.com/en-us/library/ms143432.aspx)

    And if you are curious to How to find the instances that's installed?

    To find the instance name of an instance of SQL Server, please follow the steps below:

    1. Open SQL Server Configuration Manager (SSCM) from Start - > All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools;
    2. In the SSCM, click SQL Server Services under SQL Server Configuration Manager (Local), you could find list times like SQL Server (<InstanceName>), the<InstanceName> is exactly what we need to find, instance name. If you install multiple instances on the server, you will see more than one SQL Server (<InstanceName>) list times there.


    • Proposed as answer by Praveen Rayan D'sa Thursday, April 17, 2014 4:48 AM
    • Edited by Abubeker Refaw Monday, April 21, 2014 5:21 PM font size
    • Marked as answer by Sofiya Li Wednesday, April 23, 2014 8:43 AM
    Wednesday, April 16, 2014 1:31 PM
  • Hello Satish,

    a "Default instance" is a nameless instance; you can have max one per machine.

    For a General handling of Default & named instances it's shown up with "SQL Server (MSSQLSERVER)" in Windows Service Manager and some other places, e.g. in Registry; because having serveral instance on one machine was first introduced in SQL Server 7.0 as a new Feature.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Sofiya Li Wednesday, April 23, 2014 8:42 AM
    Thursday, April 17, 2014 9:55 AM
  • I guess Olaf answered  your question. MSSQLSERVER is default instance of sql server which you install without giving any name and so this name ( MSSQLSERVER) is taken.And since you can install multiple instances on same windows box, and  if you want to install new instance you will have to provide name for it.When installing SQL server you have option to install default instance or named instance as Olaf said you can have only one default instance.You can install named instance without installing default instance its totally up to you how you move.

    When you execute select @@Servername it will give SQL instance name .If it is named instance it will give like machinename\instancename if it is default it will give Machinename .

    For @@Servername refer to this link

    http://technet.microsoft.com/en-us/library/ms187944.aspx

    Hope this is clear


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by Sofiya Li Wednesday, April 23, 2014 8:42 AM
    Thursday, April 17, 2014 10:09 AM
  • As per me 50 SQL instances are maximum we can install on server

    For difference in instances check below thread

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2b78115e-67fa-4954-9538-5b10f8785965/whats-the-difference-between-installing-a-default-instance-vs-a-named-instance?forum=sqlgetstarted


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Marked as answer by Sofiya Li Wednesday, April 23, 2014 8:43 AM
    Wednesday, April 16, 2014 12:51 PM
  • The instances are children. So each child must have some thing to identify it. Thus, one of the children can be a child with no name. That child with no name is the default instance (it is MSSQL Server). The others will each have a name and will be identified by the name in the house.

    ---------------------------------------

    I have seen in many blogs the default instance will be MSSQL SEREVR ,here my question is when am installing sql server 2008 r2 i went with default name , But when i executed a query 

    SELECT @@ServerName

    i get O/P as : Satish (which is my computer name)

    But every where it is said as MSSQL SEREVR , Except in services (Services.Msc)  i haven't seen MSSQL SERVER in other places. 

    And i heard even SQL SEREVR AGENT will be also comes as Default...

    Can some one please explain me clearly ,

    Thanks in advance......



    Hi Satish Reddy G,

    You can also check the default instance name in SQL Server Configuration Manager. In SQL Server Services page, if you install the default instance, it will display “SQL Server (MSSQLSERVER)” and “SQL Server Agent(MSSQLSERVER).  When you want to connect to the default instance via typing server name, it will resolved to the instance name. You can also connect to the default instance via typing the following format.

    <Server name>\MSSQLSERVER,1433

    In addition, if you install 50 SQL Server 2008R2 instances in one Server, you could not install the 51th instance no matter if the version is SQL Server 2012 or others.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Wednesday, April 23, 2014 8:43 AM
    Thursday, April 17, 2014 7:55 AM

All replies

  • please see the link bellow

    http://msdn.microsoft.com/en-us/library/ms143432.aspx

    what are the major difference between Default Instance And Named Instance?

    well there is no major difference, think like two full version of sql server running on the same server, that could be same version or different version

    to connect to the default instance you just use the name of the server but to connect to the named instance you need to specify the name of the server

    e.g mypc\sql2008

    vt 


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker



    • Edited by SimpleSQL Wednesday, April 16, 2014 12:53 PM
    Wednesday, April 16, 2014 12:50 PM
  • As per me 50 SQL instances are maximum we can install on server

    For difference in instances check below thread

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2b78115e-67fa-4954-9538-5b10f8785965/whats-the-difference-between-installing-a-default-instance-vs-a-named-instance?forum=sqlgetstarted


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    • Marked as answer by Sofiya Li Wednesday, April 23, 2014 8:43 AM
    Wednesday, April 16, 2014 12:51 PM
  • Hello,

    SQL Server maximum capacity has been documented here

    http://technet.microsoft.com/en-us/library/ms143432.aspx

    Instance is just a separate installation of SQL server .

    http://technet.microsoft.com/en-us/library/aa174516(v=sql.80).aspx

    Make a habit of searching net for simple questions

    >>And what are the major difference between Default Instance And Named Instance?

    Funny indeed


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Wednesday, April 16, 2014 12:51 PM
  • According to below, you can have 50 instances, except for workgroup where 16 is supported. Unless on a cluster where 25 are supported:

    QL Server Database Engine object

    Maximum sizes/numbers SQL Server (32-bit)

    Maximum sizes/numbers  SQL Server (64-bit)

    Columns per foreign key

    16

    16

    Columns per primary key

    16

    16

    Columns per SELECT statement

    4,096

    4,096

    Columns per INSERT statement

    4096

    4096

    Database size

    524,272 terabytes

    524,272 terabytes

    Databases per instance of SQL Server

    32,767

    32,767

    Filegroups per database

    32,767

    32,767

    Files per database

    32,767

    32,767

    File size (data)

    16 terabytes

    16 terabytes

    File size (log)

    2 terabytes

    2 terabytes

    Instances per computer

    50 instances on a stand-alone server for all SQL

    Server editions. SQL Server supports 25 instances

    on a failover cluster.

    50 instances  on a stand-alone  server. 25 instances  on a failover  cluster.

    Rows per table

    Limited by available storage

    Limited by available  storage

    DEFAULT INSTANCE

    A DEFAULT INSTANCE  has much the same profile that SQL Server installations have had in past; you install SQL Server and then connect using the computer name of the server. Your Windows Server can only have one computer name, so you can only use it to connect to one SQL Server instance. This is called the default instance.

    NAMED INSTANCE

    If you install additional instances of SQL Server, these are referred to as NAMED INSTANCE. You connect to them using the <computername><instancename>format. For example, if you have a server named JOHN and you install a named instance named SQL1, you would connect to that instance using jOHNSQL1. As you can see the above table you can have upto 50 instances right? so If you try to install another named instance called SQL2, you would connect using JOHN SQL2 and on.

    Check Microsoft website for more deep info. 

    (http://msdn.microsoft.com/en-us/library/ms143432.aspx)

    And if you are curious to How to find the instances that's installed?

    To find the instance name of an instance of SQL Server, please follow the steps below:

    1. Open SQL Server Configuration Manager (SSCM) from Start - > All Programs -> Microsoft SQL Server 2005/2008/2008 R2 -> Configuration Tools;
    2. In the SSCM, click SQL Server Services under SQL Server Configuration Manager (Local), you could find list times like SQL Server (<InstanceName>), the<InstanceName> is exactly what we need to find, instance name. If you install multiple instances on the server, you will see more than one SQL Server (<InstanceName>) list times there.


    • Proposed as answer by Praveen Rayan D'sa Thursday, April 17, 2014 4:48 AM
    • Edited by Abubeker Refaw Monday, April 21, 2014 5:21 PM font size
    • Marked as answer by Sofiya Li Wednesday, April 23, 2014 8:43 AM
    Wednesday, April 16, 2014 1:31 PM
  • The best way to look at it is considering Instances as children in one house. The house is the computer(machine on which you want to install SQL Server).

    The instances are children. So each child must have some thing to identify it. Thus, one of the children can be a child with no name. That child with no name is the default instance (it is MSSQL Server). The others will each have a name and will be identified by the name in the house.

    so when you want to connect to a named instance, you put MACHINENAME\NAMEOFINSTANCE. AND THEN OF COURSE YOU WILL HAVE to pass the user name and pass word.

    To connect to the default instance, you just need to put the name of the machine and do not need to put any name for the default instance does not have a name. Thus, MACHINENAME!!

    Always remember that when you want to connect to a given instance, you go to your machine(computer), then open the SSMS, and then put the name of the machine followed by the name of the instance.

    Then after, you will be inside the instance you want to be in.

    How many instances allowed see the other links. To my knowledge, it is 50.


    ebro

    Wednesday, April 16, 2014 2:48 PM
  • Thanks Ebro..

    great explanation, i have been to many blogs but am not clear.Now am ok... :)

    Microsoft says 50 instances can be created in a machine.

    Is it limited with respect to the versions or not.

    Example if i installed 2008R2 and i have 50 instances , Then if i try 2012 can i create another instance(NO:51) 

    do machine support the instance to be installed.

    Thursday, April 17, 2014 4:27 AM
  • The instances are children. So each child must have some thing to identify it. Thus, one of the children can be a child with no name. That child with no name is the default instance (it is MSSQL Server). The others will each have a name and will be identified by the name in the house.

    ---------------------------------------

    I have seen in many blogs the default instance will be MSSQL SEREVR ,here my question is when am installing sql server 2008 r2 i went with default name , But when i executed a query 

    SELECT @@ServerName

    i get O/P as : Satish (which is my computer name)

    But every where it is said as MSSQL SEREVR , Except in services (Services.Msc)  i haven't seen MSSQL SERVER in other places. 

    And i heard even SQL SEREVR AGENT will be also comes as Default...

    Can some one please explain me clearly ,

    Thanks in advance......


    Thursday, April 17, 2014 5:55 AM
  • The instances are children. So each child must have some thing to identify it. Thus, one of the children can be a child with no name. That child with no name is the default instance (it is MSSQL Server). The others will each have a name and will be identified by the name in the house.

    ---------------------------------------

    I have seen in many blogs the default instance will be MSSQL SEREVR ,here my question is when am installing sql server 2008 r2 i went with default name , But when i executed a query 

    SELECT @@ServerName

    i get O/P as : Satish (which is my computer name)

    But every where it is said as MSSQL SEREVR , Except in services (Services.Msc)  i haven't seen MSSQL SERVER in other places. 

    And i heard even SQL SEREVR AGENT will be also comes as Default...

    Can some one please explain me clearly ,

    Thanks in advance......



    Hi Satish Reddy G,

    You can also check the default instance name in SQL Server Configuration Manager. In SQL Server Services page, if you install the default instance, it will display “SQL Server (MSSQLSERVER)” and “SQL Server Agent(MSSQLSERVER).  When you want to connect to the default instance via typing server name, it will resolved to the instance name. You can also connect to the default instance via typing the following format.

    <Server name>\MSSQLSERVER,1433

    In addition, if you install 50 SQL Server 2008R2 instances in one Server, you could not install the 51th instance no matter if the version is SQL Server 2012 or others.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    • Marked as answer by Sofiya Li Wednesday, April 23, 2014 8:43 AM
    Thursday, April 17, 2014 7:55 AM
  • Thanks Ebro..

    great explanation, i have been to many blogs but am not clear.Now am ok... :)

    Microsoft says 50 instances can be created in a machine.

    Is it limited with respect to the versions or not.

    Example if i installed 2008R2 and i have 50 instances , Then if i try 2012 can i create another instance(NO:51) 

    do machine support the instance to be installed.

    This post of your clearly means that you dont refer to links posted but depends on ready made answer.What Abubeker pasted was fragment from the Capacity link we posted.You dont want to do any work and just post on  forum for ready made answers ,i have see this from you lot of time.Nonetheless you can keep posting .I would avoid posting any further on your posts.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, April 17, 2014 8:01 AM
  • Thanks...
    Thursday, April 17, 2014 8:33 AM
  • Shanky_621,

    i went through all the blogs , but i was confused like it says MSSQL Server will be a default name but when i check for a serve name using sql command it shows the Computer name , there i was confused.

    Blog says it will be MSSQL SERVER but it is showing my computer name.

    so, to make it clear i was moving into every corner regarding instance.

    Thursday, April 17, 2014 8:38 AM
  • Shanky_621,

    i went through all the blogs , but i was confused like it says MSSQL Server will be a default name but when i check for a serve name using sql command it shows the Computer name , there i was confused.

    Blog says it will be MSSQL SERVER but it is showing my computer name.

    so, to make it clear i was moving into every corner regarding instance.

    Buddy please dont take it in wrong manner,I just want you to do some manual work because that would force you to read and in turn will provide you more knowledge. Forum is not just to simply paste answer it is also about motivating OP to read and gain more knowledge ,just posting answer will provide solution but make OP a kind of spoon fed.  I believe in one should always point a horse in right direction of well rather than taking him personally and showing him how to drink'. Hope you get my point.My Emailid is present in Biography section of my profile feel free to mail me about your issues . I would be happy to help

    Also please unmark the answer which you just marked .I guess V.vt gave you first correct link so logically his answer shoudl be marked.

    Hope this helps

    Cheers!!!


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, April 17, 2014 9:24 AM
  • it happened unknowingly  , i need you to answer my questions .

    I went through blogs am confused on this issue particularly ( Regarding Default instance it says MSSQL Server will be the default name) , i get the same MSSQL SERVER in Services and also in Configuration manager ,

    But when i execute Select @@Servername it shows my machine name.

    While am in search of instances, many of my questions got answered , like how many DB can be created , Failover Clusters and more...

    But Screwed up here on Default instance name (MSSQL SERVER) .....

    This may not be a big issue but want to clear this .

     


    Thursday, April 17, 2014 9:38 AM
  • Hello Satish,

    a "Default instance" is a nameless instance; you can have max one per machine.

    For a General handling of Default & named instances it's shown up with "SQL Server (MSSQLSERVER)" in Windows Service Manager and some other places, e.g. in Registry; because having serveral instance on one machine was first introduced in SQL Server 7.0 as a new Feature.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Sofiya Li Wednesday, April 23, 2014 8:42 AM
    Thursday, April 17, 2014 9:55 AM
  • I guess Olaf answered  your question. MSSQLSERVER is default instance of sql server which you install without giving any name and so this name ( MSSQLSERVER) is taken.And since you can install multiple instances on same windows box, and  if you want to install new instance you will have to provide name for it.When installing SQL server you have option to install default instance or named instance as Olaf said you can have only one default instance.You can install named instance without installing default instance its totally up to you how you move.

    When you execute select @@Servername it will give SQL instance name .If it is named instance it will give like machinename\instancename if it is default it will give Machinename .

    For @@Servername refer to this link

    http://technet.microsoft.com/en-us/library/ms187944.aspx

    Hope this is clear


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Marked as answer by Sofiya Li Wednesday, April 23, 2014 8:42 AM
    Thursday, April 17, 2014 10:09 AM
  • Thanks all....
    Thursday, April 17, 2014 10:12 AM