MAP Toolkit – SQL Server Inventory data for free!

May 28, 2018

The Microsoft Assessment and Planning (MAP) Toolkit is provided by Microsoft as a simple way of identifying what Servers you have on your Network. This allows you to ensure that everything is licensed and paid for. So kind.

MAP Toolkit also helps you to plan migrations onto Virtual Platform, helping you choose additional Microsoft product no doubt.

Actually, this is a really useful tool. Arm it with an account that has wide permissions and set it running. MAP Toolkit will scan every Server on the Network, identifying the Operating System and any SQL Server components that are installed.

The information returned can be really useful to check that your licensing is up to date, to ensure that you’re aware of any “user installs” and to collect details of SQL Server versions and Editions that are in use, which will help with patching.

When running the MAP Toolkit scan, be prepared for complaints. As with all Server scanning software, it’s easy to end up performing your own “Denial of Service” attack on your own Servers.

Data Storage

If you stick to the defaults during installation, MAP Toolkit will use “localdb” to store the results – a cut-down version of SQL Server Express Edition. Unfortunately, this makes it difficult to access the data – you can use the Connection String “(LocalDB)\MSSQLLocalDB” on the Server hosting MAP Toolkit, but this is restrictive and only works on the local Server.

As it’s using SQL Server Express Edition, regular SQL Server Data files are used. The Database files themselves are held in the following location:

C:\Users\[UserNameUsedForInstall]\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MAPToolkit

The SQLLocalDB Utility can be used to Stop the SQLLocalDB Service, and then you can copy the Database files to another Server for further reporting or consolidation.

Better to install SQL Server Express Edition before installing MAP Toolkit, creating an Instance called “MAPS”. If the MAP Toolkit Installation finds this Instance during installation, then it will be used instead of “localdb”.

A Model Database

This is probably the first application that I’ve ever seen that uses the [model] Database correctly. When a new Database is created on a SQL Server, the [Model] Database is copied. If you create a Table in the [model] Database, that Table will appear in each new Database that’s created. This is often used to set up Physical Data Files and Filegroups to be consistent across all Databases on a Server / System.

The [Model] Database on a MAP Toolkit instance contains a series of Schemas, Tables, Views and Stored Procedures.

Cross Domain Data Collection

In an environment that hosts multiple Domains, the easiest way of gathering all of the information is to have a designated Server for MAP Toolkit in each Domain. Run the MAP Toolkit in each Domain, copy the Database files to a single Server and combine the Data. GUIDs are used within the Data making it easier to perform this operation without getting duplicates.

Reporting

The MAP Toolkit has a series of built-in Reports. This will provide information on number of Servers running each type of Windows Server or SQL Server. The Reports can be opened as Excel documents and a drill-down facility is available.

You can also view details of how Data Center Consolidation can help.

Reporting Tables

A few key Tables have been identified within the MAP_Toolkit Databases. These Tables contain lots of useful information, including:

Windows Version
SQL Server Version and Patching Level – this can be used to build a Patching list
Server Specification – Processors / Cores – useful for determining if the correct Licensing is in place
SQL Server Edition – e.g. Developer, Express, Enterprise, Standard Edition
SQL Server Instance Information – how many, what are their names,
Server / Workstation Name – useful for Identifying and SQL Server installations on local PCs

It’s worth noting that MSDN Editions of SQL Server cannot be differentiated from regular paid Editions.

All of the information above is contained in the following Tables. The two Tables are joined using the [DeviceNumber] field.

[Map_Combined].[SqlServer_Inventory].[Inventory]

[Map_Combined].[Core_Inventory].[Devices]

This information can be augmented with other Tables to build a full Configuration Management Database.

Some examples of additional data to be stored:

Purpose of the Server
Support contact details – who to contact in the event of an issue
Dependencies – upstream and downstream dependencies on this Server

Overall, a pretty neat free tool for software inventory collection.

MAP Toolkit can be downloaded from Microsoft using this link:

https://www.microsoft.com/en-gb/download/details.aspx?id=7826