使用PowerShell管理SQL数据库：服务器资产跟踪模块构建

### 使用 PowerShell 管理 SQL 数据库：服务器资产跟踪模块构建 在 IT 工作中，我们常常会遇到部门依赖的 Excel 电子表格或 Access 数据库出现故障的情况。深入调查后，会发现多年来拼凑的复杂宏代码让人头疼。其实，这往往是有人为了改进工作而临时搭建的系统，后来整个部门都依赖它。为了避免陷入这样的困境，我们可以学习使用合适的数据库。 #### 1. 背景与需求 在复杂的混合环境中，跟踪服务器的位置和类型变得日益困难。我们要创建一个 PowerShell 模块，用于跨不同环境跟踪服务器资产。该模块会将服务器数据存储在 SQL 数据库中，并具备以下功能： 1. 向数据库添加服务器。 2. 在数据库中搜索服务器。 3. 更新一个或多个服务器的信息。 我们可以使用免费的 Microsoft SQL Server Express，若之前已安装，可直接使用；未安装的话，可使用相关脚本进行安装和设置。同时，将使用 `dbatools` PowerShell 模块与 SQL 进行交互。 #### 2. 创建数据库 使用 `New-DbaDatabase` cmdlet 创建数据库，只需提供 SQL 实例和数据库名称，并将恢复模式设置为“Simple”。以下是创建 `PoshAssetMgmt` 数据库的代码： ```powershell $SqlInstance = "$($env:COMPUTERNAME)\SQLEXPRESS" $DatabaseName = 'PoshAssetMgmt' $DbaDatabase = @{ SqlInstance = $SqlInstance Name = $DatabaseName RecoveryModel = 'Simple' } New-DbaDatabase @DbaDatabase ``` 若在 SQL Express 安装的同一台机器上运行 PowerShell，可直接运行上述代码；若在远程机器或使用自己的 SQL 实例，需更新 `$SqlInstance` 变量。 #### 3. 设置表结构 ##### 3.1 确定列 创建存储服务器信息的表时，可先设置一些标准列： 1. **Name**：资产名称。 2. **Operating System Type**：操作系统类型（Linux 或 Windows）。 3. **Operating System Version**：操作系统版本名称。 4. **Status**：服务器状态（运行、维修或停用）。 5. **Remote Method**：远程连接方法（SSH、WSMan、Power CLI 等）。 此外，还需添加一些列用于引用外部系统，如 UUID、Source 和 Source Instance。同时，创建一个标识列，让数据库自动为每个条目分配 ID，方便在表间引用和更新数据。也可根据需求添加其他字段，但要避免数据重复。 ##### 3.2 确定数据类型 确定表结构时，需考虑数据类型。以字符串类型为例，SQL 中有多种字符串类型，常用的是 `nvarchar` 类型，它可容纳 1 到 4000 字节对，且支持 Unicode 字符。声明 `nvarchar` 时，需设置最大字符长度。对于其他字段，根据所需数字大小选择合适的数据类型，如 `int`、`float` 等。同时，要考虑是否允许空值。 以下是 `Servers` 表的数据类型映射： | Name | Type | MaxLength | Nullable | Identity | | ---- | ---- | ---- | ---- | ---- | | ID | int | N/A | No | Yes | | Name | nvarchar | 50 | No | No | | OSType | nvarchar | 15 | No | No | | OSVersion | nvarchar | 50 | No | No | | Status | nvarchar | 15 | No | No | | RemoteMethod | nvarchar | 25 | No | No | | UUID | nvarchar | 255 | No | No | | Source | nvarchar | 15 | No | No | | SourceInstance | nvarchar | 255 | No | No | ##### 3.3 创建表 使用 `New-DbaDbTable` cmdlet 创建表，先在哈希表中定义每列，再将不同的哈希表添加到数组中，最后将数组传递给 `-ColumnMap` 参数。以下是创建 `Servers` 表的代码： ```powershell $SqlInstance = "$($env:COMPUTERNAME)\SQLEXPRESS" $DatabaseName = 'PoshAssetMgmt' $ServersTable = 'Servers' $ServersColumns = @( @{Name = 'ID'; #A Type = 'int'; MaxLength = $null; Nullable = $false; Identity = $true; } @{Name = 'Name'; #B Type = 'nvarchar'; MaxLength = 50; Nullable = $false; Identity = $false; } @{Name = 'OSType'; #C Type = 'nvarchar'; MaxLength = 15; Nullable = $false; Identity = $false; } @{Name = 'OSVersion'; #D Type = 'nvarchar'; MaxLength = 50; Nullable = $false; Identity = $false; } @{Name = 'Status'; #E Type = 'nvarchar'; MaxLength = 15; Nullable = $false; Identity = $false; } @{Name = 'RemoteMethod'; #F Type = 'nvarchar'; MaxLength = 25; Nullable = $false; Identity = $false; } @{Name = 'UUID'; #G Type = 'nvarchar'; MaxLength = 255; Nullable = $false; Identity = $false; } @{Name = 'Source'; #H Type = 'nvarchar'; MaxLength = 15; Nullable = $false; Identity = $false; } @{Name = 'SourceInstance'; #I Type = 'nvarchar'; MaxLength = 255; Nullable = $false; Identity = $false; } ) $DbaDbTable = @{ SqlInstance = $SqlInstance Database = $DatabaseName Name = $ServersTable ColumnMap = $ServersColumns } New-DbaDbTable @DbaDbTable ``` 创建好表后，就可以创建与之交互的模块和函数了。 #### 4. 连接到 SQL 为避免每次调用函数都传递 SQL 实例和数据库参数，可在模块的 `psm1` 文件中设置变量，供函数引用。变量名最好具有模块唯一性，可在开头添加下划线。将连接信息作为一个 PowerShell 对象，包含 SQL 服务器实例、数据库和表名等属性，能使代码更简洁易管理。 以下是创建 `PoshAssetMgmt` 模块的步骤和代码： 1. 使用 `New-ModuleTemplate` 函数生成模块基础结构。 ```powershell Function New-ModuleTemplate { [CmdletBinding()] [OutputType()] param( [Parameter(Mandatory = $true)] [string]$ModuleName, [Parameter(Mandatory = $true)] [string]$ModuleVersion, [Parameter(Mandatory = $true)] [string]$Author, [Parameter(Mandatory = $true)] [string]$PSVersion, [Parameter(Mandatory = $false)] [string[]]$Functions ) $ModulePath = Join-Path .\ "$($ModuleName)\$($ModuleVersion)" New-Item -Path $ModulePath -ItemType Directory Set-Locati ```