windows:powershell:updateinstance
Differences
This shows you the differences between two versions of the page.
|
windows:powershell:updateinstance [2014/06/16 20:03] haidong Refactoring and test cases |
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> | ||
| - | function updateInstanceSQL($i) { | ||
| - | $InstanceName = $i.InstanceName | ||
| - | $InstanceID = $i.InstanceID | ||
| - | |||
| - | $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 | ||
| - | |||
| - | if ($InstanceName.Contains("\")) { | ||
| - | $serviceName = "mssql`$" + $InstanceName.split("\")[1] | ||
| - | $service = Get-WmiObject win32_service -ComputerName $InstanceName.split("\")[0] | where {$_.Name -eq $serviceName}} | ||
| - | else { | ||
| - | $serviceName = "mssqlserver" | ||
| - | $service = Get-WmiObject win32_service -ComputerName $InstanceName | where {$_.Name -eq $serviceName}} | ||
| - | |||
| - | $StartupAcct = $service.StartName | ||
| - | $sql = "EXEC Windows.Instance_Update $InstanceID, '$InstanceEdition', '$InstanceEditionID', '$InstanceVersion', '$InstanceServicePack', '$StartupAcct'" | ||
| - | return $sql} | ||
| - | |||
| - | $InstanceList = Invoke-Sqlcmd -Query "exec Windows.Instance_Select_InstanceID_InstanceName" -ServerInstance "sql1" -Database "JiMetrics" | ||
| - | $InstanceList | ForEach-Object { | ||
| - | try { | ||
| - | $sql = updateInstanceSQL($_) | ||
| - | Invoke-Sqlcmd -Query $sql -ServerInstance "sql1" -Database "JiMetrics"} | ||
| - | Catch [Exception] {Continue}} | ||
| - | </code> | ||
windows/powershell/updateinstance.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
