In this blog, I will provide the step-by-step instructions to discover SQL Server across the Enterprise. 

Additional Resources:

• An overview of the MAP Toolkit: http://microsoft.com/map
• The Getting Started Guide: http://download.microsoft.com/download/4/6/F/46F45C42-D679-404E-9812-6053DD59A0D2/Getting_Started_Guide.en.doc
• Training Guide: http://download.microsoft.com/download/4/6/F/46F45C42-D679-404E-9812-6053DD59A0D2/MAP_Training_Kit.zip

Lessons Learned and Best Practices:

• Use domain accounts and passwords with domain administrator rights whenever possible
• Ensure the WMI and Remote registry services are running on all computers
• Start small and expand later. Select the minimum options you need when running it to cut down on failures and high run times. Consider starting by specifying IP address ranges and then start with a single computer and expand once you get the hang of it.

Download the latest version of the MAP Toolkit from here! The latest version is the MAP Toolkit 6.5 that was released in December 2011.

The steps to discover SQL Server across your enterprise…

1. After launching the MAP Toolkit, you are prompted to create a new database. Enter a name for the database and click Ok.

2. On the Data Collection page, click the “Go” button next to the “Perform an Inventory” step to launch the Inventory Wizard. Click Next to continue.

3. On the Inventory Scenarios page of the Wizard, you can choose to just to discover SQL Server of obtain full details of SQL Server. If you just want to discover SQL Server, select the “Windows computers” options. This is the most common scenario. If you want to discover SQL Server and connect to SQL Server to discover details about the databases and server properties, select the “SQL Server” option. By selecting the “SQL Server” option, the MAP Toolkit will discovery details information about the operating system and SQL Server installations including the database details.

4. On the Discovery Scenarios page of the Wizard, select how you want the MAP Toolkit discover SQL Server across your enterprise. Here is an explanation of your choices:

• Use Active Directory Domain Services -- This method allows you to query a domain controller via the Lightweight Directory Access Protocol (LDAP) and select computers in all or specific domains, containers, or OUs. Use this method if all computers and devices are in AD DS.
• Windows networking protocols --  This method uses the WIN32 LAN Manager application programming interfaces to query the Computer Browser service for computers in workgroups and Windows NT 4.0–based domains. If the computers on the network are not joined to an Active Directory domain, use only the Windows networking protocols option to find computers.
• System Center Configuration Manager (SCCM) -- This method enables you to inventory computers managed by System Center Configuration Manager (SCCM). You need to provide credentials to the System Center Configuration Manager server in order to inventory the managed computers. When you select this option, the MAP Toolkit will query SCCM for a list of computers and then MAP will connect to these computers.
• Scan an IP address range -- This method allows you to specify the starting address and ending address of an IP address range. The wizard will then scan all IP addresses in the range and inventory only those computers. Note: This option can perform poorly, if many IP addresses aren’t being used within the range.
• Manually enter computer names and credentials -- Use this method if you want to inventory a small number of specific computers.
• Import computer names from a files -- Using this method, you can create a text file with a list of computer names that will be inventoried.

5. For this blog, I am going to select “Manually enter computer names and credentials” and then select Next.

6. On the All Computers Credentials page, enter the accounts that have administrator rights to connect to the discovered machines. This does not need to a domain account, but needs to be a local administrator. I have entered my domain account that is an administrator on my local machine. Click Next after one or more accounts have been added.

NOTE:

The MAP Toolkit primarily uses Windows Management Instrumentation (WMI) to collect hardware, device, and software information from the remote computers. In order for the MAP Toolkit to successfully connect and inventory computers in your environment, you have to configure your machines to inventory through WMI and also allow your firewall to enable remote access through WMI. The MAP Toolkit also requires remote registry access for certain assessments. In addition to enabling WMI, you need accounts with administrative privileges to access desktops and servers in your environment.

7. On the Credentials Order page, select the order in which want the MAP Toolkit to connect to the machine and SQL Server. Generally just accept the defaults and click Next.

8. On the Enter Computers Manually page, click Create to pull up at dialog to enter one or more computer names.

On the Specify Computers and Credentials dialog, enter the computer names to discover. I have entered my local machine (petersad50) and then selected Use All computers credentials list. Then click Save and then Next on the Enter Computers Manually page.

On the Summary page, review your selections and then click Finish.

After clicking Finish the status is shown.

After the inventory is complete and you dismissed the dialog you are returned back to the MAP Toolkit console. You can then navigate to the SQL Server Discovery tab in the left navigation to see a summary of the SQL Server instances that were found. (Here I am showing the sample database that you can download from here)

Next you can generated more detailed reports by clicking on the Generate report/proposal link. When you click the link, the MAP toolkit will generate the following reports:

• Executive Proposal (Word document) – Identifies computers that have SQL Server or SQL Server components installed. It indicates their ability to migrate to SQL Server 2008 R2. The MAP Toolkit also generates a SQL Server Database Details report, which provides detailed information about various SQL Server instances running in your network. This report also shows which databases are installed on each instance. You can use this information to consolidate SQL Server instances or databases in your environment.
• SQL Server Database Details (Excel Spreadsheet) – Provides a listing of many SQL Server instance and database details that were discovered, such as database size, last time backup, and collation.
• SQL Server Assessment (Excel Spreadsheet) – Provides a quick summary of SQL Server database instances and other SQL Server components such as Reporting Services and Analysis Services