Powershell script to find SQL Server instances on remote servers

This is actually part 2 of a process I am creating to automatically discover SQL Server instances in an Active Directory domain. So there will be a series of handful of posts.

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

 Part 1: Powershell script to find new servers in an AD domain

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

I will be using the CSV file generated by the powershell script mentioned in the above post.

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.

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

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

Now 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 discovere to a CSV file.

By default it uses the connected users credentials.
Though, there is option ($user variable) to specify a different credentials. 
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 + ")"
                $_
        }
        } 

        # 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" 
     $_  
}

And now the powershell script to load the collected information into sql table:

<#
This also 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
drop table [dbo].[sql_server_instances_stage]
GO

CREATE TABLE [dbo].[sql_server_instances_stage](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [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,
PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[sql_server_instances_stage] ADD  DEFAULT (getdate()) FOR [InsertedDate]
GO

Find clustered index on non primary key columns

First, some blah... blah... blah...

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.

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. But it 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 tsql script to find and optionally fix the orphaned windows users.

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:

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 party because of legacy reasons and partly because it supports clustering across all major hardware, operating system and applications including SQL Server. SQL Server instances are setup in either 2 or 3 nodes active/passive, active/active etc. configuration. There are about 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 connection to a sql server instance.  Often I would need to know actual physical node name where a particular sql instance is active and, I needed to find it out programmatically.

You may have different reason/s where you are connected 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') ComputerNamePhysicalNetBIOS

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 every other text out of it to extract only the computer name, store it in a variable and/or in my own meta data table for further processing, reporting etc...

Then I thought, is there another way, maybe a better way? Well, I would not call my next approach any better but it is another way.

If you just want a one liner to look up computer name remotely, simply run the following command from the command prompt or powershell:

wmic /NODE:sqlservernode1 computersystem get Name

Here is a bit lenghty 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, if I am using a DOS or powershell command then I use the powershell to populate the data in sql table and avoid all that other extra code that I have used in the above tsql block.

And finally, there is one more way, sorta! If you execute a multi server query with the "Add server name to the results" option set to true (default), it would display the physical server name in the results pane. Though I am 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 is actually part of a process I am creating to automatically discover SQL Server instances in an Active Directory domain. So there will be a series of handful of posts.

In case you are wondering if I am reinvesting the wheel here, you are right, in most cases. As a consultant and visiting DBA, I have good reasons to resort to this. Fortunately I already had the scripts so this is more about automating the whole process.

This is part 1 in the series. It finds new servers added to the AD. At this stage, we would not know if any of those servers are SQL Servers. That will be in my next blog!

It will display the results of discovery to the console as well as export to CSV. Please feel to comment/change anything you would like.

Before trying this script, please review and adjust the default values for the variables.

---

<#

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

You would need a normal AD account to be able to search AD. However, you don't need to be a domain admin or need any special permission in the AD.

#>

"Start: " + (Get-Date)

try

{

# 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

$logging_level = 1

$search_base = "OU=SERVERS," + $distinguished_name

$export_file_name = "new_servers.csv"

$days_to_search  = -7             # this value needs to be negative integer

if ($days_to_search -ge 0) {throw "Value for variable $days_to_search must be a negative integer in days."}

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

"-------------------------------------------------------"

$date_filter = (get-date).adddays($days_to_search)

"Date filter value: " + $date_filter

"Find new computers created in last " + $days_to_search + " days in domain " + $domain_name + " using domain controller " + $domain_controller + "....."

$computers = get-adcomputer -SearchBase  $search_base -Properties * -Filter {Created -gt $date_filter -and operatingsystem -like "*windows*"} -server $domain_controller

"Total Number of Servers Found: " + $Computers.Count

# Display the results on the console 

$computers | Select-Object Name, Created, createTimeStamp, Description,DNSHostName,DistinguishedName,IPv4Address,IPv6Address,OperatingSystem,OperatingSystemHotfix,OperatingSystemServicePack,OperatingSystemVersion | ft -AutoSize

# Exports results to a CSV file

$computers | Select-Object Name, Created, createTimeStamp, Description,DNSHostName,DistinguishedName,IPv4Address,IPv6Address,OperatingSystem,OperatingSystemHotfix,OperatingSystemServicePack,OperatingSystemVersion | Export-CSV $export_file_name -NoTypeInformation -Encoding UTF8

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

"------------------------------------------------------"

"Completed: " + (Get-Date)

}

Catch

{

    "Error occurred: " + (Get-Date)

    throw

   

}

---

Caveats:

As you can guess, this script only works if in your organization all the servers must be in AD.  If the new servers are setup as non-AD, stand alone servers in some private DMZs, this script won't find them.

Also, it assumes that all servers, including SQL Servers, are added to the SERVERS OU in AD. If your organization uses a different OU or you would like to search all OUs (including computers running non-server editions of windows), just update the $search_base variable.

Also, currently this script can only search one domain at a time, which is by default your current domain but you could also specify any domain in the AD forest as long you have access to that domain or if there is trust setup between your authentication domain and that domain.  This script can be enhanced to search all domains in an AD forest.