My customer keeps me busy writing automation scripts for various requirements . These days I am helping them to build a centralized repository for their on-premises SQL servers . To start with , I want to collect instance names and versions of SQL server installed in their environment ( quite a few servers have multiple SQL servers installed ) . So I want to be ready with a script which can query all SQL instances in a windows server ,and yes of course will be very helpful for them if I can make the script friendly for any T-SQL queries.
Thinking of this I got few options like querying WMI , using Microsoft MAP toolkit ,querying registry etc.. And today I am going to explore the registry as I feel its more reliable than WMI in many ways ( I do not want to use MAP toolkit as I want to avoid bringing another application into our customer infrastructure and run behind the security clearance , IT approvals etc..)
# Building a PowerShell object for easy presentation and display output in a tabular format in csv
$temp = New-Object -TypeName psobject
$temp | Add-Member -MemberType NoteProperty -Name HostName -Value $null
$temp | Add-Member -MemberType NoteProperty -Name InstanceName -Value $null
$temp | Add-Member -MemberType NoteProperty -Name SQLVersion -Value $null
# list of HostNames.
$hostnames = get-content "D:\Work\PowerTest\Servers_list.txt" Step1. Loop through the host names .
foreach ($hostname in $hostnames)
{
$tempobj = $temp | Select-Object *Step2. Query the registry to find out all SQL instances configured .
#traversing registry of the remote host to identify the sql instances installed
#ensure that you have the right to access the resgistry in remote machines
$SQLInstances = Invoke-Command -ComputerName $hostname {
(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances }
foreach ($sql in $SQLInstances)
{Step3. Build a connection to each instance and execute the T-SQL .
#registry will give me the default instance name as "MSSQLSERVER" which needs to be changed for connection
if($sql -eq 'MSSQLSERVER')
{$instance = $sql.PSComputerName
$tempobj.InstanceName = $instance}
#concatenating hostname with instance name to get the correct connetion object
else { $instance = $sql.PSComputerName,$sql -join"\"
$tempobj.InstanceName = $instance}Step4. Fetch the output of queries in table format in a csv file .
$result = Invoke-Sqlcmd -ServerInstance $instance -Database master -Query "select @@version as 'Version'"
$tempobj.SQLVersion = $result.Version
$tempobj.HostName = Hostname
$tempobj| Export-Csv "D:\Work\PowerTest\instance_details.csv" -NoTypeInformation -append
}}Final Script:
# Building a PowerShell object for easy presentation and display output in a tabular format in csv
$temp = New-Object -TypeName psobject
$temp | Add-Member -MemberType NoteProperty -Name HostName -Value $null
$temp | Add-Member -MemberType NoteProperty -Name InstanceName -Value $null
$temp | Add-Member -MemberType NoteProperty -Name SQLVersion -Value $null
$hostnames = get-content "D:\Work\PowerTest\Servers_list.txt"
# looping through the host names provided
foreach ($hostname in $hostnames)
{
$tempobj = $temp | Select-Object *
#traversing registry of the remote host to identify the sql instances installed
$SQLInstances = Invoke-Command -ComputerName $hostname {
(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances }
foreach ($sql in $SQLInstances)
{
#registry will give me the default instance name as "MSSQLSERVER" which needs to be changed for connection
if($sql -eq 'MSSQLSERVER')
{$instance = $sql.PSComputerName
$tempobj.InstanceName = $instance}
#concatenating hostname with instance name to get the correct connetion object
else { $instance = $sql.PSComputerName,$sql -join"\"
$tempobj.InstanceName = $instance}
$result = Invoke-Sqlcmd -ServerInstance $instance -Database master -Query "select @@version as 'Version'"
$tempobj.SQLVersion = $result.Version
$tempobj.HostName = Hostname
$tempobj| Export-Csv "D:\Work\PowerTest\instance_details.csv" -NoTypeInformation -append
}}Notes
- I have not used any error handling mechanism as its purely on consumers need . ( perhaps you can add couple of TRY CATCH )
- You can pull a lot of information from SQL server instances , its just that I need versions . Therefore you will have to handle the output and present accordingly.
- Ensure that you have enough permission to read the registry.