User Tools

Site Tools


windows:powershell:updateinstance

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

windows:powershell:updateinstance [2013/10/15 15:45]
haidong created
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 "​SysMetrics"​ 
-$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 "​SysMetrics"​ 
-        } 
-    Catch { Return } 
-} 
-</​code>​ 
windows/powershell/updateinstance.txt ยท Last modified: 2018/05/14 22:00 (external edit)