Archive for September, 2013

The Amazing Microsoft Assessment and Planning Toolkit (MAP)

If you are elected as president of a newly discovered country, what would be one of the most critical first things you should do to be successfully as a leader? Let’s put aside the formidable goals of declaring yourself as king or queen or absolving the term limits for a second. Most likely, the most practical first things to do is to establish a team of national geographic experts and task the team to create you a map of the country so that you will know your country’s geographical areas and utilize the resources efficiently. In a similar scenario, if you have been chosen as the guardian of the company’s data – the SQL Server Database Administrator (DBA), the very first thing you should be is to find out the SQL Server instances, their functionalities, and specifications such as version, edition, service pack, clustering, collations, databases, data files, log files, and etc… Wouldn’t it be nice if there’s a free discovery tool that have us SQL Server DBAs in mind and when executed will provide all of these useful information in professionally prepared graphs , charts, and also enables us the function to export the data to Excel and Word documents. There is and it is the Microsoft Assessment and Planning Tookit (MAP)! While it is not new, it is definitely underutilized and deserves to have more exposures in the SQL Server community.
You can read more about it here and then download it. It is a 72.3MB file and it requires .NET Framework 4.2+ to run. That’s all the pre-requisites. While you can use it as a discovery tool for other Microsoft server products such Sharepoint, Exchange, Lync, Windows Server, and etc…, our interest in this blog is to use it as a SQL Server instances discovery tool.

The following are the steps on how I had used it on my testing lab (my laptop):
1. Download and install MAP.
2. In the “All Programs” menu, open “Microsoft Assessment and Planning Toolkit” and it will prompt you to create a new inventory database. Here, I will call it MAP and then click the OK button.
Inventory database

3. On the “Overview” section, click on the link “Perform an inventory”

Perform an inventory

4. On the “Inventory and Assessment Wizard” and under the “Inventory Scenarios” section, select the checkbox for “SQL Server with Database Details” and the click the Next button.

Inventory Scenarios - SQL Server with Database Details

5. On the “Discovery Methods” section, select the methods you wish to use to discover the SQL Server in your network. Note: Usually, you will select the top two options of “Use Active Directory Domain Services (AD DS)” and “Use Windows networking protocols” when you are running MAP in your company’s network. In this testing scenario, I will select “Manually enter computer names and credentials” as I am not running my laptop inside a network. Click the Next button once you made your selections.

Discovery Methods

6. On the “All Computers Credentials” page, click the “Create…” button to supply the account that will be used to connect to the machines you will be specifying and the click the “Save” button and then click the “Next” button to continue.

Supply the account to connect ot the machines

7. On the “All Computers Credentials” page, click the “Create…” button to supply the account that will be used to connect to the machines you will be specifying and the click the “Save” button and then click the “Next” button to continue.

Credentails Order

8. On the “Enter Computers Manually” page, click on the “Create…” button and then click on the “Next” button to key in the computer names, click on the “Add” button to add the computers, select the “Use All Computers credential list” option to use the credential you had submitted in the last step, and then click on the “Save” button to continue. Click the “Next” button on the subsequent page.

Enter Computers Manually

Key in the computer name and specify the credential

9. Review the “Summary” page and the click the “Finish” button when done.

Summary Page

10. The “Data Collection” page will be shown and the collection process will be in progress.

Data Collection

11. After the “Assessment” is “Completed”, click on the “Close” button.

Assessment Completed

12. On the left hand side of the menus, click on the “Database” link and then click on the “SQL Server Products” section to view the details of the “SQL Servers”:

SQL Server Products

13. In this case, on my laptop, I have 2 SQL Server instances. One is running SQL Server 2012 and the other is running on SQL Server 2008 R2. There’s a pie chart showing the distributions of versions:

SQL Server Discovery

14. Click on the “Generate Detailed SQL Server Reports” link to export the details to Excel and Word documents.

Generate Detailed SQL Server Reports

15. The detailed reports will then get exported to the MAP folder in the “My Documents” folder:

The Reports are saved in the MAP folder

16. Review the reports as you wish.