social share alt icon
Thought Leadership
March 20, 2017
Collect Inventory for SQL Server using Microsoft Assessment and Planning (MAP) Tool
Vijayakumar K02

Microsoft Assessment and Planning Toolkit provision the collection of inventory for database migration and layout an environment for Database migration, DB development testing.

Reference to Microsoft Books Online:

The Microsoft Assessment and Planning (MAP) Toolkit is an inventory, assessment, and reporting tool that helps to assess a given current IT infrastructure and determine the right required Microsoft technology Software for IT requirement. This toolkit uses Windows Management Instrumentation (WMI), SMS Provider,  Active Directory Domain Services (AD DS), and different technologies to gather data in a given environment and catalogs computer hardware, software, and operating systems in any scale IT environments without the installation of any agent software on the destination servers.

To make MAP Toolkit to connect successfully and catalog servers in a given scope, machines have to be configured through WMI with firewall authorization if any, to enable remote access through WMI. In addition to permitting WMI, accounts should have administrative privileges to access desktops and servers destined.

Download Install Bits & Deploy MAP tool

Microsoft Assessment and Planning Toolkit can be downloaded from the below link

http://www.microsoft.com/en-us/download/details.aspx?&id=7826

During MAP tool installation, SQL Server Express LocalDB gets installed by default to host the inventory database.

MAP tool is 9.6 is the latest version available

MAP 9.6 is engineered to catalog, assess and report the SQL Server 2016 instances and components in the SQL Server assessment.

1.After launching MAP toolkit, it configures inventory database, a repository to store the inventory data , statistics during execution of MAP tool.

2. Invoke the MAP tool and in the Create or Select a Database to Use dialog box, click Create an inventory database and type Learner as a new database name and click OK.  User gets a message, Learner database is created successfully in few minutes.

3. Launch Command Prompt as Run as Administrator, to enable the remote administration. Type the below command and press enter. A message OK in response should be the result.

netsh advfirewall set currentprofile settings remotemanagement enable

4. This below is the home screen for MAP tool which allows the different options to collect the inventory data for Server, Desktop, Cloud, user tracking, etc.

MAP toolkit uses WMI, SQL Server Commands, Oracle client, VMware web services, Active Directory Domain Services, PowerShell and SSH with remote shell commands which will collect the inventory data and statistics for the destined network machines. MAP tool is not required to exist on the destined machine for inventory collection.

5. On the left side pane, click on Database option

6. When clicked on Collect Inventory Data link, it launches an Inventory and Assessment Wizard window.

The window gives an option to choose the scenarios on the required choice to collect the details with various scenarios available:

·         Windows-based computers

·         Linux-based computers

·         VMware computers

·         SQL Server

·         SQL Server with database details

·         Windows Azure Platform Migration

·         MySQL, Oracle, and Sybase

·         Active Devices and users

·         Forefront Endpoint Protection Server

·         Lync Server

·         Exchange Server

7. Checkbox SQL Server and SQL Server with Database Details is chosen in Inventory Scenarios Screen.

8. Once Next is clicked.

Discovery Methods wizard appears for discovering computers. The below are available discovery methods listed for selection of method

·         Active Directory Domain Services

·         Windows networking protocols

·         System Center Configuration Manager

·         Scan an IP address range

·         Manually enter computer names and credentials

·         Import computer names from a file

9. Choose Manually enter computer names and credentials.

Click Next.

10. Click Create in All Computer Credentials page.

11. In the Account Entry dialog, give the username of the local user.

12. Authenticate with the password of the local user in both the Password and Confirm Password fields and click Save

13. In the All Computer Credentials Page, click.

14. Click Next in the Credentials Order.

15. Click Next in Enter Computers Manually screen.

16. In Specify computers and credentials page enter any or destined computer name and Click Add

Multiple computer names can be added here as per the requirement.

17. Click Save

18. Click Next button to move Summary section19.  In Summary section, the list of the selected can be seen including any detected errors

20. Click Finish.

21. Inventory and Assessment Window appears on the screen in which progress of Data collection is viewed

After completing all previous steps, it’s now time to generate the reports to get SQL Server DB information?

To generate the report, click SQL Server from Database scenario.

In the displayed option two types of reports can be executed:

1.    Generate SQL Server Assessment Report

2.    Generate SQL Server Database Detail Report

Click on any report and save to local disk.

These two reports shows different type of information which are

1.    SQL Server Assessment Report

This report displays information about Database Instances and Components.

1.    SQL Server Database Detail Report

This report displays an information of all the SQL Server database engine instances

and a number of databases discovered on the network.

Conclusion

The MAP Toolkit is one of the best tool for gathering inventory data, as well as for analyzing upgrade readiness, without the need of any high-cost management tools. Apart from saving tremendous time in the data collection, the added enhancement is, it also provides spreadsheets and documents to ease the use of data more efficiently. I spotlight it as one of the best free products available on the market, and I abide by that assessment. This article covers only a tiny proportion of what the tool can do.  More can be done with other available Scenarios.

References:

https://blogs.technet.microsoft.com/canitpro/2013/10/13/microsoft-assessment-and-planning-toolkit-best-practices

www.kapilsqlgeek.com

Comments
MORE ARTICLES BY THE AUTHOR
RECENT ARTICLES
RELATED ARTICLES