关于SQL Server信息哈希表独立存储及整合到Invoke-SqlCmd脚本的技术问询

Absolutely! Storing your server configuration in a hashtable (or better yet, an array of hashtables for multiple servers) in a separate file is a smart move—it keeps your main script clean, makes updating server details a snap without touching core logic, and keeps all your configs in one organized spot. Let's break this down step by step.

1. Can a hashtable be stored in a separate file? #

Yep, totally possible! PowerShell lets you define variables (including hashtables and arrays of hashtables) in a dedicated .ps1 file, then "source" that file into your main script to access those variables. Think of it like a reusable config file your script can reference whenever it needs server details.

2. How to create an array of hashtables for your servers #

Since you're working with multiple servers, an array of hashtables is ideal—each hashtable holds the unique details for one server. Here's how to set this up:

Create a new file (name it something like ServerConfigs.ps1) and add this code:

# Array of server configurations
$ServerList = @(
    @{
        ServerName = "ProdSQL01"
        Port       = 1433
        Instance   = "MSSQLSERVER" # Default instance, can leave blank if needed
        Database   = "SalesDB" # Optional: add if you target the same DB every time
    },
    @{
        ServerName = "DevSQL02"
        Port       = 1434
        Instance   = "DevInstance"
        Database   = "DevTestDB"
    },
    @{
        ServerName = "ReportSQL03"
        Port       = 50000
        Instance   = "Reporting"
        Database   = "ReportServer"
    }
)

• Feel free to add extra fields like Credential if you need specific auth for individual servers.
• For default SQL instances, Instance is typically MSSQLSERVER, but you can omit it or set it to an empty string if you prefer.

3. Integrating this into your main script #

Now just pull in the config file and loop through each server to run Invoke-SqlCmd. Here's a sample main script (Run-SqlQueries.ps1):

# Source the config file to load the $ServerList variable
# Use $PSScriptRoot to avoid path issues (points to the current script's directory)
. "$PSScriptRoot\ServerConfigs.ps1"

# Loop through each server in the config list
foreach ($server in $ServerList) {
    # Build the ServerInstance parameter value dynamically
    $serverInstance = if (-not [string]::IsNullOrEmpty($server.Instance)) {
        "$($server.ServerName)\$($server.Instance),$($server.Port)"
    } else {
        "$($server.ServerName),$($server.Port)"
    }

    try {
        # Run your Invoke-SqlCmd command with the server's details
        Invoke-SqlCmd -ServerInstance $serverInstance `
                      -Database $server.Database `
                      -Query "SELECT GETDATE() AS CurrentTime, @@SERVERNAME AS ServerName" `
                      -ErrorAction Stop
        
        Write-Host "Successfully ran query on $($server.ServerName)" -ForegroundColor Green
    } catch {
        Write-Host "Failed to connect to $($server.ServerName): $_" -ForegroundColor Red
    }
}

Quick tips: #

• The . "$PSScriptRoot\ServerConfigs.ps1" line is critical—it imports the config variables into your main script's scope.
• We build ServerInstance dynamically to handle both default and named instances correctly.
• Adding try/catch blocks lets you handle connection errors gracefully instead of crashing the entire script.
• You can expand this to run different queries per server by adding a Query key to each hashtable in the config file.

内容的提问来源于stack exchange，提问作者user770022