windows:powershell:insertinstanceconfig
Differences
This shows you the differences between two versions of the page.
|
windows:powershell:insertinstanceconfig [2013/11/05 11:33] haidong created |
windows:powershell:insertinstanceconfig [2018/05/14 22:00] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | Here is a PowerShell script to gather SQL Server instance configuration information. It comes from sys.configurations view. Please create a SQL Server Agent job for this. The domain account that this job runs under needs to have proper access to the instances in question and be able to insert data into the JiMetrics database. | ||
| - | ** Remember to change "sql1" to your host instance name ** | ||
| - | |||
| - | <code powershell insertInstanceConfig.ps1> | ||
| - | function getInstanceConfig($ServerInstance) | ||
| - | { | ||
| - | $InstanceConfigQuery = @" | ||
| - | SELECT [configuration_id] | ||
| - | , [name] | ||
| - | , [value] | ||
| - | , [value_in_use] | ||
| - | FROM [sys].[configurations]; | ||
| - | "@ | ||
| - | try { | ||
| - | $InstanceConfigList = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query $InstanceConfigQuery -Database "master" | ||
| - | } | ||
| - | catch {} | ||
| - | $dataIndexArray = New-Object System.Collections.ArrayList | ||
| - | $InstanceConfigList | foreach { | ||
| - | $myHashtable = @{Configuration_Id = $_.configuration_id; Name = | ||
| - | $_.name; Value = $_.value; ValueInUse = $_.value_in_use} | ||
| - | [void] $dataIndexArray.add($myHashtable) | ||
| - | } | ||
| - | $dataIndexArray | ||
| - | } | ||
| - | |||
| - | $InstanceList = Invoke-Sqlcmd -Query "exec Windows.Instance_Select_InstanceID_InstanceName" -ServerInstance "sql1" -Database "JiMetrics" | ||
| - | $InstanceList | ForEach-Object { | ||
| - | |||
| - | $InstanceName = $_.InstanceName | ||
| - | $InstanceID = $_.InstanceID | ||
| - | |||
| - | Try { | ||
| - | $InstanceConfigArray = getInstanceConfig($InstanceName) | ||
| - | $InstanceConfigArray | ForEach-Object { | ||
| - | $ConfigurationId, $Name, $Value, $ValueInUse = | ||
| - | $_.Configuration_Id, $_.Name, $_.Value, $_.ValueInUse | ||
| - | |||
| - | $sql = "EXEC Windows.InstanceConfig_Insert $InstanceID, | ||
| - | $ConfigurationId, '$Name', '$Value', '$ValueInUse'" | ||
| - | Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics" | ||
| - | } | ||
| - | } | ||
| - | Catch { Return } | ||
| - | } | ||
| - | </code> | ||
windows/powershell/insertinstanceconfig.txt ยท Last modified: 2018/05/14 22:00 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
