7 Inventorying your SQL estate

 

In the previous chapter, you learned how to find all of the SQL Server instances on your network. Now it’s time to gather essential information about each of those servers and create an inventory of it.

Creating inventories lets you provide access to reports for members of the organization without having to grant access to your SQL instances. Keeping your inventory up to date will help to speed up the planning of migrations and upgrades in particular, because knowing what features are in use can keep upgrades properly planned and on track.

DBAs are often expected to just know the configuration of every host, instance, and database in their estate. If you have only a handful of instances, this may be possible, but for hundreds or thousands, it is unlikely.

In this chapter, we will show you how to use dbatools to build an inventory of the things we’re often expected to know, such as the following:

  • Feature usage
  • Build information
  • Host (server) information
  • Databases
  • Jobs
  • Application logins
  • Disk space trends
  • Installation date
  • Port configuration
  • Edition
  • Last backup date
  • Last database integrity check
  • Suspect pages
  • Instance configuration (is xp_cmdshell enabled?)
  • Centralized error messages

Armed with this information, you will be able to answer ad hoc questions about a host, an instance, or a database. Combine this knowledge with the skills you learned in chapters 5 and 6, and you will be able to document your entire estate in a SQL database.

7.1 SQL features

7.2 Build