none
Missing servers in SQL Server Detailed Reports

    Question

  • Hello,

    At the end of collecting inventory data, the SQL Server database reports only show 11 servers. MAP says 189 servers have been discovered and inventoried, but only 11 servers show up in the SQL Server Databases Detailed reports.  Can you identify what I am missing?  I have MAP Toolkit 8.5

    thanks.



    Paul


    • Edited by Paul Byrum Wednesday, July 17, 2013 10:20 PM
    Wednesday, July 17, 2013 9:51 PM

Answers

  • I found the problem.  SQL Server was not configure to be accessed remotely.

    By following these steps below, MAP Tool was about to get information from SQL Server. 

    • Open SQL Server Configuration Manager
    • Select SQL Server Network Configuration
    • Chose your instance of a SQL Server
    • Make sure that TCP/IP protocol is enabled
    • Right click TCP/IP protocol
    • Select properties
    • Click IP addresses tab
    • Scroll down to IP4. The IP address of the server should be here. Set active to yes and enabled to yes. Set TCP port to 1433 (don't know if this is necessary. Can some expert comment)
    • Scroll down to IPAll. Set TCP port to 1433
    • Make an inbound firewall rule for port 1433
    • open sql server management studio, right click server instance, properties->connections-> allow remote connections. Security-> SQL Server and Windows Authentication mode
    • restart sql server service
    • restart sql server browser


    Paul

    • Marked as answer by Paul Byrum Wednesday, August 14, 2013 11:01 PM
    Wednesday, August 14, 2013 11:01 PM

All replies

  • Hi Paul.

    I am trying to understand your situation. Please clarify:

    Are you looking at Windows Servers or SQL Servers count?
    Are you looking at the MAP UI or the backend (DB)?




    Thursday, July 18, 2013 8:06 PM
  • Hi,

    I am looking at SQL Server counts. I am looking for SQL Server product names, instances and versions of those instances.  

    I am looking at the MAP UI.

    I am using a domain account, but wondering if this has access to the SQL Servers to gather the data. 


    Paul

    Thursday, July 18, 2013 8:32 PM
  • "MAP says 189 servers have been discovered and inventoried"
    - you are probably reading the number of Windows Servers inventoried.

    MAP reports 11 properly inventoried SQL instances (does not have a 1 to 1 relationship with Win Serv).
    Are you expecting more?


    Friday, July 19, 2013 2:45 PM
  • Correct, those are 11 instances, on 8 servers.

    Yes, I am missing 5 servers or 12 or so instances on those servers. 

    I like to verify the type of account to use for MAP to access the servers. Does the account need to be a login in SQL Server?

    What account needs to be present in the SQL Server and what roles and access does it need?

    Thanks. 



    Paul


    • Edited by Paul Byrum Friday, July 19, 2013 5:45 PM Added info
    Friday, July 19, 2013 5:19 PM
  • Domain administrator credentials should be enough. Nothing on the SQL side.

    There is a known issue that might cause this:
    "On a computer that is running Windows Server 2003 with Service Pack (SP2), Windows Vista, Windows Server 2008, Windows 7 or Windows Server 2008 R2, a Svchost.exe process that hosts the remote procedure call (RPC) service crashes."

    Have a look and see if this will help:

    http://support.microsoft.com/default.aspx?scid=kb;en-US;2401588

    Monday, July 22, 2013 4:11 PM
  • 189 servers have been discovered and inventoried, and only 11 of them with SQL installed.
    Thursday, July 25, 2013 1:50 AM
  • Do these missing servers show up in the "Inventory Report" file as Failed? If they do ...

    You might not have the proper credentials or WMI call is failing.

    To check for this:

    "The first thing I would check is to make sure you actually have WMI access.   An easy way to do that is to use the WBEMTEST tool that's available in every version of windows since Windows XP.

    The instructions here:[ http://technet.microsoft.com/library/ee692770.aspx ]; will help you do a simple test to see if you can connect and retrieve data.  Use the same credentials that you used for MAPS, and run WBEMTEST from the same machine you ran MAPS from.  Make sure that you actually run a WMI query to complete the test, merely connecting doesn't prove that the credentials you use have access to execute queries."
    Thursday, July 25, 2013 2:57 PM
  • Thank you all for the wonderful help.

    I am getting more SQL Server instances, but I am not getting SQL Server instance information on 2 servers that are Windows Server 2012.  

    I have adding the domain account that I am using with MAP to WMI Control on the Windows Server 2012, but the server does not show on on the reports.

    Do you have any suggestions for MAP getting access to SQL Server 2008 R2 SP 2 on a Windows 2012 Server?

    Thanks


    Paul


    • Edited by Paul Byrum Friday, July 26, 2013 11:51 PM Added more information
    Friday, July 26, 2013 11:49 PM
  • From looking at the inventory reports, I don't see the server names listed at all. What inventory report are you referring to?

    I looked at the WBEMTEST, but not really sure how to do the test with the application. Can you  provide a few more steps to get me in the right direction?

    Thanks.


    Paul

    Wednesday, July 31, 2013 10:53 PM
  • WBEMTEST "how to" video:
    http://www.youtube.com/watch?v=Xtxo8re2_9w

    If you can't see the 5 servers in the Inventory Report (Environment->Inventory Results(double click) -> Generate Inventory Results Report) at all it might be that these servers are not on your AD and maybe you should run an IP scan for those 5 in particular and see if that will give you better results (default does an AD based scan).

    In any case if the WBEMTEST fails you will need to troubleshoot more and you will receive an error from the test that should point you in the right direction.
    Thursday, August 01, 2013 1:08 PM
  • Thanks for the video. That was very helpful.<o:p></o:p>

    With WBEMTEST, I was able to connect and run tests to the servers that I don't see on the SQL Server Details reports. So, I am not sure what is going on. When I looked at other reports from MAP, like the Windows Server 2012 Assessment, the missing servers on the SQL Server reports show up.  So, MAP does find them, but it says in the WMI Status column as "Failed - Access Denied", but the tests for these servers from WBEMTEST are good and show the good information.<o:p></o:p>

    I have tried IP addresses and the FQDN, but that does not work, So, I don’t know what is going on.<o:p></o:p>



    Paul

    Friday, August 02, 2013 10:39 PM
  • Hello,

    I generated a Windows Server 2012 Assessment report and the two Windows Server 2012 servers do show up in this report.  The WMI status says SUCCESS. So, can you tell my why the MS Assessment Planning Tool does not pick up the SQL Servers on these 2 machines when I run the SQL Server Detailed report?

    Can you offer any suggestions?  I really appreciate your help.

    Thanks,


    Paul

    Tuesday, August 13, 2013 8:09 PM
  • I found the problem.  SQL Server was not configure to be accessed remotely.

    By following these steps below, MAP Tool was about to get information from SQL Server. 

    • Open SQL Server Configuration Manager
    • Select SQL Server Network Configuration
    • Chose your instance of a SQL Server
    • Make sure that TCP/IP protocol is enabled
    • Right click TCP/IP protocol
    • Select properties
    • Click IP addresses tab
    • Scroll down to IP4. The IP address of the server should be here. Set active to yes and enabled to yes. Set TCP port to 1433 (don't know if this is necessary. Can some expert comment)
    • Scroll down to IPAll. Set TCP port to 1433
    • Make an inbound firewall rule for port 1433
    • open sql server management studio, right click server instance, properties->connections-> allow remote connections. Security-> SQL Server and Windows Authentication mode
    • restart sql server service
    • restart sql server browser


    Paul

    • Marked as answer by Paul Byrum Wednesday, August 14, 2013 11:01 PM
    Wednesday, August 14, 2013 11:01 PM