chapter seven

7 Inventorying your SQL estate

 

In the last chapter, you learned methods of identifying the SQL Server Instances on your network. Once you know where they are, you will want to create an inventory. This is an essential DBA task that is useful for a variety of scenarios:

  • Audits
  • Baselines
  • Capacity Planning
  • Change Management
  • Migrations & Upgrades
  • Reporting
  • Troubleshooting

Creating inventories lets you to 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 & upgrades in particular, as 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. With a handful of instances this may be possible but for hundreds or thousands it is unlikely.

We are often asked to know things like:

  • Feature usage
  • Build info
  • Host (server) info
  • Databases
  • Jobs
  • Application logins
  • Disk space trends
  • Instance configuration (is xp_cmdshell enabled?)
  • Port Configuration
  • Edition
  • Installation Date
  • Last Check Database Integrity Check
  • DBCC / Suspect Pages
  • Last Backup Date
  • Centralized Error Messages

7.1  SQL Features

7.2  Build

7.3  Host info

7.4  Databases

7.4.1  Filtering the databases that are returned from Get-DbaDatabase

7.4.2  Filtering the databases that are returned from Get-DbaDatabase by last backup time

7.5  Putting it all together into a database

7.6  Hands-on Lab