Auditing with Microsoft Assessment and Planning (MAP) Toolkit 5.0 – Part 2
The Database Instances worksheet lists the name of each physical computer or virtual machine and the names of the SQL instances running in each of these systems. It also shows a host of other information including:
- The type of the machine (physical or virtual)<
- Operating system running in the machine and its service pack level.
- The architecture of the OS (32 bit or 64 bit)
- The number of physical and logical processors present and the speed of each CPU
- System memory
- Logical drives defined for each physical / virtual system
- Size of each logical drive and the free space remaining in them
- The version, edition and service pack level for each SQL Server instance running in a machine
- Whether the instance is clustered and if so what is the network name of the cluster
- The language configured for SQL Server
- Status of the SQL Server instance (Running or Stopped) and its start mode
There is another column showing MAP’s recommendation regarding the server.
The Components worksheet shows the same information as the Database Instances, except it lists the SQL Server components (SQL Service, Analysis Service, Integration Service, Reporting Service etc.) installed in each machine.
This second Excel spreadsheet is the Database Discovery report. This has five worksheets. Throughout these five tabs, the database detail report shows a large set of information about every database in each of the instances it inventoried. This information will include:
- The database names in each instance, their sizes, individual data and log file names, their sizes and growth characteristics
- Database compatibility level and other properties like recovery model, collation setting and status (offline, online, read-only etc.)
- The owner of each database and when the database was created
- Date and time the database was last backed up
- Number of tables, views, stored procedures and functions in each database
- Instance level information such as CLR version, license types etc.
- Every configuration parameter value such as max server memory, max degree of parallelism etc.
- User account information for every database including user name, role memberships, associated login name, default schema and default database
The following figure shows the first worksheet of the report:
Apart from the two Excel files, MAP also creates a MS Word file. This is a proposal document that shows the same information as seen in the application: all the high level numbers and pie charts are presented along with recommendations about upgrades that can and should be made. The document can be a good starting point if you are writing a business case for version upgrade.