September 7, 2013 at 5:48 am
Hi everyone.
Hope someone can offer some advice on my issue.
I have been learning Powershell with the aid of the SQL Server 2008 Administration with Windows Powershell book published by Wrox.
Book is good and I have learnt a lot from it.
The book covers SQL 2000, SQL 2005 and SQL 2008.
Up to a stage now where my inventory also includes SQL 2008 R2 and SQL 2012.
I don't think the scripts are configured to capture the correct parameters for capturing SQL 2008 R2 and SQL 2012 details.
I have included the full script.
Could any one offer me any advice on how to the scripts should be changed to capture SQL 2008R2 and SQL 2012 configs.
(and if possible and explanation on what was changed so I can learn from it?)
Full script:
# ======================================================================================================================
#
# NAME: Upsert-Server.ps1
#
# AUTHOR: Yan and MAK
# DATE : 6/8/2008
#
# COMMENT: This script adds a server record into inventory.
#If the server already exists, then this script updates the existing server record.
# Example: Upsert-Server -instanceName MSSQLSERVER -hostName POWERPC -status U
# =======================================================================================================================
##############################################################################
# Initialize parameters
##############################################################################
param (
[switch]$help,
[string]$instanceName = {},# Name of the SQL Server instance to add into inventory. For a default instance, it is MSSQLSERVER.
[string]$hostName = {},# Name of the SQL Server host.
[string]$clusterName = {},# Name of the SQL Server cluster.
[string]$status = {}# Status of the SQL Server instance. Possible values include D, Q, P, U and R.
)
# This function connects with a SQL Server instance [$pHostName\$pInstanceName,$pTcpPort] to execute a SQL query $pSql.
function execSQL([String] $pHostName, [String] $pInstanceName, [String] $pSql, [String] $pTcpPort)
{
if ( $pInstanceName -eq 'MSSQLSERVER' ) {
(Invoke-Sqlcmd -Query "$pSql" -ServerInstance "$pHostName,$pTcpPort" -Database master).Column1
}
else {
(Invoke-Sqlcmd -Query "$pSql" -ServerInstance "$pHostName\$pInstanceName,$pTcpPort" -Database master).Column1
}
}
# This function returns the statements that can be used to get a server configuration in a column called Column1.
function getConfigSql([String] $option)
{
$strSql = "CREATE TABLE #temp (name nvarchar(35), minimum int, maximum int, config_value int, run_value int)`n"
$strSql = $strSql + "INSERT INTO #temp EXEC (`'exec sp_configure `'`'$option`'`'`')`n"
$strSql = $strSql + "SELECT run_value as Column1 from #temp`n"
$strSql = $strSql + "DROP TABLE #temp`n"
return $strSql
}
# This function connects to the HKLM registry hive of the SQL Server host $pHostName
# and retrieve the TCP/IP port number that the instance $pInstanceName is listening on.
function getTcpPort([String] $pHostName, [String] $pInstanceName)
{
$strTcpPort=""
$reg = [WMIClass]"\\$pHostName\root\default:stdRegProv"
$HKEY_LOCAL_MACHINE = 2147483650
# Default instance
if ($pInstanceName -eq 'MSSQLSERVER') {
#SQL Server 2000 or SQL Server 2005/2008 resides on the same server as SQL Server 2000
$strKeyPath = "SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp"
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
if ($strTcpPort) {
Set-Variable -Name instanceRegPath -Value "SOFTWARE\Microsoft\MSSQLServer\MSSQLServer" -Scope 1
return $strTcpPort
}
}
else {
#SQL Server 2000 or SQL Server 2005/2008 resides on the same server as SQL Server 2000
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\$pInstanceName\MSSQLServer\SuperSocketNetLib\Tcp"
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
if ($strTcpPort) {
Set-Variable -Name instanceRegPath -Value "SOFTWARE\Microsoft\Microsoft SQL Server\$pInstanceName\MSSQLServer" -Scope 1
return $strTcpPort
}
}
#SQL Server 2005
for ($i=1; $i -le 50; $i++) {
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i"
$strInstanceName=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"").svalue
if ($strInstanceName -eq $pInstanceName) {
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i\MSSQLServer\SuperSocketNetLib\tcp\IPAll"
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
Set-Variable -Name instanceRegPath -Value "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i\MSSQLServer" -Scope 1
return $strTcpPort
}
}
#SQL Server 2008
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.$pInstanceName\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"
$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
if ($strTcpPort) {
Set-Variable -Name instanceRegPath -Value "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.$pInstanceName\MSSQLServer" -Scope 1
return $strTcpPort
}
return ""
}
# This function connects to the HKLM registry hive of the SQL Server host $pHostName
# and retrieve the network protocols used by the instance $pInstanceName.
function getServerNetWorkProtocols([String] $pHostName, [String] $pInstanceName)
{
$strProtocols=""
$reg = [WMIClass]"\\$pHostName\root\default:stdRegProv"
$HKEY_LOCAL_MACHINE = 2147483650
$strKeyPath = "$instanceRegPath\SuperSocketNetLib"
#SQL Server 2000
$arrValues=$reg.GetMultiStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"ProtocolList").sValue
if ($arrValues) {
$arrValues | foreach -process { $strProtocols=$strProtocols + $_ + ',' }
return $strProtocols.Substring(0, $strProtocols.Length-1)
}
#SQL Server 2005 or 2008
else {
$strKeyPath = "$instanceRegPath\SuperSocketNetLib\Tcp"
$intEnabled=$reg.GetDWORDValue($HKEY_LOCAL_MACHINE,$strKeyPath,"Enabled").uvalue
if ($intEnabled) {
if ($intEnabled -eq 1) { $strProtocols='tcp,' }
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$instanceNo\MSSQLServer\SuperSocketNetLib\Np"
$intEnabled=$reg.GetDWORDValue($HKEY_LOCAL_MACHINE,$strKeyPath,"Enabled").uvalue
if ($intEnabled -eq 1) { $strProtocols=$strProtocols + 'np,' }
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$instanceNo\MSSQLServer\SuperSocketNetLib\Sm"
$intEnabled=$reg.GetDWORDValue($HKEY_LOCAL_MACHINE,$strKeyPath,"Enabled").uvalue
if ($intEnabled -eq 1) { $strProtocols=$strProtocols + 'sm,' }
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$instanceNo\MSSQLServer\SuperSocketNetLib\Via"
$intEnabled=$reg.GetDWORDValue($HKEY_LOCAL_MACHINE,$strKeyPath,"Enabled").uvalue
if ($intEnabled -eq 1) { $strProtocols=$strProtocols + 'via,' }
return $strProtocols.Substring(0, $strProtocols.Length-1)
}
}
}
# This function connects to the HKLM registry hive of the SQL Server host $pHostName
# and retrieve the startup parameters used by the instance $pInstanceName.
function getStartupParameters([String] $pHostName, [String] $pInstanceName)
{
$reg = [WMIClass]"\\$pHostName\root\default:stdRegProv"
$HKEY_LOCAL_MACHINE = 2147483650
$strKeyPath = "$instanceRegPath\Parameters"
$arrValues=$reg.EnumValues($HKEY_LOCAL_MACHINE,$strKeyPath).sNames
#SQL Server 2000
if ($arrValues) {
for ($i=0; $i -lt $arrValues.Length; $i++) {
$strParameters=$strParameters + $reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,$arrValues[$i]).svalue + ";"
}
return $strParameters
}
#SQL Server 2005
for ($i=1; $i -le 50; $i++) {
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i"
$strInstanceName=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"").svalue
if ($strInstanceName -eq $pInstanceName) {
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i\MSSQLServer\Parameters"
$arrValues=$reg.EnumValues($HKEY_LOCAL_MACHINE,$strKeyPath).sNames
if ($arrValues) {
for ($i=0; $i -lt $arrValues.Length; $i++) {
$strParameters=$strParameters + $reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,$arrValues[$i]).svalue + ";"
}
return $strParameters
}
}
}
#SQL Server 2008
$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.$pInstanceName\MSSQLServer\Parameters"
$arrValues=$reg.EnumValues($HKEY_LOCAL_MACHINE,$strKeyPath).sNames
if ($arrValues) {
for ($i=0; $i -lt $arrValues.Length; $i++) {
$strParameters=$strParameters + $reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,$arrValues[$i]).svalue + ";"
}
return $strParameters
}
}
##############################################################################
# Main Program
##############################################################################
[String] $strUpsertSql=""
[String] $instanceRegPath = '' # Registry path for the instance
if ( $help ) {
"Usage: Upsert-Server -serverName <string[]> <<-hostName <string[]>|-clusterName <string[]>> -status <string[]>"
exit 0
}
if ( $instanceName.Length -eq 0 ) {
"Please enter an instance name."
if ($instanceName -ieq 'mssqlserver') {
$instanceName='MSSQLSERVER'
}
exit 1
}
if (( $hostName.Length -eq 0) -and ($clusterName.Length -eq 0)) {
"Please enter a host name or a cluster name."
exit 1
}
if (( $hostName.Length -gt 0) -and ($clusterName.Length -gt 0)) {
"You only need to enter either a host name or a cluster name."
exit 1
}
if ( $status -notmatch '^D|Q|P|U|R$' ) {
"The status is invalid. Please enter D, Q, P, U or R."
exit 1
}
[String] $sqlNetworkName="" # For standalone host, it is the same as $hostName. For cluster, it is the same as $clusterName
[String] $windowsNetworkName="" # For standalone host, it is the same as $hostName. For cluster, it is the WindowsClusterName from the Clusters table.
if ($hostName.Length -gt 0) {
$sqlNetworkName=$hostName
$windowsNetworkName=$hostName
}
else {
$sqlNetworkName=$clusterName
# Find the Windows Cluster Name
$strQuerySql="SELECT WindowsClusterName FROM Clusters WHERE SQLClusterName='$clusterName'"
$sqlCluster=Invoke-Sqlcmd -Query $strQuerySql -ServerInstance $inventoryServer -Database $inventoryDatabase
$windowsNetworkName=$sqlCluster.WindowsClusterName
}
$tcpPort=(getTcpPort $windowsNetworkName $instanceName)
# If tcpPort is not available, the server or the host doesn't exist.
if ($tcpPort -eq "") {
"Tcp port is not found. Please check the server name and the host/cluster name."
exit 2
}
if ($hostName.Length -gt 0) {
$strUpsertSql = $strUpsertSql + "exec uspUpsertServers '$instanceName', '$status', '$hostName', Null, '$tcpPort', "
}
else {
$strUpsertSql = $strUpsertSql + "exec uspUpsertServers '$instanceName', '$status', Null, '$clusterName', '$tcpPort', "
}
$strUpsertSql = $strUpsertSql + "'" + (getServerNetWorkProtocols $windowsNetworkName $instanceName) + "', "
$strQuerySql = "SELECT CASE SUBSTRING(CONVERT(nvarchar, ServerProperty ('ProductVersion')), 1, CHARINDEX('.', convert(nvarchar, ServerProperty('ProductVersion')))-1 ) WHEN '10' THEN '2008' WHEN '9' THEN '2005' WHEN '8' THEN '2000' END"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + "', "
$strQuerySql = "Select ServerProperty('Edition')"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + "', "
$strQuerySql = "Select ServerProperty('ProductVersion')"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + "', "
$strQuerySql = "Select ServerProperty('ProductLevel')"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + "', "
$strParameters =(getStartupParameters $windowsNetworkName $instanceName)
$strUpsertSql = $strUpsertSql + "'" + $strParameters + "', "
$strQuerySql = "select top 1 filename as Column1 from dbo.sysfiles"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort | Split-Path -parent) + "', "
$strErrorLog=( $strParameters.Split(";") | where {$_.StartsWith("-e")} )
$strUpsertSql = $strUpsertSql + "'" + $strErrorLog.Substring(2, $strErrorLog.Length-2) + "', "
$strQuerySql = "Select ServerProperty('Collation')"
$strUpsertSql = $strUpsertSql + "'" + (execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + "', "
$strQuerySql = ( getConfigSql "min server memory" )
$strUpsertSql = $strUpsertSql + ( execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + ", "
$strQuerySql = ( getConfigSql "max server memory" )
$strUpsertSql = $strUpsertSql + ( execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + ", "
$strQuerySql = ( getConfigSql "awe enabled" )
$strUpsertSql = $strUpsertSql + ( execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + ", "
$strQuerySql = ( getConfigSql "user connections" )
$strUpsertSql = $strUpsertSql + ( execSQL $sqlNetworkName $instanceName $strQuerySql $tcpPort) + " "
$strUpsertSql = $strUpsertSql + ";"
$strUpsertSql
Invoke-Sqlcmd -Query $strUpsertSql -ServerInstance $inventoryServer -Database $inventoryDatabase
November 7, 2013 at 10:23 am
Hi MickyD
Just modify your getStartupParameters function, and it works as a charm on all versions.
####################################### Mathias FATENE : New code###########################
# This function connects to the HKLM registry hive of the SQL Server host $pHostName
# and retrieve the startup parameters used by the instance $pInstanceName.
function getStartupParameters([String] $pHostName, [String] $pInstanceName)
{
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$server =$pHostName
$SQLServices = Get-WmiObject -ComputerName $server -Class win32_service -Filter "State='Running'" | where {($_.name -like "MSSQL$*" -and $_.name -like "*") -or $_.name -eq "MSSQLSERVER"}
$wmiserver = New-Object "Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer" $server
foreach($sqlServer in $SQLServices) {
$servicename="MSSQL$"+$pInstanceName
$servicename=$servicename -replace (' ',"")
if ('MSSQL$'+"$pInstanceName" -eq $sqlServer.Name)
{
$startupParameters = $wmiserver.Services[$sqlServer.Name].StartupParameters
$startupParameters
}
}
return $startupParameters
}
####################################### Mathias FATENE ###########################
April 11, 2014 at 1:51 pm
I worked on a similar solution to collect Windows and SQL Server inventory by gathering scripts from SQL Family and released it as a free tool at http://crazydba.com. The tool uses powershell to grab data from all servers and stores it in SQL tables and can be viewed using SSRS reports. Can you please check it and give me any suggestions to improve it?
Thnx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply