User Tools

Site Tools


This is an old revision of the document!

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

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}}
windows/powershell/updateinstance.1402967000.txt.gz · Last modified: 2018/05/14 21:58 (external edit)