none
where is the second instance?

    Question

  • Hi,

    checking old SQL... please help me to demystify instance existence...

    In ManStudio I see one instance SQL Server 9 (that is 2005), but in Setup Discovery second instance appear - 2008 R2.

    ?

    Thanks.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Friday, February 17, 2017 1:34 PM

Answers

  • Have you tried looking in All Programs from Start menu of Windows?

    Or, you can choose Start > Run then enter this command to launch SQL Server Configuration Manager:

    When SQL Server Configuration Manager launches, you should be able to see all SQL instances installed on your machine.


    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, February 17, 2017 4:06 PM
    • Marked as answer by pob579 Monday, February 20, 2017 3:39 PM
    Friday, February 17, 2017 1:39 PM
  • Each instance should have a distinct TCP/IP port assigned which you can check in 'SQL Server Network Configuration'.

    Notice in your screenshot that one instance is under 'SQL Server Network Configuration (32bit)' node and the other instance is under 'SQL Server Network Configuration' node.

    Look up the TCP/IP port assignment for each and then include that when you try and connect to instance from SSMS:

    For example:

    HostName,2754

    HostName,1433

    (replace the port number in my example above with the actual port numbers you lookup on you instances)

    In SSMS, click the 'Connect' dropdown and type in hostname,port# 

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, February 17, 2017 4:22 PM
    • Marked as answer by pob579 Monday, February 20, 2017 3:40 PM
    Friday, February 17, 2017 4:18 PM
  • for Shanky

    for Phil


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Yes their are 2 instances

    1. Named instance MSSQL listening on Dynamic port

    2 Default instance which gets by default name MSSQLSERVER listening on default post 1433

    You can connect to named instance like hostname\MSSQL and named instance like Hostname


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by pob579 Monday, February 20, 2017 3:40 PM
    Friday, February 17, 2017 5:34 PM
    Moderator
  • Hi pob579,

    Firstly, as mentioned by Shanky_621, two instances can be listed in one SSMS interface, but you need to login them respectively. It is shown in below picture:



    Secondly, according to your description, this issue can be caused by that you have not enabled Mixed Authentication, you need to use Windows Authentication to login. Does this instance installed yourself? Which Windows account did you use to install this instance? You need to use this Windows account to login to Windows and then open this instance.

    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.

    • Marked as answer by pob579 Monday, February 20, 2017 3:41 PM
    Monday, February 20, 2017 2:25 AM

All replies

  • Have you tried looking in All Programs from Start menu of Windows?

    Or, you can choose Start > Run then enter this command to launch SQL Server Configuration Manager:

    When SQL Server Configuration Manager launches, you should be able to see all SQL instances installed on your machine.


    HTH,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, February 17, 2017 4:06 PM
    • Marked as answer by pob579 Monday, February 20, 2017 3:39 PM
    Friday, February 17, 2017 1:39 PM
  • When you look at the services, do you see services for SQL Server (MSSQL)? Are they running? Can you connect to ".\MSSQL" through SSMS?

    Thanks,
    Sam Lester (MSFT)


    https://blogs.msdn.microsoft.com/samlester/

    Twitter - @SQLSamLester

    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

    Friday, February 17, 2017 1:40 PM
    Moderator
  • Checked configuration manager. There are 2 instances...

    Is it normal that in SSMS I see only one - 2005?.... That what is confusing me.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Friday, February 17, 2017 3:47 PM
  • There seems to be 2 instance one is SQL Server 2005 and other is SQL Server 2008 r2.

    Can you go to SQL Server configuration manager and see how many instances are listed. Use SSCM of SQL Server 2008 r2.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 17, 2017 4:00 PM
    Moderator
  • Each instance should have a distinct TCP/IP port assigned which you can check in 'SQL Server Network Configuration'.

    Notice in your screenshot that one instance is under 'SQL Server Network Configuration (32bit)' node and the other instance is under 'SQL Server Network Configuration' node.

    Look up the TCP/IP port assignment for each and then include that when you try and connect to instance from SSMS:

    For example:

    HostName,2754

    HostName,1433

    (replace the port number in my example above with the actual port numbers you lookup on you instances)

    In SSMS, click the 'Connect' dropdown and type in hostname,port# 

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, February 17, 2017 4:22 PM
    • Marked as answer by pob579 Monday, February 20, 2017 3:40 PM
    Friday, February 17, 2017 4:18 PM
  • for Shanky

    for Phil


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Friday, February 17, 2017 5:04 PM
  • for Shanky

    for Phil


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Yes their are 2 instances

    1. Named instance MSSQL listening on Dynamic port

    2 Default instance which gets by default name MSSQLSERVER listening on default post 1433

    You can connect to named instance like hostname\MSSQL and named instance like Hostname


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by pob579 Monday, February 20, 2017 3:40 PM
    Friday, February 17, 2017 5:34 PM
    Moderator
  • I have no doubt that there are 2 instances. I guess it is clear from the very first post (see Setup Discovery report screenshot)

    The question is why I don't see both in SSMS? or it is normal when there are 2...


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis



    • Edited by pob579 Friday, February 17, 2017 6:23 PM
    Friday, February 17, 2017 6:00 PM
  • I have no doubt that there are 2 instances. I guess it is clear from the very first post (see Setup Discovery report screenshot)

    The question is why I don't see both in SSMS? or it is normal when there are 2...


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis




    You have 2 instances for sure. Just connect using way I have outlined in my previous reply

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, February 17, 2017 6:25 PM
    Moderator
  • Shanky,

    I tried to connect using server_name\mssql. I got two errors (intermittently).

    Checking errors on web showing it most likely related to  non mixed authentication. In SQL Config Man I found that MSSQL instance is running under .\sqlsrv name.

    SA and Windows account credentials of sysadmin for accessible instance (MSSQLSERVER) not working for MSSQL instance.

    I would like to access this instance for checking if it does have any DBs.

    Accessible one has 30 DBs. So I will try to "play" with password for "sqlsrv" local user. Sure I can change a password, but prefer to be cautious... for not breaking something.

    What do you think can I change the local admin password in order to try to connect to the DB in question. Then I can connect to the VM console as local admin and try to access.

    In real life I don't need this instance, since the DB I need to export is under accessible instance.

    But just out of curiosity I would like to access the instance and see if there are any DBs.

    I tried to connect to the DB in question with Domain_Admin credentials and use Windows authentication it gives me the same errors as SA. The second generated in French(Server is Fr OS). But error 233 on web is a right translation of French message. And it requires some workaround. May be will take a look later. No rush now.

    Just to clarify the question...

    When  2 (or more) instances do exist, could they be listed in one SSMS opened?

    Sure that this server was installed without "head"... There are other things that obviously not right...

    Thanks.

    then after close/reopen SSMS:


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Friday, February 17, 2017 7:34 PM
  • >When  2 (or more) instances do exist, could they be listed in one SSMS opened?

    Yes they can be listed unless one was uninstalled and was uninstalled incorrectly leaving some of the features. Did you clicked on servername and searched the SQL Server instance over network. I am telling about below


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, February 18, 2017 4:39 AM
    Moderator
  • Hi pob579,

    Firstly, as mentioned by Shanky_621, two instances can be listed in one SSMS interface, but you need to login them respectively. It is shown in below picture:



    Secondly, according to your description, this issue can be caused by that you have not enabled Mixed Authentication, you need to use Windows Authentication to login. Does this instance installed yourself? Which Windows account did you use to install this instance? You need to use this Windows account to login to Windows and then open this instance.

    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.

    • Marked as answer by pob579 Monday, February 20, 2017 3:41 PM
    Monday, February 20, 2017 2:25 AM
  • Teige,

    it is not me who installed the server. There are no info about it. I am setting up a new one and just digging in for finding as much info as possible.

    Finally, after running Data Migration Assistant against the SQL Server in question I found that all DBs were verified. And they all listed under 2005 instance in SSMS. It just confirms that there are no DBs in  MSSQL (2008) instance.

    1. thanks for confirming that 2 instances should/must appear in the same console. I felt that it should be like this.

    But being logged in with domain admin credentials to the server was able to see just one instance.

    Sure Domain Admin doesn't have sysadmin Role. But at least for listing instances it should be OK I believe.

    And this is probably strange regardless authentication set during SQL install

    2. seeing that MSSQL instance running under .\sqlsrv I tried to login with this account as SA. But the password not works.

    As mentioned above running Data Migration Assistant allowed to conclude that there are no DBs in inaccessible instance. So it makes no sense to spent time for further troubleshooting.

    Thanks to all for advises.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis


    • Edited by pob579 Tuesday, February 21, 2017 3:46 PM
    Monday, February 20, 2017 2:32 PM
  • wanted to ask how to get the Browse for Servers box mentioned by Shanky...

    Don't need it now but for future... when right click on server in SSMS don't see the option... probably other place.


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Tuesday, February 21, 2017 3:51 PM
  • You would have to click on network server tab and it would search SQL Servers available subject to condition the servers are visible on network

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, February 21, 2017 5:16 PM
    Moderator
  • > wanted to ask how to get the Browse for Servers box mentioned by Shanky...

    where is the object from which to call Browse for servers window please? then I will click the Network Servers tab.

    Where to start?


    --- When you hit a wrong note its the next note that makes it good or bad. --- Miles Davis

    Tuesday, February 21, 2017 6:53 PM