This shows you the differences between two versions of the page.
windows:powershell:updateinstance [2013/11/04 21:22] haidong Change SysMetrics to JiMetrics |
windows:powershell:updateinstance [2018/05/14 22:00] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | After server host SQl Server instance(s) have been discovered, here is a PowerShell script to update those instances with information like SQL Server edition, product version, service pack level, etc. Please create a SQL Server Agent job for this. The domain account that this job runs under needs to have access to the instances. | ||
- | This information should be fairly static. Depending on your situation, scheduling this to run weekly, monthly, or quarterly, or whatever. You can always manually kick off this job if needed. | ||
- | |||
- | ** Remember to change "sql1" to your host instance name ** | ||
- | |||
- | <code powershell updateInstance.ps1> | ||
- | $InstanceList = Invoke-Sqlcmd -Query "exec Windows.Instance_Select_InstanceID_InstanceName" -ServerInstance "sql1" -Database "JiMetrics" | ||
- | $InstanceList | ForEach-Object { | ||
- | |||
- | $InstanceName = $_.InstanceName | ||
- | $InstanceID = $_.InstanceID | ||
- | |||
- | Try { | ||
- | $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT SERVERPROPERTY('Edition')" | ||
- | $InstanceEdition = $result.column1 | ||
- | $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT | ||
- | SERVERPROPERTY('EditionID')" | ||
- | $InstanceEditionID = $result.column1 | ||
- | $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT SERVERPROPERTY('ProductVersion')" | ||
- | $InstanceVersion = $result.column1 | ||
- | $result = Invoke-Sqlcmd -ServerInstance $InstanceName -Query "SELECT SERVERPROPERTY('ProductLevel')" | ||
- | $InstanceServicePack = $result.column1 | ||
- | |||
- | $sql = "EXEC Windows.Instance_Update $InstanceID, | ||
- | '$InstanceEdition', '$InstanceEditionID', '$InstanceVersion', '$InstanceServicePack'" | ||
- | Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics" | ||
- | } | ||
- | Catch { Return } | ||
- | } | ||
- | </code> |