Building SQL Server Inventory - Professional SQL Server 2008 Administration with Windows PowerShell - page 361

After installing a SQL Server instance, we need a script, Upsert-Server.ps1 , to insert the SQL server
record into the inventory. This script calls the uspUpsertServers stored procedure. For a SQL Server
instance on a standalone host, we pass a host name. For a SQL Server instance on a cluster, we pass a clus-
ter name. This script connects to the HKLM registry hive of the SQL Server host/cluster and retrieves the
TCP/IP port number on which the instance is listening. Then it connects with the SQL Server instance
with the port number, and retrieves the other information such as version, edition, and server config-
urations. If we run the script against the same server for the second time, the script will update the
information for the existing server record. The usage of this script is as follows:

Upsert-Server -instanceName <string[]> <<-hostName <string[]>|-clusterName
<string[]>> -status <string[]>

The complete script, which is available for download from the Wrox website for this topic, is as follows:

# ===========================================================
#
# 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
}

}