SQL Server – Get Version Numbers Using PowerShell

Background

I have a list of SQL Server Instances that we manage.

Wanted to get Version Numbers for the sake of ensuring that we have the last Service Pack for each SQL Server Version.

Here is the set of scripts that we have thus far.

Code

Source Code Type File Use
 Data files
datafile\SQLServerInstances.txt List of SQL Server Instances; each SQL Server Instance in a line by itself
 Microsoft C#
 _SQLInstanceNode.cs Supporting – Object ( _SQLInstanceNode )
 _SQLServicePackLatest.cs Supporting – Object ( _SQLServicePackLatest & _SQLServicePackLatestList )
 Microsoft PowerShell
 SQLInstanceVersion.ps1  Main App

 

 

Code – C#- _SQLInstanceNode.cs



using System;
using System.Collections.Generic;
using System.Collections;

public class _SQLInstanceNode
{
	
	public _SQLInstanceNode()
	{
		errorMessage = "";
		failedItem = "";
		patchedATLatest = ' ';
	}		
	
	public bool	  active { get; set; }
	public string sqlInstance { get; set; }
	public string edition { get; set; }
	public string productLevel { get; set; }
	public string productVersion { get; set; }
	public string marketing { get; set; }
	
	public string servicePackLatest { get; set; }
	public char   patchedATLatest { get; set; }
	
	public string errorMessage { get; set; }
	public string failedItem { get; set; }

}

Code – C#- _SQLServicePackLatest.cs





using System;
using System.Collections.Generic;
using System.Collections;

public class _SQLServicePackLatest
{
	
	public string marketing   { get; set; }
	public string servicePack { get; set; }
	public int    servicePackAsInt { get; set; }

}


public class _SQLServicePackLatestList
{
	
	private ArrayList _objList = new ArrayList();
	private _SQLServicePackLatest objSPK;
	
	public ArrayList list
    {
        get { return _objList; }
    }
	
	public int count
    {
        get { return _objList.Count; }
    }
	
		
	public _SQLServicePackLatestList()
	{
		
		//2016
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2016";
		objSPK.servicePack = "RTM";
		objSPK.servicePackAsInt = 0;
		_objList.Add(objSPK);
		
		//2014
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2014";
		objSPK.servicePack = "SP2";
		objSPK.servicePackAsInt = 2;
		_objList.Add(objSPK);
		
		
		//2012
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2012";
		objSPK.servicePack = "SP3";
		objSPK.servicePackAsInt = 3;
		_objList.Add(objSPK);
		
		//2008-R2
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2008-R2";
		objSPK.servicePack = "SP3";
		objSPK.servicePackAsInt = 3;
		_objList.Add(objSPK);
				
		//2008
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2008";
		objSPK.servicePack = "SP4";
		objSPK.servicePackAsInt = 4;
		_objList.Add(objSPK);
						
		//2005
		objSPK = new _SQLServicePackLatest();
		objSPK.marketing = "2005";
		objSPK.servicePack = "SP4";
		objSPK.servicePackAsInt = 4;
		_objList.Add(objSPK);
								
			
	
	}
	
	
}


Code – PowerShell – SQLInstanceVersion.ps1



<# Functions a) Create Your Own PowerShell Functions http://windowsitpro.com/windows/create-your-own-powershell-functions b) PowerShell – Create collections of custom objects https://www.andreasbijl.com/powershell-create-collections-of-custom-objects/ c) Scope::Global https://www.sapien.com/blog/2013/03/06/first-rule-of-powershell-scoping-rules/ #>
Set-StrictMode -Version 2

<# List of filenames #>
$fileIn = "datafile\SQLServerInstances.txt"
$fileOut = "datafile\SQLServerVersions.txt"
#$fileOutTabbed = "datafile\SQLServerVersionsTabbed.txt"
$fileOutFailed = "datafile\SQLServerVersionFailed.txt"

#DB (SQL Server) Connection String 
$SQLConnectionStringTemplate = "Server={0}; Integrated Security = True;;Connect Timeout={1};";	

#DB (SQL Server) Connection Timeout / Default is 20 seconds
$connectionTimeout = 3;


<# Query for DB Version Using 1) ServerProperty a) serverName b) edition c) productLevel d) productVersion #>
$SQL_VERSION_RETRIEVE = 
"
	select 	
			  [sqlInstance] = serverproperty('serverName') 	
			, [edition]   = serverproperty('Edition') 
			, [productLevel] =serverproperty('productLevel')
			, [ProductVersion] = serverproperty('ProductVersion')
			, [marketing]
				= case cast
							(
								parseName
								(
									cast(serverproperty('ProductVersion') as sysname)
									, 4
								) 
								+ '.'
								+ parseName
								(
									cast(serverproperty('ProductVersion') as sysname)
									, 3
								) 
								as decimal(6, 2)
							)
						when 13 then '2016'							
						when 12 then '2014'
						when 11 then '2012'
						when 10.5 then '2008-R2'
						when 10 then '2008'
						when 9 then '2005'
						when 8 then '2000'
						else 'Undefined'
				  end 	
					  
									
"; 

<# Collections #>
$collectionSQLInstance = New-Object System.Collections.ArrayList
$collectionSQLVersion = New-Object System.Collections.ArrayList
$collectionSQLVersionInvalid = New-Object System.Collections.ArrayList

<# CHAR #>
$CHAR_TAB = "`t";
$CHAR_YES = "Y";
$CHAR_NO = "N";

<# String Format #>
$FORMAT_OUTPUT_SUCCESSFUL_HEADER = "{0,-28} {1,-20}  {2,-50}  {3,-20}  {4,-20}  {5,-6}   {6,-1} ";
$FORMAT_OUTPUT_SUCCESSFUL =  	   "{0,-28} {1,-20}  {2,-50}  {3,-20}  {4,-20}  {5,-6}   {6,-1} ";

#http://ss64.com/ps/syntax-esc.html
#`t  Horizontal tab
$FORMAT_OUTPUT_SUCCESSFUL_Tabbed = "{0,-28}`t{1,-20}`t{2,-50}`t{3,-20}`t{4,-20}`t{5,-6}`t{6,-1}";

$FORMAT_OUTPUT_FAILED = "SQL Instance: {0,-28}`r`nError:{1} ";

$FORMAT_SUMMARY_CONNECTION_ATTEMPTED  = "Number of attempted connections  {0,10}"; 
$FORMAT_SUMMARY_CONNECTION_SUCCESSFUL = "Number of successful connections {0,10}";
$FORMAT_SUMMARY_CONNECTION_FAILED     = "Number of failed connections     {0,10}";


<# Define Global Variables #>
$global:iNumberofSQLServerInstancesAttempted = 0;
$global:iNumberofSQLServerInstancesConnected = 0;
$global:iNumberofSQLServerInstancesFailed = 0;

<# addCSharpObjectTemplate Read C# file and make available to Powershell #>
function addCSharpObjectTemplate($file)
{
	
	$fileFullPath = resolve-path $file;
	
	$Source = [System.IO.File]::ReadAllText($file);
	 
	$strLog = "Importing Files :- fileFullPath: {0}"  -f $fileFullPath
	
	Write-Host $strLog
	
	Add-Type -TypeDefinition $Source -Language CSharpVersion3;

}


<# addCSharpObject Invokes addCSharpObjectTemplate for the listed files #>
function addCSharpObject
{

	$file = "_SQLInstanceNode.cs";
	addCSharpObjectTemplate($file);
	

	$file = "_SQLServicePackLatest.cs";
	addCSharpObjectTemplate($file);	

}


<# readInputFile Read Input file #>	
function readInputFile($filename, $collectionSQLInstance)
{
	ForEach ($sqlInstance in Get-Content $filename)
	{

		$collectionSQLInstance.Add($sqlInstance) | Out-Null
		
	}
}	


<# Connect to SQL Server Database from PowerShell http://stackoverflow.com/questions/25682703/connect-to-sql-server-database-from-powershell #>

function dbInteract($SQLServer)
{

	$bConnected = $false
	
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	
	$sqlConnectString = $SQLConnectionStringTemplate -f $SQLServer, $connectionTimeout
	
	$SqlConnection.ConnectionString = $sqlConnectString;

	
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	
	$SqlCmd.CommandText = $SQL_VERSION_RETRIEVE
						  		
	
	$SqlCmd.Connection = $SqlConnection 
	
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	
	$SqlAdapter.SelectCommand = $SqlCmd 
	
	$DataSet = New-Object System.Data.DataSet
	
	
	##Try
	Try
	{
	
		#Increment Counter - Attempt
		$global:iNumberofSQLServerInstancesAttempted++;
		
		$FORMAT_SQL_CONNECTING = "Connecting to {0,4}:{1}...";
		$log = $FORMAT_SQL_CONNECTING -f $global:iNumberofSQLServerInstancesAttempted, $SQLServer
	
		Write-Host $log
		

		<# Connect to Database, run SQL, and fill dataset #>
		$SqlAdapter.Fill($DataSet) 
				
		<# Get first dataset element and place in table variable #>
		$table = $DataSet.Tables[0]
		

		<# Get datarows from table #>
		ForEach ($dataRow in $table)
		{

			#Increment Counter - Connected
			$global:iNumberofSQLServerInstancesConnected++;
		
			#Instanciate SQlInstanceNode
			$objSQLNode = New-Object _SQLInstanceNode

			#Add SQlInstanceNode to collecton			
			$collectionSQLVersion.Add($objSQLNode) | Out-Null		

			#Read Database data and place into class (_SQLInstanceNode) instance variables
			$objSQLNode.active =1;
			$objSQLNode.sqlInstance = $dataRow["sqlInstance"].ToString();
			$objSQLNode.edition = $dataRow["edition"].ToString();
			$objSQLNode.productLevel = $dataRow["productLevel"].ToString();
			$objSQLNode.productVersion = $dataRow["productVersion"].ToString();
			$objSQLNode.marketing = $dataRow["marketing"].ToString();

			
		}
			
		$bConnected = $true
	

	}	
	<# Print full exception in PowerShell try/catch block using “format-list” https://www.leaseweb.com/labs/2014/01/print-full-exception-powershell-trycatch-block-using-format-list/ #>
	Catch
	{
	
		#Increment Counter - Failed
		$global:iNumberofSQLServerInstancesFailed++;

		#Instanciate SQlInstanceNode
		$objSQLNode = New-Object _SQLInstanceNode
		
		#Add SQlInstanceNode to collecton	
		$collectionSQLVersionInvalid.Add($objSQLNode) | Out-Null			

		#Set class (_SQLInstanceNode) instance variables when failure occurs
		$objSQLNode.active =0;
		$objSQLNode.sqlInstance = $SQLServer;
		
		$objSQLNode.errorMessage = $_.Exception.Message
		
		$bConnected = $false

		
	}
	
	if ($SqlConnection)
	{
		$SqlConnection.Close() 
	}		

	#End :database Intraction


}

<# processQLInstance Process list of sql server instances #>
function processQLInstance($collectionSQLInstance)
{

	$bConnected = $false;

	$log = "Processing through SQL Server Instances..."
	
	Write-Host $log
	
	ForEach ($sqlInstance in $collectionSQLInstance)
	{
	
		$bConnected = dbInteract ( $sqlInstance ) | Out-Null
		
	}
	
	matchToSQLServerVersionMatrix $collectionSQLVersion
	
}	


<# matchToSQLServerVersionMatrix For each SQL Server Instance processed: a) Find matching record in list of sql server latest service pack #>
function matchToSQLServerVersionMatrix($SQLInstanceListConnected)
{
		
	$objSQLSPLL = New-Object _SQLServicePackLatestList
	
	$objSPList = $objSQLSPLL.list;
	$count =  $objSQLSPLL.count;
	$i = 0;

	$log = "# of SQL Server Service Pack List {0}" -f $count
	Write-Debug $log	
		
	#Iterate SQL Instances
	ForEach ($objIns in $SQLInstanceListConnected)
	{
	
	    #Reset counters
		$i = 0;
		
		#Set related sql instance variable to defaults
		$objIns.patchedATLatest = $CHAR_NO;
		
		#Iterate SQL Server Service Pack List
		while ($i -lt $count  )
		{

			#Get contextual Service Pack
			$objSP = $objSPList[$i]
			
			#If SQL Instance marketing is same as SP List Marketing
			if ($objIns.marketing -eq $objSP.marketing)
			{
			
				#Atach SP List Latest service pack to SQL Instance [ServicePackLatest]
				$objIns.servicePackLatest = $objSP.servicePack;

				# if product level service pack is at latest patch then set indicator to Yes
				if ($objIns.productLevel -eq $objSP.servicePack )
				{
				
					$objIns.patchedATLatest = $CHAR_YES;
					
				}			
				
				#Once found exit Service Pack loop
				break;

				
			}
				
			#Go to next Service Pack	
			$i = $i + 1;			
			
		} ## while	
		
	}
	
}	

function writeResult($SQLInstanceListConnected)
{

	$log = $null;
	$logTabbedAlt = $null;

	$log = "Writing Result to {0}" -f $fileOut
	Write-Host $log
	
	$log = $FORMAT_OUTPUT_SUCCESSFUL_HEADER -f "SQLInstance", "ProductVersion", "Edition", "Marketing", "ProductLevel", "Latest", "Patched"
	Add-Content $fileOut $log	
	#Write-Host $log

	$log = $FORMAT_OUTPUT_SUCCESSFUL_HEADER -f "===========", "==============", "=======", "============", "=========", "======", "=======";	
	Add-Content $fileOut $log	
	#Write-Host $log
	
	ForEach ($objIns in $SQLInstanceListConnected)
	{
		
		#Format Output
		$log = $FORMAT_OUTPUT_SUCCESSFUL -f $objIns.sqlInstance,$objIns.productVersion `
											, $objIns.edition, $objIns.marketing `
											, $objIns.productLevel, $objIns.servicePackLatest `
											, $objIns.patchedATLatest
							
		
		#Write-Host $log
			
		Add-Content $fileOut $log
		
		<# $logTabbed = $FORMAT_OUTPUT_SUCCESSFUL_Tabbed ` -f $objIns.sqlInstance,$objIns.productVersion ` , $objIns.edition, $objIns.marketing ` , $objIns.productLevel, $objIns.servicePackLatest ` , $objIns.patchedATLatest Add-Content $fileOutTabbed $logTabbed #>
		
		<# $logTabbedAlt = $objIns.sqlInstance ` + $CHAR_TAB + $objIns.productVersion ` + $CHAR_TAB + $objIns.edition ` + $CHAR_TAB + $objIns.marketing ` + $CHAR_TAB + $objIns.productLevel ` + $CHAR_TAB + $objIns.servicePackLatest ` + $CHAR_TAB + $objIns.patchedATLatest ` + " (2)" Add-Content $fileOutTabbed $logTabbedAlt Add-Content $fileOutTabbed "" Add-Content $fileOutTabbed "" #>
		
	}
	
}	


function writeResultInvalid($collectionSQLVersionInvalid)
{

	$log = "Writing Result for failed connections to {0}" -f $fileOutFailed
	Write-Host $log
	

	#$collectionSQLVersionInvalid
		
	$log = "";
	
	
	ForEach ($sqlIns in $collectionSQLVersionInvalid)
	{
		
		$log = $FORMAT_OUTPUT_FAILED -f $sqlIns.sqlInstance, $sqlIns.errorMessage;
		
		#Write-Host $log
		
		Add-Content $fileOutFailed $log
		
		Add-Content $fileOutFailed ""
		Add-Content $fileOutFailed ""
		
	}
	
	
}	


function writeSummary()
{

	#$iNumberofSQLServerInstancesAttempted
	$log = $FORMAT_SUMMARY_CONNECTION_ATTEMPTED -f $global:iNumberofSQLServerInstancesAttempted
	Write-Host $log
	
	$log = $FORMAT_SUMMARY_CONNECTION_SUCCESSFUL -f $global:iNumberofSQLServerInstancesConnected
	Write-Host $log
	
	$log = $FORMAT_SUMMARY_CONNECTION_FAILED  -f $global:iNumberofSQLServerInstancesFailed
	Write-Host $log
	
}	

addCSharpObject

readInputFile $fileIn $collectionSQLInstance

processQLInstance  $collectionSQLInstance



#https://blogs.technet.microsoft.com/heyscriptingguy/2015/06/07/powertip-use-powershell-to-create-new-file/
New-Item $fileOut -ItemType file  -force | Out-Null	
#New-Item $fileOutTabbed -ItemType file  -force | Out-Null	
New-Item $fileOutFailed -ItemType file  -force | Out-Null			

writeResult $collectionSQLVersion

writeResultInvalid $collectionSQLVersionInvalid

writeSummary


Source Code Repository

  1. Github

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s