Powershell script to find SQL Server instances on remote servers

This is regarding finding the SQL Server database services/instances on remote computers, not any other SQL Server components like SSRS, SSIS, Full-Text services etc. maybe installed there...

But, first, if you are just interested in looking up sql server services on a single remote computer, you can simply use this PowerShell one liner:

Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'" -ComputerName "SQLSERVERVM1"

This is actually part 2 of a process I am creating to automatically discover SQL Server instances in an Active Directory domain, more specifically, new SQL Server  instances added to any existing server or on newly installed servers.  The process is supposed to be for organizations with very large number of computers where you don't want to scan the entire AD everyday to see if any new SQL Servers are installed.

The idea is to:

.

• Get list of all servers in the AD and store the results in to a SQL table
• Get servers with SQL Server installed and store the results into a SQL table
• Compare the list with the previous list and send a report to DBAs

You can find the part 1 of this blog series at the following link:

https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-new-servers.html

I will be using the CSV file (new_servers.csv) generated by the powershell script mentioned in the above post. You can also create your own text/CSV file with list of servers in it, with Name as the name of the first column, it could be the only column in it.

In below powershell script all I am doing is to check if the remote servers have sql server instance winodws services setup and their current status. I am not checking yet whether I have access to them or what version of sql servers these instances are running. That will be in the next post in this series!

Additionally, in this post I am also inserting the collected information into a sql staging table.

---

The powershell script to find SQL Server instances on remote servers:

<#

This powershell script uses WMI to connect to the each server and 

check windows services that matches %%sqlservr.exe%% pattern.
Therefore in order for this to work you would need to have access 

to the servers otherwise it will throw Access Denied errors. 
However since I am getting the list of servers to check from a CSV, 

it will continue on to the next server after the errors.

At the end it displays list of servers it successfully connected 

to and a separate list where it errored out.

It also exports the list of sql instances it discovered to a CSV file.

By default it uses the connected users credentials.
Though, there is option ($user variable) to specify a different 

credentials (Windows).  The password field is in plain text so 

I am not a big fan of it.

#>
(Get-Date).ToString() + ": Begin" 
try
{

        $user = ""           # Should be in Domain\UserName format
        $pass = ""
        

        if ($user -eq "") { $user = $Null}


        # If user/pass pair is provided, authenticate it against the domain
        if ($user-ne $Null)
        {
            "Authenticating user $user against AD domain"
            $domain = $user.Split("{\}")[0] 
            $domainObj = "LDAP://" + (Get-ADDomain $domain).DNSRoot 
            $domainObj
            
            $domainBind = New-Object System.DirectoryServices.DirectoryEntry($domainObj,$user,$pass)
            $domainDN = $domainBind.distinguishedName 
            "domain DN: " + $domainDN
            
            # Abort completely if the user authentication failed for some reason
            If ($domainDN -eq $Null) 
               {
                       "Please check the password and ensure the user exists and is enabled in domain: $domain"
                       throw "Error authenticating the user: $user"
                       exit
               }
            else {"The account $user successfully authenticated against the domain: $domain"}

            $passWord = ConvertTo-SecureString -String $pass -AsPlainText -Force
            $credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $user, $passWord
        }

        $csv_file_name = "new_servers.csv"
        $CSVData = Import-CSV $csv_file_name
        $export_file_name = "sql_server_instances.csv"
        $csv_row_count = $CSVData.Count
        (Get-Date).ToString() + ": Total rows in the CSV file: " + $csv_row_count
        $servers = $CSVData.DNSHostName

        $SqlInstancesList = @()
        $ErrorServers = @()
        ""
        $servers
        ""
        
        # iterate through each server and search for sql services on them

        foreach($server in $servers) 

        { 
        try
        {

                if ($user-ne $Null)
                   {$SqlServices = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -credential $credentials  -ComputerName $server -ErrorAction Continue}
                else
                   {$SqlServices = Get-WmiObject -Query "select * from win32_service where PathName like '%%sqlservr.exe%%'"  -ComputerName $server -ErrorAction Continue}
                
                $SqlInstancesList += $SqlServices
        }
        catch
        {
                # even though error occured, it will continue to the next server
                "Error when looking up SQL Instances on: " + $server 
                $ErrorServers += $server + " (" + $_.Exception.Message + ")"
                throw
        }
        } 

        # if there were any errors with any of the servers, print off names of those servers along with the error message/reason
        if ($ErrorServers.Count -gt 0)
        {
                "Error when looking up SQL Instances on following servers:"
                "--------------------------------------------------------"
                $ErrorServers
        }
        ""
        "SQL Instances Found:"
        "-------------------"
        $SqlInstancesList | select-object -Property PSComputerName, @{n="SqlInstance";e={$_.Name -replace "MSSQL\$", ""}}, Name, ProcessID, StartMode, State, Status, ExitCode, PathName | Export-CSV $export_file_name -NoTypeInformation -Encoding UTF8
        Import-Csv -Encoding UTF8 -Path $export_file_name | ft -AutoSize
        (Get-Date).ToString() + ": Complete" 
}
 
Catch
{
    (Get-Date).ToString() + ": Error Occurred" 
     throw  
}

PowerShell script to load the collected information into sql table:

<#
This too uses the connected users credentials to connect to 
sql server instance.

Since I am loading data into a staging table, this first 
truncates that table then loads the data into it.
#>
(Get-Date).ToString() + ": Begin Loading data into sql staging table" 

$sql_instance_name = 'mssqlservervm\SQL2016AG01' 
$db_name = 'AdminDBA'
$destination_table_name = "dbo.sql_server_instances_stage"
$export_file_name = "sql_server_instances.csv"

$truncate_table_command = "truncate table " + $destination_table_name
"Truncate table command: " + $truncate_table_command
invoke-sqlcmd -Database $db_name -Query $truncate_table_command -serverinstance $sql_instance_name

$SqlServices = Import-Csv -Encoding UTF8 -Path $export_file_name
foreach ($sqlservice in $SqlServices)
{
                    $PSComputerName      = $SqlService.PSComputerName
                    $Name                = $SqlService.Name
                    $SqlInstance         = $SqlService.SqlInstance
                    $PathName            = $SqlService.PathName
                    $ExitCode            = $SqlService.ExitCode
                    $ProcessID           = $SqlService.ProcessID
                    $StartMode           = $SqlService.StartMode
                    $State               = $SqlService.State
                    $Status              = $SqlService.Status

$query = "INSERT INTO " + $destination_table_name + " (PSComputerName,ServiceName, InstanceName,PathName,ExitCode,ProcessID,StartMode,State,Status)
          VALUES ('$PSComputerName','$Name','$SqlInstance','$PathName','$ExitCode','$ProcessID','$StartMode','$State','$Status')" 

$execute_query = invoke-sqlcmd -Database $db_name -Query $query  -serverinstance $sql_instance_name
}

$query = "select count(*) rcount from " + $destination_table_name 
$rcount = invoke-sqlcmd -Database $db_name -Query $query -serverinstance $sql_instance_name -As DataTables
"Number of records inserted into sql table: " + $rcount[0].rcount
(Get-Date).ToString() + ": Complete Loading data into sql staging table" 

SQL Script to create the dbo.sql_server_instances_stage table:

USE [AdminDBA]
GO
if object_id('sql_server_instances_stage', 'U') is not null
drop table [sql_server_instances_stage]
GO

CREATE TABLE [sql_server_instances_stage](
 [id] [int] IDENTITY(1,1) PRIMARY KEY,
 [PSComputerName] [varchar](500) NULL,
 [ServiceName] [varchar](500) NULL,
 [InstanceName] [varchar](500) NULL,
 [PathName] [varchar](1500) NULL,
 [ExitCode] [int] NULL,
 [ProcessID] [int] NULL,
 [StartMode] [varchar](500) NULL,
 [State] [varchar](500) NULL,
 [Status] [varchar](500) NULL,
 [InsertedDate] [datetime] NULL DEFAULT GETDATE()
)

Find clustered index on non primary key columns

By default when a primary key constrained is created on a table/view,  SQL Server automatically creates a unique clustered in order to enforce it.  And since a table can only have one clustered index, all the subsequent or any previous indexes created before that are created as a non-clustered index.

That works best in most cases and is the recommended best practice.

And decision to have clustered index on what columns affects everything about everyday working of an application. And also as a general best practice every table should have a clustered index, but its not required and there are cases where its best not to.

Scenario:

So now imagine a scenario where a table has the PK but the clustered index is on non PK columns. I am going to assume that there must be well thought-out index strategy for that particular table at the design time.

But over time the usage patterns may evolve and/or through endless enhancements, bug fixes etc. now that index may not be optimal. Of course that could be true for any index but the consequences are more severe if that's the case for a clustered index.

Now you are supporting that database in production mode. Users report that the query performance has gotten extremely slow and you also notice that the index optimization job is taking much longer to complete.

As part of your research and troubleshooting this issue, one of things you decide to check is index strategy already in place and you check 1) Are there any missing indexes 2) are the indexes of correct type (unique, clustered, non-clustered etc.), fill factor etc. 3) whether the clustered index is created on right columns etc....

The query that I have below is to find out if clustered index is on non-pk columns. I have consciously decided to exclude tables that have either no clustered index, no primary key or there is clustered as well as non-clustered index created on primary key columns.

-- CREATE A TEST TABLE
IF OBJECT_ID('dbo.tbl_test_ci_on_non_pk', 'U') IS NOT NULL
   DROP TABLE tbl_test_ci_on_non_pk
GO

-- ADD A CLUSTERED INDEX ON A NON-PK COLUMN
CREATE TABLE [dbo].[tbl_test_ci_on_non_pk](
	[id] [int] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
	[name] [varchar](50) NULL)
GO
CREATE CLUSTERED INDEX [idx_ci_tbl_test_ci_on_non_pk_name] 
ON [dbo].[tbl_test_ci_on_non_pk]
([name] ASC)
GO

;WITH cte_indexes

     AS (SELECT db_name()                         db_name, 
                schema_name(o.schema_id)          schema_name, 
                object_name(i.object_id)          object_name, 
                o.type_desc                       object_type, 
                i.NAME                            index_name, 
                i.type_desc                       index_type, 
                i.is_primary_key, 
                o.object_id                       object_id, 

                pk_index_id   = (SELECT index_id FROM   sys.indexes c WHERE  c.object_id = o.object_id AND c.is_primary_key = 1),
                pk_index_name = (SELECT name FROM   sys.indexes c WHERE  c.object_id = o.object_id AND c.is_primary_key = 1),
                clustered_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max)) 
                                                                FROM   sys.index_columns ic 
        INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id 
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
        WHERE  ( ic.object_id = o.object_id AND ic.index_id = i.index_id) 
        FOR xml path ('')), 1, 1, '') ), ''),

               pk_index_columns = COALESCE(( stuff((SELECT cast(',' + c.name AS VARCHAR(max)) 
                                                                FROM   sys.index_columns ic 
        INNER JOIN sys.indexes ii ON ii.object_id = ic.object_id AND ii.index_id = ic.index_id 
        INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id 
        WHERE  ( ic.object_id = o.object_id AND ii.is_primary_key = 1) 
        FOR xml path ('')), 1, 1, '') ), '')

         FROM   sys.objects o 
                INNER JOIN sys.indexes i ON o.object_id = i.object_id 
         WHERE  Objectproperty(o.object_id, 'ismsshipped') = 0) 

 
SELECT db_name, 
       schema_name, 
       object_type, 
       object_name,
       index_name  non_pk_clustered_index_name,
       pk_index_name,
       clustered_index_columns,
       pk_index_columns

FROM   cte_indexes 
WHERE  1 = 1 
       AND index_type = 'CLUSTERED' 
       AND pk_index_id ! = 1 
       AND clustered_index_columns != pk_index_columns
ORDER  BY object_name, 
          index_name 

Caveat:  I only considered the traditional index types (clustered, non-clustered, unique, non-unique etc.).

I have tested this on SQL Server versions 2008 R2 and above.

What about orphaned windows users?

I should start off by mentioning that this post is applicable to sql server versions 2012 and up. If you have an older version of sql server, the solution discussed here will not work.

We are generally aware that a user in a database is orphaned when it does not have a matching SID record in the sys.server_principals table.   This is not an issue if your databases is CONTAINED and uses database authentication. Otherwise, the user will not be able to login into the sql server instance and as a result cannot access the database even though the user has access to the database.

Generally, you will get orphaned database users after restoring a database to a different server and one or more users in the database do not have corresponding LOGIN at the instance level or has mismatched SID.  Another possibility is that the login got deleted from sys.server_principals or from the Active Directory or local machine. I am sure there are other possible situations.

Microsoft has been obviously aware of the situation for a long time and has provided a stored procedure sp_change_users_login to find and optionally fix orphaned database users. Check out this Microsoft artcile for more information:

How To Troubleshoot Orphaned Users in SQL Server

But that solution only works for sql server logins. In other words, it does not support windows users.

What further complicates windows users is that the user may have access to sql server through membership in a windows group.  So just comparing SID column between sys.database_principals and sys.server_principals will not give you accurate results. That is where extended stored procedure xp_logininfo is helpful.

So what I have below is a T-SQL script to find and optionally fix the orphaned windows users. It script utilizes the extended stored procedure xp_logininfo to bridge the gap left by traditional methods.

DECLARE @username NVARCHAR(500) 

DECLARE @privilege NVARCHAR(500) 
DECLARE @sql NVARCHAR(4000) 
DECLARE @fix_orphaned_user BIT 
DECLARE @cnt INT 

SET @fix_orphaned_user = 0  -- set this to 1 to also fix the orphaned user

DECLARE c1 CURSOR FOR 
  SELECT dp.NAME 
  FROM   sys.database_principals dp 
         LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid 
  WHERE  dp.type_desc = 'WINDOWS_USER' 
         AND dp.authentication_type_desc = 'WINDOWS'
         AND dp.principal_id != 1 
         AND sp.sid IS NULL 

OPEN c1 

FETCH c1 INTO @username 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
      SET @cnt = Isnull(@cnt, 0) + 1 

      EXEC xp_logininfo @acctname = @username, @option = 'all', @privilege = @privilege output 

      IF @privilege IS NULL 
        RAISERROR('Orphaned windows user name: %s',10,1,@username) 

      IF @fix_orphaned_user = 1 
        BEGIN 
            SET @sql = 'CREATE LOGIN [' + @username + '] FROM WINDOWS WITH DEFAULT_DATABASE = [' + DB_NAME() + ']' 
            PRINT @sql 
            EXEC(@sql) 
        END 

      FETCH c1 INTO @username 
  END 
  
CLOSE c1 
DEALLOCATE c1 

IF @cnt IS NULL 
  RAISERROR('No orphaned windows users found',10,1) 

Report only:

Report and fix:

Download Script: Find-Orphan-Windows-Users-In-SQLServer

Caveat: If windows user is deleted, disabled, locked out etc. at the OS level, this script will not attempt to fix that issue.

I hope this works not only on windows but also on Linux but I have not tested it there.

Query machine name of the sql server instance - the hard or the harder way

I was at a client where they are using non-Microsoft clustering technology to achieve the high availability of SQL Server instances. This was partly because of legacy reasons and partly because it supports clustering across all major hardware, operating systems, and applications, including SQL Server. SQL Server instances are set up in either 2 or 3 nodes, active/passive, active/active, etc., configurations. There are approximately 30 physical servers hosting SQL Server instances. Yes, the client is going to move all the SQL workloads to Always On Clusters, but the process has been slow because all the databases are used for COTS/third-party applications.

A virtual name is used to make a connection to a SQL Server instance. Often, I would need to know the actual physical node name where a particular SQL instance is active, and I needed to find it out programmatically.

You may have different reasons for connecting to SQL Server using a virtual name but need to know the underlying machine name.

So I first tried this query:

SELECT   

  @@SERVERNAME ServerName_Global_Variable
 ,SERVERPROPERTY('ServerName') ServerName
 ,SERVERPROPERTY('InstanceName') InstanceName
 ,SERVERPROPERTY('MachineName') MachineName
 ,SERVERPROPERTY('ComputerNamePhysicalNetBIOS') NetBIOS

That would be the easier way if it worked in this situation. Alas, it still kept giving me virtual server name.

Then I tried using a DOS command, assuming you have or are able to temporarily turn on XP_CMDSHELL.

EXEC master..XP_CMDSHELL 'Hostname'

And I still got the virtual server name.

Then I tried the following which does display the actual server name in one of the messages:

EXEC master..xp_cmdshell 'net user'

Mission accomplished, great! I could write additional code around it to trim out every other text from it, extracting only the computer name, and then store it in a variable or in my own metadata table for further processing, reporting, etc.

Then I thought, is there another way, perhaps a better way? Well, I wouldn't necessarily call my next approach better, but it's certainly another way.

If you're looking for a one-liner to remotely look up a computer name, simply run the following command from the command prompt or PowerShell:

wmic /NODE:sqlservernode1 computersystem get Name

Here is a bit lengthy TSQL code to do this while connected to a SQL instance. Note that if the XP_CMDSHELL is disabled,  it temporarily turns it on then off when done.

SET NOCOUNT ON

DECLARE @computer_name VARCHAR(500)
DECLARE @xp_cmdshell_status BIT

SELECT @xp_cmdshell_status = cast(value_in_use AS BIT)
FROM sys.configurations
WHERE name = 'xp_cmdshell'

IF @xp_cmdshell_status = 0
BEGIN
 PRINT 'XP_CMDSHELL is disabled on this server, temporarily enabling it...'

 EXEC sp_configure 'show advanced options','1'

 RECONFIGURE

 EXEC sp_configure 'xp_cmdshell','1'

 RECONFIGURE
END

IF object_id('tempdb..#t1_xp_cmdshell_output') IS NOT NULL
 DROP TABLE #t1_xp_cmdshell_output

CREATE TABLE #t1_xp_cmdshell_output (
 id INT identity
 ,txt NVARCHAR(2000)
 )

INSERT INTO #t1_xp_cmdshell_output
EXEC master..xp_cmdshell 'wmic computersystem get Name'

SELECT @computer_name = txt FROM #t1_xp_cmdshell_output WHERE id = 2

SELECT @computer_name computer_name

IF @xp_cmdshell_status = 0
BEGIN
 PRINT 'Changing XP_CMDSHELL back to disabled state...'

 EXEC sp_configure 'show advanced options','1'

 RECONFIGURE

 EXEC sp_configure 'xp_cmdshell','0'

 EXEC sp_configure 'show advanced options','0'

 RECONFIGURE
END

Quite a lengthy code!

Generally, when I'm using a DOS or PowerShell command, I prefer using PowerShell to populate the data in the SQL table, avoiding all the additional code I've used in the above T-SQL block.

And finally, there is one more way, sort of! If you execute a multi-server query with the "Add server name to the results" option set to true (default), it will display the physical server name in the results pane. However, I'm not aware of a way to capture it dynamically in a variable at this point.

If you think I missed something, please don't hesitate to provide feedback!

Powershell script to find new servers in an AD domain

This post is part of a process I'm developing to automatically discover SQL Server instances within an Active Directory domain. Expect a series of several related posts.

You might wonder if I'm reinventing the wheel. In many cases, you'd be right. However, as a consultant and visiting DBA, I have valid reasons for this approach. Luckily, I already possess the necessary scripts, so this endeavor mainly revolves around automating the entire process.

This is part one of the series. It identifies new servers added to AD. At this point, we can't ascertain if any of these servers are SQL Servers. That topic will be addressed in the subsequent blog post at:

https://sqlpal.blogspot.com/2019/06/powershell-script-to-find-sql-server.html

---

The script below, written in PowerShell, displays the results of the discovery on the console (for up to 100 servers). It also exports the findings to a CSV file named 'new_servers.csv'. Feel free to modify or comment out any part as you see fit.

Before executing this script, kindly review and modify the default values for the variables as necessary.

<#

You can use this to get list of all servers in an AD domain or
new servers added in last X days, or any other properties
you want to filter the results on.

You should not need to be a domain admin or 
need any special permission in the AD.
This might change in the future versions though.


You will need powershell active directory module installed 
on the computer where you are running this script from.

If you are using a Windows 10 machine like I am right now, 
here is a good resource to get the AD module installed.
https://gallery.technet.microsoft.com/Install-the-Active-fd32e541


#>
try
{

# filter by servers added in last n days
# or set this to 0 for all servers
$days_to_search = 30

# if searching in different domain than your current domain, 
# specifiy the domain name between the double quotes
$domain = ""                

if ($domain -eq "")
{
        $domain = Get-ADDomain 
}
else
{
        $domain = Get-ADDomain -Identity $domain
}


$domain_name = $domain.name
$distinguished_name = $domain.DistinguishedName
$domain_controller = (Get-ADDomainController -server $domain_name).HostName

$search_base = "OU=SERVERS," + $distinguished_name
$export_file_name = $env:USERPROFILE + "\Documents" + "\new_servers.csv"


# convert $days_to_search to a negative value
if($days_to_search -lt 0) {$days_to_search = -$days_to_search}


[String](Get-Date) + ": Begin searching for new servers in the AD domain"
"-------------------------------------------------------"

if($days_to_search -lt 0)
{
   $date_filter = (get-date).adddays($days_to_search)
   "Date filter value: " + $date_filter
   $search_filter = {Created -gt $date_filter -and operatingsystem -like "Windows Server*"}
   "Find new computers added in last " + $days_to_search + " days to AD domain (" + $domain_name + ")"

}
else
{
   $search_filter = {operatingsystem -like "Windows Server*"}
   "Find all servers in AD domain (" + $domain_name + ")"
}
"Search Base: $search_base"
"Domain controller: " + $domain_controller


$computers = @()
$computers += (get-adcomputer -SearchBase  $search_base -Properties * -Filter $search_filter -server $domain_controller)

[String](Get-Date) + ": Total Number of Servers Found: " + $Computers.Count

# Display the results on the console 
"Displaying first 100 results...."
$computers | Select-Object Name, 
                           Created, 
                           IPv4Address,
                           OperatingSystem,
                           OperatingSystemVersion  -First 100 | ft -AutoSize


# Exports results to a CSV file
[String](Get-Date) + ": Exporting results to ($export_file_name)...."
$computers | Select-Object Name, 
                           Created, 
                           DNSHostName,
                           IPv4Address,
                           OperatingSystem,
                           OperatingSystemHotfix,
                           OperatingSystemServicePack,
                           OperatingSystemVersion, 
                           IPv6Address,
                           DistinguishedName, 
                           createTimeStamp, 
                           Description | Export-CSV `
                                         $export_file_name -NoTypeInformation `
                                                           -Encoding UTF8


[String](Get-Date) + ": End searching for new computers"

}
Catch
{
    [String](Get-Date) + ": Error occurred"
    throw
   
}

Download this PowreShell script From GitHub

Caveats:

This script is specifically tailored for organizations where all servers are registered in Active Directory (AD). If there are new servers set up as non-AD, standalone units within private DMZs, this script will not detect them.

Additionally, the script operates under the assumption that all servers, SQL Servers included, are registered under the 'SERVERS' Organizational Unit (OU) in AD. Should your organization utilize a different OU, or if you wish to scan all OUs (which might include computers running non-server Windows editions), you'll need to adjust the $search_base variable.

At its current configuration, the script searches one domain at a time. By default, it's set to your current domain. However, it can be adjusted to target any domain within the AD forest, given you have access to it or if there's a trust relationship established between your authentication domain and the target domain. Further development can enhance this script to scan all domains in an AD forest.