Problem
In a previous tip called SQL Server DBA On-Boarding Checklist, we determined how to access different tools to efficiently onboard new database administrators (DBA). One of the tools listed was the Microsoft Assessment and Planning (MAP) Toolkit. I’ve read that the tool is primarily used for desktop, server, and cloud migrations. How can I use it to take inventory of SQL Server instances that I will be managing?
Solution
While the Microsoft Assessment and Planning (MAP) Toolkit is designed for desktop, server, and cloud migrations, it is also a very powerful tool to take inventory of SQL Server instances and databases across the network. And because of the way it captures information, you can use the generated reports to work with systems administrators on delegating responsibilities and creating a task action plan as a DBA.
Installing the MAP Toolkit
You can download the MAP Toolkit from the Microsoft Download Center. The download consists of four (4) files – the installation file, sample reports, documentation, and a readme file. You can choose to install it on a Windows client workstation or a server operating system. The machine should be able to access the network you want to conduct an inventory and assessment on. In this example, the MAP Toolkit will be installed on a Windows client workstation that is joined to an Active Directory domain. Be sure you have administrative privileges on the workstation to install the MAP Toolkit.
The installation process is pretty straightforward. Simply run the MAPSetup.exe file to start the installation process.
In the License Agreement dialog box, select the I accept the terms in the License Agreement check box and click Next.
Accept the default installation folder and click Next.
In the Begin Installation dialog box, click Next.
Click Finish once the installation process completes.
Configuring the MAP Toolkit
Launch the tool to start configuring it for collecting information about your SQL Server instances. The installation process will include a SQL Server Express LocalDB to store the data collected during inventory. The database files will be in the C:\Users\<User>\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MAPToolkit directory.
In the Name field under the section Create or select a database, provide the name of the database to store inventory data. Click OK.
To start collecting information about SQL Server instances, click the Database section on the left side. Note: You will see other options like Cloud, Desktop, Server, etc. Keep in mind that the MAP Toolkit is designed for desktop, server, and cloud migrations. And that’s what it is mainly used for.
Clicking the Database section will show you different scenarios. Note that you can also do an inventory of Oracle databases. Click Collect inventory data under the SQL Server Discovery scenario to inventory SQL Server instances and databases.
This will open the Inventory and Assessment Wizard. In the Inventory Scenarios dialog box, select the SQL Server and SQL Server with Database Details checkboxes. The SQL Server inventory scenario will capture information about the SQL Server instance and the operating system it is running on. The SQL Server with Database Details scenario will capture information about the individual databases running on those instances.
Click Next.
In the Discovery Methods dialog box, choose how you want to discover the servers. Active Directory Domain Services (ADDS) will be used in this example. The assumption here is that all your SQL Server machines are joined to an Active Directory domain. This is helpful, especially when running database servers in different network subnets. You no longer have to worry about remembering IP addresses or IP address ranges should the servers be segregated across different network subnets. Check with your systems administrators on the most efficient way of collecting information about SQL Server instances across the network.
Click Next.
In the Active Directory Credentials dialog box, provide the name of the Active Directory domain, the domain account you want to use to query Active Directory, and its corresponding password. Note: The domain account does not need to be a domain administrator. An ordinary domain user account is good enough, even your own.
NOTE: This dialog box and the succeeding dialog boxes will differ depending on your choice in the Discovery Methods dialog box.
Click Next.
Accept the defaults in the Active Directory Options dialog box and click Next. This will allow the MAP Toolkit to query the entire Active Directory domain. Consult your Active Directory domain administrators if you have custom groups of servers, such as Development, Production, DR, etc. There may be some security policies that could prevent the tool from querying those groups.
In the All Computers Credentials dialog box, click Create. This will open up the Account Entry dialog box.
Use this to provide the credentials to connect to the Windows Server and the SQL Server instance. Suppose you have a segregation of duties where the sysadmins are responsible for the operating system and the DBAs for the SQL Server instances. In that case, you will need to provide the domain account with local administrative privileges on the servers. Providing multiple credentials gives the MAP Toolkit the flexibility to try all the credentials in case one fails. For example, connecting to a SQL Server instance configured in Mixed mode authentication. If the provided domain account does not have permissions to log in to SQL Server, a SQL Server login will be used to continue the data collection if you’ve provided one.
Click Save to save the credential and Close to close the Account Entry dialog box.
Click Next.
In the Credentials Order dialog box, change the priority order of how you want the tool to use the credentials. In this example, the domain account will be used first when connecting to a SQL Server instance. If that fails, the sa login will be used. Note that I’m only using the sa login for demonstration purposes. A security best practice is to create a SQL Server login that has sysadmin privileges and use that account to manage the SQL Server.
Click Next.
Review all the configuration settings in the Summary dialog box and click Finish. This will automatically run the data collection process and do the inventory.
The amount of time it takes to do the inventory will depend on the size of your network, the number of servers, the number of SQL Server instances, and the number of databases on those instances.
Reviewing the Generated Reports
After the data collection process completes, the SQL Server Discovery scenario will now reflect that.
You can click on the SQL Server Discovery section to display the different reports – SQL Server versions, components, and licensed editions. And while the graphs may appeal to your manager, you need the Excel spreadsheets containing the inventory details. Under the Options section, click the Generate SQL Server Assessment Report link. If you want details of the SQL Server databases, click the Generate SQL Server Database Details Report link.
You will find the Excel spreadsheets under the C:\Users\<user>\Documents\MAP\<database> directory. Be sure to save the spreadsheets in your document repository. You will be using them to create your inventory database of all SQL Server instances that you will be responsible for. The filenames of the Excel spreadsheet will include the date and time when you ran the tool.
Open the SQL Server Assessment spreadsheet to see the details of the SQL Server instances discovered in the network. The Summary worksheet displays the SQL Server components found.
The DatabaseInstances worksheet displays details of all the SQL Server database engine instances.
What Can You Do With This Information?
The information can be overwhelming if you don’t know what to do with it. Below is a list of tasks that you can perform based on the information provided in the DatabaseInstances worksheet:
- Create an inventory database for auditing purposes. You can use the inventory database to create a checklist of SQL Server instances that you need to check daily, weekly, or monthly. You can add extra details like what apps are using the databases, environment type (development, staging, production, DR), DBA responsible (if you have a DBA team), RPO/RTO/SLA, etc.
- Prepare for SQL Server upgrade and/or patch installation. If you find a version no longer supported (like SQL Server 2000), you can plan for an upgrade or migration. If you see a supported version that isn’t updated, you can plan for the installation of the latest service pack and/or cumulative update.
- Prepare for Windows Server upgrade and/or patch installation. Also included in the report is the version of the Windows Server operating system that the SQL Server instance is running on top of. If you found a version that is no longer supported (like Windows Server 2008 R2), you can work with your Windows sysadmins to plan an upgrade or migration. Same thing if you see a supported version that isn’t updated.
- Audit SQL Server licenses for compliance. Included in the report is the number of CPU cores of the Windows Server machine running the SQL Server instance and the SQL Server edition. Check with the person responsible for keeping track of licenses to see if every SQL Server instance is covered based on the number of CPU cores and the SQL Server edition. You may need to buy extra licenses or start planning for consolidation.
- Plan for scale-up. Scaling up is the process of adding compute or storage resources. Also included in the report is the amount of system memory and disk space allocated on the server. You can plan to add more memory or disk space based on the workload and storage requirements.
The SQL Server Database Details spreadsheet contains more details about the SQL Server instances and the databases.
Below is a list of tasks that you can perform based on the information provided in this spreadsheet:
- Investigate reported failures. There may be cases where data collection was done on a server, but the inventory failed. The Overview worksheet has a column named SQL Server Database Engine Instances with Inventory Failures. Use this information to investigate if there were SQL Server instances that have not been reported in the inventory but are available on the network. If you are responsible for the SQL Server instance, you need to know and find out why the inventory failed.
- Review best practices configuration. The DBInstanceProperties and DatabaseSummary worksheets contain configuration values of the SQL Server instances and databases, respectively. Check the current values to see if they meet Microsoft’s and/or your internal best practices.
Summary
Use the MAP Toolkit to easily perform an inventory of your SQL Server instances and databases across your network. You can use the Excel spreadsheets as the basis of your action plan as a DBA. Once you have an initial list of all the SQL Server instances, you can start creating a centralized inventory database that you can refer to when doing your regular maintenance tasks and planning for modernization.
Next Steps
- Review the previous tips on SQL Server DBA tools for inventory purposes
- Download the Microsoft Assessment and Planning Toolkit
