Data growth has raised the stakes in the market increasing the size of SQL Server estates that DBAs are expected to manage. Because of this, fast and efficient configuration management can be very helpful. To this end, let’s delve into this article to generate a SQL Server inventory using PowerShell.
数据增长已经增加了市场的风险，增加了DBA期望管理SQL Server资产的规模。 因此，快速有效的配置管理将非常有帮助。 为此，让我们深入研究本文以使用PowerShell生成SQL Server清单。
In the previous article, we discussed how to discover SQL instances automatically. In this article, we’ll build on that to see how to capture the SQL Server setting details of the database environment in CSV file. Once complete, we’ll have a script that will iterate through all of our instances and document their most important settings
在上一篇文章中，我们讨论了如何自动发现SQL实例 。 在本文的基础上，我们将了解如何捕获CSV文件中数据库环境SQL Server设置详细信息。 完成后，我们将拥有一个脚本，该脚本将遍历所有实例并记录其最重要的设置
脚本结构 (Script structure)
- In the first part of our inventory, we’ll list the ServerName in the input CSV file. We’re going to import the server list using the Import-CSV cmdlet. 在清单的第一部分，我们将在输入CSV文件中列出ServerName。 我们将使用Import-CSV cmdlet导入服务器列表。
- Next, we’re going to take a look and see what SQL Server properties are available. In this case, we’re going to pass in the name of the SQL Server. 接下来，我们将看一下可用SQL Server属性。 在这种情况下，我们将传递SQL Server的名称。
- Next, we’ll list the SQL Server instances from browsing the SQL Server folder using the Get-ChildItem cmdlet. The output has several properties such as ComputerNamePhysicalNetBIOS, Name, DisplayName and Instance and so on. In the properties list, one of those is ComputerNamePhysicalNetBIOS. It is then assigned to a variable named $instance. 接下来，我们将使用Get-ChildItem cmdlet通过浏览SQL Server文件夹列出SQL Server实例。 输出具有多个属性，例如ComputerNamePhysicalNetBIOS，Name，DisplayName和Instance等。 在属性列表中，其中之一是ComputerNamePhysicalNetBIOS。 然后将其分配给名为$ instance的变量。
Let’s take a look at the following PowerShell code.
让我们看一下下面的PowerShell代码。
The SQL Server folder is traversed for the server HQDBT01 using the ForEach-Object cmdlet.
使用ForEach-Object cmdlet遍历服务器HQDBT01SQL Server文件夹。
'hqdbt01'|
ForEach-Object {
Get-ChildItem -Path "SQLSERVER:\SQL\$_"} | Select-Object -Property
ComputerNamePhysicalNetBIOS, Name, DisplayName, InstanceName
In the output, you can see that the Server HQDBT01 is configured with two SQL Server instances. The first, default instance named HQDBT01 and the second, named instance, HQDBT01\SQL2017.
在输出中，您可以看到Server HQDBT01配置了两个SQL Server实例。 第一个默认实例名为HQDBT01，第二个默认实例HQDBT01 \ SQL2017。
Note: The InstanceName column has a value SQL2017. This indicates that the SQL Server named instance is up and running.
注意：InstanceName列的值为SQL2017。 这表明SQL Server命名实例已启动并正在运行。
In the following example, the SQL instances are discovered using the following PowerShell script. But you notice that the named instance CRM2011 has a NULL value for the instance field. This indicates that the named instance is down.
在以下示例中，使用以下PowerShell脚本发现SQL实例。 但是您会注意到，命名实例CRM2011的实例字段具有NULL值。 这表明命名实例已关闭。
'hqdbsp17'|
ForEach-Object {
Get-ChildItem -Path "SQLSERVER:\SQL\$_"} | Select-Object -Property
ComputerNamePhysicalNetBIOS, Name, DisplayName, InstanceName
Note: The InstanceName column has a NULL value. This indicates that the SQL Server named instance is down and it is in not running state.
注意：InstanceName列的值为NULL。 这表明SQL Server命名实例已关闭，并且未处于运行状态。
We all agree that there is chance to have multiple instances of SQL on the single machine. In this section, we’ll take a look at fetching the instance names from the aforementioned snippets. Let’s dissect the PowerShell script to understand better about selecting the SQL instance.
我们都同意，有机会在一台计算机上具有多个SQL实例。 在本节中，我们将介绍从上述代码片段中获取实例名称的过程。 让我们剖析PowerShell脚本，以更好地了解有关选择SQL实例的信息。
The following piece of code compares the DiplayName property with DEFAULT or MSSQLSERVER values. On satisfying the condition, the Name property value is assigned to a variable named $SQL. If the condition is not met, then the InstanceName property is validated for NOT NULL value and the corresponding value of the named instance is assigned to the $SQL variable. As I already mentioned in the above note, the NULL value in the InstanceName field indicates that the named instance is down. In such a case, we’ll not able to build or pull any SQL Server properties.
下面的代码将DiplayName属性与DEFAULT或MSSQLSERVER值进行比较。 满足条件后，会将Name属性值分配给名为$ SQL的变量。 如果不满足该条件，那么将对InstanceName属性的NOT NULL值进行验证，并将命名实例的相应值分配给$ SQL变量。 正如我在上面的注释中已经提到的，InstanceName字段中的NULL值指示命名实例已关闭。 在这种情况下，我们将无法构建或提取任何SQL Server属性。
Note: The authentication is done at the scope of PowerShell session level with the security level of the currently running user access.
注意：验证是在PowerShell会话级别的范围内，使用当前正在运行的用户访问的安全级别进行的。
If (($_.DisplayName -eq 'DEFAULT') -or ($_.DisplayName -eq 'MSSQLSERVER')) {
$SQL = $_.name
}
elseif ($_.InstanceName -ne $NULL)
{
#$SQLInstance = "$($_.ComputerNamePhysicalNetBIOS)\$($_.InstanceName)"
$SQL = $_.name
}
Note: You can also see the commented line of the above code to prepare the named SQLInstance
注意：您还可以查看上面代码的注释行以准备命名SQLInstance
SMO（SQLServer管理对象） (SMO (SQLServer Management Objects))
In this section, you’ll see the working of the SMO (SQL Server Management Objects) and the SQL Provider together. It is evident that DBAs can perform or find a lot of tasks using either one of them. So far, we see that using SQL provider how we derived the SQL instance details. Now it’s time to take a look at how we can use the SQL Management Object library.
在本节中，您将看到SMO（SQL Server管理对象）和SQL Provider一起工作。 显然，DBA可以使用其中任何一个执行或查找许多任务。 到目前为止，我们看到使用SQL提供程序如何导出SQL实例详细信息。 现在是时候看看如何使用SQL管理对象库了。
SMO Library is a .NET class library that Microsoft designed for working with SQL Server. PowerShell at the very core, treats everything as an object. In this case, the server object has a corresponding entities associated with it. Each server properties can be accessed using object instantiation. For example, a database object has certain properties and methods that can be described by object invocation process. Through the server object, we can go through the different collections such “server information”, “Security information”, “Configuration”, “Server Settings” etc:-. And as we iterate over that collection we can return individual properties of the each of the collections.
SMO库是Microsoft为与SQL Server一起使用而设计的.NET类库。 PowerShell的核心是将所有内容都视为一个对象。 在这种情况下，服务器对象具有与之关联的相应实体。 可以使用对象实例化访问每个服务器属性。 例如，数据库对象具有某些可以由对象调用过程描述的属性和方法。 通过服务器对象，我们可以浏览不同的集合，例如“服务器信息”，“安全信息”，“配置”，“服务器设置”等：-。 当我们遍历该集合时，我们可以返回每个集合的单独属性。
In the following example, an array of server names is fed and the generated list of the SQL Instances is iterated through to pull the details of the various collections’ SQL server settings.
在下面的示例中，将馈入服务器名称数组，并迭代生成SQL实例列表以提取各种集合SQL Server设置的详细信息。
'amwrp01','amwsrp02',’aqdbt01’|
ForEach-Object {
Get-ChildItem -Path "SQLSERVER:\SQL\$_"} | Select-Object -Property ComputerNamePhysicalNetBIOS, Name, DisplayName, InstanceName |
%{
If (($_.DisplayName -eq 'Default') -or ($_.DisplayName -eq 'MSSQLSERVER')) {
$SQL = $_.name
}
elseif ($_.InstanceName -ne $NULL)
{
#$SQLInstance = "$($_.ComputerNamePhysicalNetBIOS)\$($_.InstanceName)"
$SQL = $_.name
}
New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQL |
SELECT @{label='ServerName';expression={$_.ComputerNamePhysicalNetBIOS}}, @{label='InstanceName';expression={$_.Name}} ,
Edition,BuildClrVersionString,BuildNumber,ErrorLogPath,HasNullSaPassword,IsPolyBasedInstalled,IsCaseSensitive,Platform,IsFullTextInstalled,Language,MasterDBLogPath,MasterDBPath,NetName,OSVersion,PhysicalMemory,IsSingleUser,Product,
VersionString,Collation,IsClustered,ProductLevel,ComputerNamePhysicalNetBIOS,ResourceLastUpdateDateTime,AuditLevel,BackupDirectory,DefaultFile,DefaultLog,LoginMode,ServiceAccount,ServiceStartMode,State,MailProfile,NamedPipesEnabled
}
In the following example, we can see how to capture the server settings (Generate SQL Inventory) in a CSV file. For this requirement, PowerShell script requires an input file and directory to save the output file
在以下示例中，我们可以看到如何在CSV文件中捕获服务器设置（生成SQL库存）。 为此，PowerShell脚本需要输入文件和目录来保存输出文件
先决条件 (Pre-requisites)
Let us discuss in detail about the required parameters:
让我们详细讨论所需的参数：
- User account must have the required permission to access the servers 用户帐户必须具有访问服务器所需的权限
For Example, the file serverlist.txt file should list all of the SQL instances like below:
例如，文件serverlist.txt文件应列出所有SQL实例，如下所示：
asdbsp15
asdbsp17asdbsp15
asdbsp17- DirectoryToSave – this is storehouse path for the generated output file. DirectoryToSave –这是生成的输出文件的仓库路径。
Let me dissect the internals of the PowerShell script further for better understanding.
I would like to divide this into five parts.
为了更好地理解，让我进一步剖析PowerShell脚本的内部。
我想将其分为五个部分。
- The first, the input and output file 一，输入输出文件
- The second part, listing the SQL instances by traversing the SQL Server folder using Get-Childitem and foreach-object cmdlets 第二部分，使用Get-Childitem和foreach-object cmdlet遍历SQL Server文件夹，列出SQL实例
- The third, an instantiation of the SMO class library 第三，SMO类库的实例化
- The fourth, transforming and generating SQL Server inventory information 第四，转换和生成SQL Server清单信息
- and the last, writing the data to CSV file 最后，将数据写入CSV文件
The following are the output columns that are listed as part of the process
以下是在过程中列出的输出列
- ServerName 服务器名称
- InstanceName 实例名称
- Edition 版
- BuildClrVersionString BuildClrVersionString
- BuildNumber 内部编号
- ErrorLogPath 错误日志路径
- HasNullSaPassword HasNullSaPassword
- IsPolyBasedInstalled IsPolyBased已安装
- IsCaseSensitive IsCaseSensitive
- Platform 平台
- IsFullTextInstalled IsFullText已安装
- Language 语言
- MasterDBLogPath 主数据库日志路径
- MasterDBPath 主数据库路径
- NetName 网络名称
- OSVersion 操作系统版本
- PhysicalMemory 物理内存
- IsSingleUser IsSingleUser
- Product 产品
- VersionString 版本字符串
- Collation 校对
- IsClustered 集群
- ProductLevel 产品等级
- ComputerNamePhysicalNetBIOS ComputerNamePhysicalNetBIOS
- ResourceLastUpdateDateTime ResourceLastUpdateDateTime
- AuditLevel 审核级别
- BackupDirectory 备份目录
- DefaultFile 默认文件
- DefaultLog 默认日志
- LoginMode 登录模式
- ServiceAccount 服务帐号
- ServiceStartMode ServiceStartMode
- State 州
- MailProfile 邮件资料
- NamedPipesEnabled NamedPipesEnabled
Here is an image of the generated CSV file.
这是生成的CSV文件的图像。
摘要 (Summary)
We started first here with enabling and customizing inventory, determining which classes contain information that may be useful for us down the road. We also enabled and customized the inventory details to understand the different characteristics of the SQL instances. I will discuss the process of building a full-fledged SQL Server inventory with data manipulation techniques using PowerShell in my next article. With this script you can fully inventory and document your SQL Server estate on demand
我们首先从启用和定制清单开始，确定哪些类别包含可能对我们将来有用的信息。 我们还启用并自定义了清单详细信息，以了解SQL实例的不同特征。 在下一篇文章中，我将讨论使用PowerShell使用数据操作技术构建完整SQL Server库存的过程。 使用此脚本，您可以按需全面盘点和记录SQL Server资产。
I hope you enjoyed reading this article. Feel free comment below for any questions.
希望您喜欢阅读本文。 如有任何疑问，请在下面发表评论。
附录–脚本 (Appendix – Script )
<#=================================
# Generated On: 1/07/2019
# Generated By: Prashanth Jayaram
# Version : 1.0
# Desc : SQL Inventory Generation
EXAMPLE 1 :Output CSV and no-Email
#PS:\>.\SQLServerInventory.ps1 -InputFileName c:\server123.csv -DirectoryToSaveTo C:\
#=================================
#>
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True,Position=1)]
[string]$InputFileName,
[Parameter(Mandatory=$True,Position=2)]
[string]$DirectoryToSaveTo
)
#Prepare output file name
$Filename='SQLInventory'
#delete the file if it already exists
$filename = "$DirectoryToSaveTo$filename.csv"
if (test-path $filename ) { rm $filename } #delete the file if it already exists
#An array to hold the output values
$Results=@()
#import the CSV file
$instance=Import-Csv $InputFileName|%{
ForEach-Object {
Get-ChildItem -Path "SQLSERVER:\SQL\$($_.server)"} | Select-Object -Property ComputerNamePhysicalNetBIOS, Name, DisplayName, InstanceName
}
ForEach($i in $instance)
{
If (($i.DisplayName -eq 'Default') -or ($i.DisplayName -eq 'MSSQLSERVER')) {
$SQL = $i.ComputerNamePhysicalNetBIOS
}
elseif ($i.InstanceName -ne $NULL)
{
#$SQLInstance = "$($_.ComputerNamePhysicalNetBIOS)\$($_.InstanceName)"
$SQL = $i.name
}
$instance=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQL |
SELECT @{label='ServerName';expression={$_.ComputerNamePhysicalNetBIOS}}, @{label='InstanceName';expression={$_.Name}} ,
Edition,BuildClrVersionString,BuildNumber,ErrorLogPath,HasNullSaPassword,IsPolyBasedInstalled,IsSinIsCaseSensitive,Platform,IsFullTextInstalled,Language,MasterDBLogPath,MasterDBPath,NetName,OSVersion,PhysicalMemory,IsSingleUser,Product,
VersionString,Collation,IsClustered,ProductLevel,ComputerNamePhysicalNetBIOS,ResourceLastUpdateDateTime,AuditLevel,BackupDirectory,DefaultFile,DefaultLog,LoginMode,ServiceAccount,ServiceStartMode,State,MailProfile,NamedPipesEnabled
ForEach($i in $instance)
{
$Properties = @{Name=$i.InstanceName
ComputerNamePhysicalNetBIOS =$i.ServerName
Edition=$i.Edition
BuildClrVersionString=$i.BuildClrVersionString
BuildNumber=$i.BuildNumber
ErrorLogPath=$i.ErrorLogPath
HasNullSaPassword=$i.HasNullSaPassword
IsPolyBasedInstalled=$i.IsPolyBasedInstalled
IsSinIsCaseSensitive=$i.IsSinIsCaseSensitive
Platform=$i.Platform
IsFullTextInstalled=$i.IsFullTextInstalled
Language=$i.Language
MasterDBLogPath=$i.MasterDBLogPath
MasterDBPath=$i.MasterDBPath
NetName=$i.NetName
OSVersion=$i.OSVersion
PhysicalMemory=$i.PhysicalMemory
IsSingleUser=$i.IsSingleUser
Product=$i.Product
VersionString=$i.VersionString
Collation=$i.Collation
IsClustered=$i.IsClustered
ProductLevel=$i.ProductLevel
ResourceLastUpdateDateTime=$i.ResourceLastUpdateDateTime
AuditLevel=$i.AuditLevel
BackupDirectory=$i.BackupDirectory
DefaultFile=$i.DefaultFile
DefaultLog=$i.DefaultLog
LoginMode=$i.LoginMode
ServiceAccount=$i.ServiceAccount
ServiceStartMode=$i.ServiceStartMode
State=$i.State
MailProfile=$i.MailProfile
NamedPipesEnabled=$i.NamedPipesEnabled
}
$Results += New-Object psobject -Property $properties
}
####Comment the below line, if you wish to not to display an output to the console
$Results
#####
$Results | select Name ,ComputerNamePhysicalNetBIOS,Edition,BuildClrVersionString,BuildNumber,ErrorLogPath,HasNullSaPassword,IsPolyBasedInstalled,IsSinIsCaseSensitive,Platform,IsFullTextInstalled,Language,MasterDBLogPath,MasterDBPath,NetName,OSVersion,PhysicalMemory,IsSingleUser,Product,
VersionString,Collation,IsClustered,ProductLevel,ResourceLastUpdateDateTime,AuditLevel,BackupDirectory,DefaultFile,DefaultLog,LoginMode,ServiceAccount,ServiceStartMode,State,MailProfile,NamedPipesEnabled| export-csv -Path $filename -NoTypeInformation
}
翻译自: https://www.sqlshack.com/inventory-and-document-your-sql-server-estate-using-powershell/
144
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
