Capturing SQL Server Inventory with PowerShell

Introduction

Maintaining an accurate and comprehensive inventory of your SQL Server environment is crucial for effective database management, ensuring security, and meeting compliance requirements. With PowerShell, database administrators can automate the collection of detailed information about SQL Server instances and databases, streamlining the inventory process. This blog post introduces a PowerShell script designed to efficiently gather SQL Server inventory data, separating instance-level information from database-specific details into two distinct reports. We’ll also delve into a detailed explanation of the script to enhance understanding and provide insights into its structure and PowerShell techniques employed.

PowerShell Script for SQL Server Inventory

The following PowerShell script collects information about SQL Server instances and databases, organizing the data into two separate outputs: one for static, instance-level details and another for dynamic, database-specific details.

The Script

# Load the SqlServer module
Import-Module -Name SqlServer

# Define the SQL Server instances to inventory
$servers = @('Instance1', 'Instance2')

# Initialize arrays for collecting inventory data
$instanceData = @()
$databaseData = @()

foreach ($server in $servers) {
    # Collect and store instance-level information
    $instanceQuery = "SELECT SERVERPROPERTY('ProductVersion') AS Version, SERVERPROPERTY('Edition') AS Edition;"
    $instanceInfo = Invoke-Sqlcmd -ServerInstance $server -Query $instanceQuery
    $loginsQuery = "SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE type IN ('S', 'U', 'G') AND is_disabled = 0;"
    $logins = Invoke-Sqlcmd -ServerInstance $server -Query $loginsQuery
    $jobsQuery = "USE msdb; SELECT name, enabled FROM sysjobs;"
    $jobs = Invoke-Sqlcmd -ServerInstance $server -Query $jobsQuery
    
    $instanceData += [PSCustomObject]@{
        SQLInstance = $server
        Version = $instanceInfo.Version
        Edition = $instanceInfo.Edition
        Logins = ($logins.name -join ', ')
        Jobs = ($jobs.name -join ', ')
    }

    # Collect and store database-specific information
    $databasesQuery = "SELECT name, database_id, (SELECT SUM(size) * 8 / 1024 FROM sys.master_files WHERE database_id = db.database_id AND type_desc = 'ROWS') AS SizeMB FROM sys.databases db;"
    $databases = Invoke-Sqlcmd -ServerInstance $server -Query $databasesQuery
    
    foreach ($db in $databases) {
        $databaseData += [PSCustomObject]@{
            SQLInstance = $server
            DatabaseName = $db.name
            DatabaseId = $db.database_id
            SizeMB = $db.SizeMB
        }
    }
}

# Export instance and database data to CSV files
$instanceData | Export-Csv -Path 'C:\temp\SQLServerInstanceInventory.csv' -NoTypeInformation
$databaseData | Export-Csv -Path 'C:\temp\SQLServerDatabaseInventory.csv' -NoTypeInformation

Write-Output "SQL Server inventory has been saved to C:\temp\SQLServerInstanceInventory.csv and C:\temp\SQLServerDatabaseInventory.csv"

This script efficiently separates the collection and reporting of SQL Server instance details from database-specific information, facilitating targeted analysis and management tasks.

Detailed Script Explanation

Let’s break down the script into its core components to provide a deeper understanding of its functionality and the PowerShell techniques it utilizes.

Importing the Required Module

Import-Module -Name SqlServer

The script starts by importing the SqlServer module, which is crucial for executing SQL Server-specific cmdlets. This step ensures that all the necessary commands for SQL Server management are readily available in the PowerShell session, highlighting the importance of leveraging specialized modules to extend PowerShell’s capabilities for specific management tasks.

Defining SQL Server Instances

$servers = @('Instance1', 'Instance2')

An array $servers is defined to list the SQL Server instances targeted for inventory. This approach demonstrates how arrays can manage multiple items, allowing the script to perform operations on each instance efficiently. Arrays are a fundamental concept in PowerShell, enabling batch processing and iterative operations across a collection of objects.

Initializing Arrays for Data Collection

$instanceData = @()
$databaseData = @()

Two arrays are initialized to hold the collected data, separating instance-level information from database-specific details. This organization strategy uses arrays to dynamically collect and structure data during script execution, showcasing the flexibility of PowerShell in data aggregation and manipulation.

Looping Through Each Server Instance

foreach ($server in $servers) {
    ...
}

A foreach loop iterates over each server in the $servers array, applying the enclosed commands to each instance. This illustrates the use of loops in PowerShell to automate repetitive tasks, allowing for scalable operations across multiple SQL Server instances with minimal code duplication.

Collecting and Storing Data

# Instance-level information collection
$instanceQuery = ...
$instanceInfo = Invoke-Sqlcmd -ServerInstance $server -Query $instanceQuery

# Database-specific information collection
$databasesQuery = ...
$databases = Invoke-Sqlcmd -ServerInstance $server -Query $databasesQuery

Data collection is performed using Invoke-Sqlcmd to execute T-SQL queries against the SQL Server instances. This cmdlet bridges scripting with database management, enabling direct interactions with SQL Server from within PowerShell. It exemplifies the power of PowerShell in executing database operations and retrieving information for analysis and reporting.

Creating Custom Objects for Data Aggregation

$instanceData += [PSCustomObject]@{
    SQLInstance = $server
    ...
}

Custom objects are created to aggregate the collected data, demonstrating PowerShell’s ability to structure data in customizable formats. This method is particularly useful for organizing and representing data logically, catering to specific reporting and analysis needs.

Exporting Data to CSV

$instanceData | Export-Csv -Path 'C:\temp\SQLServerInstanceInventory.csv' -NoTypeInformation

The final step exports the aggregated data to CSV files, utilizing the Export-Csv cmdlet. This process illustrates PowerShell’s capability to not only collect and process data but also to export it in a format that is widely used and easily accessible for further analysis or reporting.

Conclusion

This PowerShell script streamlines the process of collecting SQL Server inventory data by efficiently separating instance-level details from database-specific information. By breaking down the script, we provide insights into the PowerShell techniques used, enhancing understanding and demonstrating the script’s value as both a practical tool for SQL Server management and a learning resource for PowerShell scripting.