Auditing with Microsoft Assessment and Planning (MAP) Toolkit 5.0 – Part 3
Moving the MAP database
Suppose after all you have done so far with MAP, management is now happy and has granted your wish to put the system in a dedicated monitoring server. It is your little baby now – all prerequisites have been installed on the new machine and you are about to install MAP there. But you also want to use the existing database. After all, that database holds all the audits you have done so far. You don’t want to run all the computer discoveries, inventorying and performance monitoring you did over the last few weeks or months.
For migrating the database, the suggested way is:
- Backup the MAP databases in the source computer
- Install MAP in the new machine
- Copy the backup files to the new machine and restore the databases on the new instance of SQL 2008 R2 Express Edition
- When running MAP, point it to the restored MAP database.
Although you can always backup the databases manually from SQL Server Management Studio (SSMS), a command file called BackupAllDB.cmd which ships with the product can automate the process. The command file is located under the HelpSupport Files folder of the MAP program files directory:
You will need to run the command file as an Administrator of the local machine. When you double click on it, a command window will open and prompt you to hit any key. After you press any key, the backup progress will be shown (see below):
Once the process has completed, you will notice that a new folder called DBBACKUP has been created under the HelpSupport Files directory. This folder contains the backup files of all the databases in the MAPS instance, as shown below.
To restore the MAP databases, you will need to create a folder called DBUPGRADEBACKUP under the Support Files directory of MAP in the new server and copy the backup files into that directory. After that, you will need to run the RestoreCustomerDatabase.cmd command file in the new machine with two parameters: the backup file name and the database name. In our example here, the command will be something like the following:
C:…HelpSupport Files> RestoreCustomerDatabase.cmd DBUPGRADEBACKUPMAPS.bak MAPS
The first parameter (DBUPGRADEBACKUPMAPS.bak) is the relative name and path of the backup file and the second parameters (MAPS) is the actual name of the MAP database. The following figure shows the command line option:
Now it may seem to be a bit of a hassle to go through when you have to restore only one database. It is true you can do the restoring from SQL Server Management Studio by hooking up to the MAP instance. However, this approach is also about automation and freeing you up from manual interventions.
Once you have restored the database, you can point MAP to it and from there everything should be okay.
There is another thing you should keep in mind. If you have decided to host your MAP database on a SQL Server 2008 R2 instance (instead of the Express edition), that instance needs to be named as MAPS. Also, when you install the instance, the collation setting you have to choose is SQL_Latin1_General_CP1_CI_AS. In other words, it has to be “Dictionary order, case insensitive, for use with 1253 character set”.
The best way to uninstall MAP is not from the Add Remove Programs applet in the Control Panel but from the MAP installation program itself. When you run the setup again, MAP gives you the option of removing the application, its databases and associated data files as well as the SQL Server 2008 R2 Express Edition.
For SQL Server, another free tool from Microsoft used to gather and present almost the same types of information that MAP gives you now: the SQL Server Health and History (SQLH2) tool. However, it was meant for SQL Server only and required a fair bit of tweaking. It also required Reporting Services to be available for its reports.
The Assessment and Planning toolkit is a lot more than that. Microsoft has tried to include a lot of features in this free tool and it is worth having a look at. Not only it covers SQL, it can be used for Windows, Exchange or System Centre auditing and consolidation as well. This can sometimes streamline and automate a lot of processes DBAs and system administrators would otherwise perform manually. Making business cases to management also becomes lot easier thanks to the easy-to-understand reports and template based proposal documents.