none
many SQL instances on server - determine which active - remove others?

    Question

  • I am doing some learning to prepare for a new VM instance of Windows Server 2012 Datacenter hosting SharePoint 2013 Foundation. Looking at a similar VM instance hosting an intranet data portal (C#/.Net) and an SQL based document archive, PaperVision. Running command prompt with "OSQL -L" I see my similar VM server listed twice as EOWYN and EOWYN\SQLEXPRESS2012.

    Connecting to my similar server via SQL Server Studio Management to each instance I see the version listed at 11.0.6020.0 for both connections and I see the same dB in both cases. I assume this means I have only one instance with two Aliases.

    Looking at the installed programs I see a few more software instances. See images.

    1. What SQL versions are installed by default by Server 2012 Datacenter? Which versions are installed by default on an SQL Server?

    I assume other SQL versions were installed via VisualStudio/SQL Studio.

    2. Are all the listed SQL installations concurrently operational as seen in my installed programs listing? How do I determine which version is active? My command line query indicated two instances.

    3. For a single purpose server, such as my to be deployed SharePoint instance, should I only have one instance of SQL?

    4. For a server with more than one hosted application, should I remove all SQL instances but the active one(s)?

    5. If more than one vintage, as my list of installed programs suggests, how do I connect VS to each vintage of SQL?

    Thanks in advance for you assistance. Any best practices document covering such Server/SQL 101 questions would be appreciated.

    Regards,

    Kurt

    Tuesday, January 3, 2017 6:57 PM

Answers

  • Hi Kurt,

    According to your description, my understanding is that you use an installation media of SQL Server to install one instance, after that you install Visual Studio, however you find that some strange instances are installed on your machine and they are not installed by yourself. First of all, the conclusion is that these instances you have seen which are not installed by you are localdbs which are installed by Visual Studio, if you did not need them, you can delete them. 

    For the first question, I think you use the installation media to install one instance, by default it will be a default instance, you can find the default instance from Setup Discovery Report described in the second part and find the version of it.

    For the second question, to find all instances installed on your machine and their version and related feature, you can get the Setup Discovery Report, for more information about how to get this list, please refer to this article. The question related to the active instance, please refer to the reply given from Marianok.

    For the 3nd question, please refer to the reply of Olaf, for the 5th question, please describe it more clearly(some screen shot can be better).

    For the forth question, have you used these localdb to develop the application, if not, you can delete them, if it is not clear for you, please don't delete. More information about how to delete localdb, you can refer to this case.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 5, 2017 7:52 AM
    Moderator
  • Hi Kurt

    Let's go for individual questions:

    1) As you have to instances of SQL Server running in your VM. I suggest that run a simple query on each instance in SQL Server Management Studio to determine the Edition :

    SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
    
    SERVERPROPERTY ('edition')

    As far I can saw in your screenshots they are Express edition both.

    2) You can check at Services Snap In which Instance is running. Each instance will have an independent service and between parenthesis the name of that instance. MSSQLSERVER is the default, which means that server name is the name of the SQL Server instance. Otherwise you should see a different name between pathethesis.

    3) You are correct. Multiple instances are meant to divide server resources and SQL server workloads in the same box, but maintaining SQL Server administration and Security isolated on each instance.

    4) Before removing any SQL Server instance, you should check is there is activity in any database or user connections first.

    5) By saying VS, you mean Visual Studio?

    Hope it helps!

    regards.

    Mariano K.


    Mariano K.

    Tuesday, January 3, 2017 8:04 PM

All replies

  • I assume this means I have only one instance with two Aliases.

    Hello Kurt,

    May be, may be not, that could be different SQL Server instances with databases having the same names. Lookup the created SQL Server alias with "CliConfg.exe" tool.

    1. What SQL versions are installed by default by Server 2012 Datacenter?

    None, you have to install SQL Server on your own.

    2. Are all the listed SQL installations concurrently operational

    That are not SQL Server installation, only some components of SQL Server in different version. Better keep them as they are.

    3. ... should I only have one instance of SQL?

    Depends on the requirements and existing resources, it is nothing unusual to have more then on SQL Server instances installed on one machine.

    4. For a server with more than one hosted application, should I remove all SQL instances but the active one(s)?

    Yet you don't know for sure if there are more then one SQL Server is running, and even if which one is used by which application.

    5. If more than one vintage, as my list of installed programs suggests, how do I connect VS to each vintage of SQL?

    What do you mean with "vintage" here?

    At all, it seems you have only less knowledge about SQL Server and in this case I like to suggest to keep the installation untouched, you could only harm (crash) your running system.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Tuesday, January 3, 2017 7:25 PM
  • Hi Kurt,

    Try running the following on both instances (both connections/servers). It looks like you have SQL 2012 Express and perhaps SQL 2012 developer installed on the machine.

    select serverproperty('edition')

    If the results are different then they truly are different instances. If the results are the same, definitely run CliConfg.exe to see what's running.


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)


    Tuesday, January 3, 2017 7:40 PM
  • Hi Kurt

    Let's go for individual questions:

    1) As you have to instances of SQL Server running in your VM. I suggest that run a simple query on each instance in SQL Server Management Studio to determine the Edition :

    SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
    
    SERVERPROPERTY ('edition')

    As far I can saw in your screenshots they are Express edition both.

    2) You can check at Services Snap In which Instance is running. Each instance will have an independent service and between parenthesis the name of that instance. MSSQLSERVER is the default, which means that server name is the name of the SQL Server instance. Otherwise you should see a different name between pathethesis.

    3) You are correct. Multiple instances are meant to divide server resources and SQL server workloads in the same box, but maintaining SQL Server administration and Security isolated on each instance.

    4) Before removing any SQL Server instance, you should check is there is activity in any database or user connections first.

    5) By saying VS, you mean Visual Studio?

    Hope it helps!

    regards.

    Mariano K.


    Mariano K.

    Tuesday, January 3, 2017 8:04 PM
  • select serverproperty('edition') returns the same value for both dB as shown in my Studio server connections. Express x64.

    Vintages, looking at my installed applications I see in order of appearance:

    A. SQL Server 2012

    B. SQL Server Express 2012 Local DB

    C. SQL Server 2014 Express Local DB

    D. SQL Server Compact


    • Edited by Swain IT Tuesday, January 3, 2017 8:11 PM
    Tuesday, January 3, 2017 8:08 PM
  • VS, yes, visual studio. Using your more complete query on each , I see identical results. Express x64, SP3, 11.0.6020
    Tuesday, January 3, 2017 8:39 PM
  • Hi Kurt,

    According to your description, my understanding is that you use an installation media of SQL Server to install one instance, after that you install Visual Studio, however you find that some strange instances are installed on your machine and they are not installed by yourself. First of all, the conclusion is that these instances you have seen which are not installed by you are localdbs which are installed by Visual Studio, if you did not need them, you can delete them. 

    For the first question, I think you use the installation media to install one instance, by default it will be a default instance, you can find the default instance from Setup Discovery Report described in the second part and find the version of it.

    For the second question, to find all instances installed on your machine and their version and related feature, you can get the Setup Discovery Report, for more information about how to get this list, please refer to this article. The question related to the active instance, please refer to the reply given from Marianok.

    For the 3nd question, please refer to the reply of Olaf, for the 5th question, please describe it more clearly(some screen shot can be better).

    For the forth question, have you used these localdb to develop the application, if not, you can delete them, if it is not clear for you, please don't delete. More information about how to delete localdb, you can refer to this case.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 5, 2017 7:52 AM
    Moderator
  • Being in a three person shop, I must wear many hats. I am occupied with a GIS report to distribute the tax dollars to each fire district based on property values within each district. I am 90% done. I will return to this ASAP. Sorry for the delay. Great responses so far. I will check the best solution.
    Thursday, January 5, 2017 10:04 PM
  • Hi Kurt,

    Have you checked it and got the better solution? We are looking forward to your reply.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 10, 2017 8:06 AM
    Moderator
  • I found only one instance running within Services. This was very helpful. Each reply provided some information. Thanks to all. Looking back at the installed applications after seeing the Setup discovery report, it is still confusing to see

    A. SQL Server 2012

    B. SQL Server Express 2012 Local DB

    C. SQL Server 2014 Express Local DB

    D. SQL Server Compact

    The VS local DB can be useful. I assume A is the Express2012 instance. I still have no clue what D is. I will leave all instances in tact for now. As I install things on the as yet to be created VM for SharePoint, I will make some screen shots to learn more about what installations produce which instances. I may have tried more than one vintage of VS. giving my Local DB as 2012 and 2014.

    Thanks for the lessons. KA

    • Edited by Swain IT Tuesday, January 10, 2017 4:39 PM expain better
    Tuesday, January 10, 2017 1:20 PM
  • This was quite helpful too. Thanks. If I understand this image, I only have one instance of SQL and one Visual Studio local dB.
    Tuesday, January 10, 2017 1:32 PM