Some hints and tips for the MAPS tool

A few weeks ago I had a small SQL consolidation project. The target was to consolidate five stand-alone SQL Servers (SQL 2000 and 2005) and one SQL 2005 cluster. When consolidating, first of all you must know about the workload on those servers. But how to find out the workload? Spending hours and days in front of the servers, looking at them? Perfmon Counters?

No, there is a tool from Microsoft, the Microsoft Assessment and Planning Toolkit (MAPS). I agreed with the customer that we will use it. When using it I found out some specials which I will talk about here.

1. Installation

Installation is easy, download the tool from the web, run setup, and….. no, not done. First of all, the tools needs a SQL Server instance to store the data. And, the special thing is, the SQL Instance cannot be anywhere, it must be on the computer where you install the tool. During setup it gives you the chance to download SQL 2008 Express, which is really good for the job here, but you can also pre-install your own SQL Instance using Express, Standard, Enterprise or Developer Edition. But there is one thing you must be aware of: the installation must be a named instance and the name must be MAPS. Really true.

2. Installation 2nd

When installing there is the prerequisite that Office 2007 SP2 or Office 2010 must be on the computer. The MAPS tool can automatically create nice decision maker ready Excel sheets with results, but just for collecting data there is really no need to have Office on the computer. It would be more useful to not have this need, just install and collect to SQL. Then backup the SQL database, copy to your laptop, restore there rand make the rest of the work there, including creating those nice Excel sheets. This is the way I did it; I took the results with me and did the documentation in my home office. But, nevertheless, the installation needs it, so prepare your customer to have the installation media ready.

3. Collection of data

The collection runs automatically. First of all you must configure a database on the MAPS, instance, the tool asks you when starting if you want to create a new one, or use an existing one. You should create a new one when you want to do a new collection, choose an existing one when you want to use already collected data.

On a new collection: first of all you must run the ‘Inventory and assessment wizard’. This one will collect basic data about the servers, OS version, SQL version, services running etc. Which server to collect can be specified in many ways, by a text file in which the servers are just entered, each one manually in the tool etc. I will not explain every option here, use the tool and discover yourself. You can enter one user account for collecting the data, of enter a number of user accounts and the tool will try all of them one after another on each of the configured servers until it finds one which has access. This is useful if the servers are in different domains; this was the case at my customer.

After the successful run of the ‘Inventory and assessment wizard’ you can collect the performance data. Make sure that you capture all times, busy times, month endings (creation of bills etc.). In my case we let the tool collect the data for 6 weeks! 30 minutes is the minimum time, by the way, otherwise the tool is doing nothing :-)

4. Collection, be careful!

At my customer the collection failed. But why? OK, it took me some time, but then I found out that the WMI service was disabled at the servers, and the tool does WMI calls. So enable and start the service, and it works…. But then it stopped…  ok, a lot of customers have group policies which disable the WMI service because of security. So, my hint: Clarify with the customer before you start, because I have the experience at my customers that it takes some days to change the group policies (and in addition, there will be some discussions with the AD staff for what you need WMI :-)).

5. Collection, be very careful!!!

The program must be opened while collecting; it is not a windows service! So, you must lock the console and wait. In my case at the customer a different person started the collection and locked the console, so the person I worked with at the day we wanted to stop the collection just used its administrative account to log out the account (which is a hard program stop from process perspective).

OK, we did this, logged on to the computer, started the MAPS program, when asked we took the existing database we collected the data into, went to the section ‘Server consolidation’, and then to the option ‘Performance metrics results’…. And the MAPS tool told us ‘sorry, I do not have any collected performance data’. WHAT?

OK, there is the option of just opening the database in Management Studio, there are tables called dbo.Performance_disk, dbo.Performance_memory etc…. and all of them are full of data! But why has the tool the opinion there is no data?

There is a table called dbo.Performance_statistics… and this is empty. I checked on my database on my laptop with a successful collection, and there is a row in the database for each server I have collected. OK, so what happened? Easy…. The MAPS tool collects for days and weeks and months, and write the data to the SQL database, but… this table is written when the collection is ended, but we…. Stopped the program by a hard logoff of the user… and then it does not write the table, and… the tool says ‘sorry’ :-(.

Solution? Easy… we just started a new collection, the tool ask if we want to overwrite the data (NO, NO, NO!!!), or if we want to keep the data (YES YES YES!!! :-)). We collect…… 31 minutes (as you remember mentioned before, 30 minutes is the minimum), after that we close the collection in the tool, and we are done. The table is filled now, and the MAPS program shows the results. YES! :-)

6. One last hint

The collected data is a nice playground for your documentation. The documentation written by the MAPS tool is ok, but only gives number of collections of data, minimum value, maximum value, average…. But perhaps you want to know how often the processor was above 90 % during the collection time for a special server, or stuff like that. All of that you find easily in the table in the database. T-SQL is your friend, the tables are named dbo.Performance_ and the rest tells you whats inside, disk, memory, processor……. :-)

That’s it for today. By the way, this info here is for MAPS 5.0. There is new version out, 5.5, but this was in beta when I did my job and not public available. So I decided to use the officially released 5.0 version. Are my hints here still valid for 5.5… no idea. Find out yourself :-)