SQL query to get Linked Server details

August 30, 2023September 1, 2023 SQLAutomationGuruLeave a comment

Hello and welcome! I recently received a request to retrieve linked server details from a SQL server. DBAs were performing a migration activity, and clearly linked servers had to be moved to the new version of SQL server as well.

The first step in any migration is to collect data from your old server and discuss it with the appropriate parties.
Going to SSMS and noting down each and every linked server with its properties is a time-consuming task.
This is when SQL queries come in handy.

I have given them below query which can be very helpful to you.

    SELECT @@servername as [Server Name]
    ,ss.server_id
    ,ss.name
    ,'Server' = case ss.server_id
                     when 0 then 'Current Server'
                     else 'Remote Server'
                     end
    ,ss.product
    ,ss.provider
    ,ss.catalog
    ,'Local Login' = case sl.uses_self_credential
                             when 1 then 'Uses Self Credential'
                             else ssp.name
                             end
    ,'Remote Login Name' = sl.remote_name
    ,'RPC Out Enabled' = case ss.is_rpc_out_enabled
                              when 1 then 'True'
                              else 'False'
                              end
    ,'Data Access Enabled' = case ss.is_data_access_enabled
                                  when 1 then 'True'
                                  else 'False'
                                  end
    ,ss.modify_date
     FROM sys.servers ss
     LEFT JOIN sys.linked_logins sl
     ON ss.server_id = sl.server_id
     LEFT JOIN sys.server_principals ssp
     ON ssp.principal_id = sl.local_principal_id

Below is the example of output of this command.

Let me know in comments if it is helpful to you.
Happy Learning!!

Powershell script for multiple servers

August 29, 2023September 1, 2023 SQLAutomationGuru1 Comment

In the previous article, we saw how to connect to a SQL server and retrieve information using PowerShell.
Consider the case when you have hundreds of servers and wish to retrieve data from all of them. Will you manually perform the query against each server? Nope! All you have to do is create a basic loop for each server and run your query. It will connect to each server and collect data automatically.

Let’s start.
First, let’s have a look how a loop works in the Powershell. Here we will use ‘foreach’ loop.

Foreach (placeholder variable IN the collection)
{
Code for what you want to do in the collection
}

Now, if you have hundred servers, put them into a text file and read into a powershell variable.
$servers = get-content -Path “C:\temp\Serverlist.txt”

Now you have to put a foreach loop to access each server you stored in $servers

foreach ($server in $servers)
{
Your code
}

Here is your code:

<#
   Powershell script to connect multiple servers
#>

#Read the server list
$servers = get-content -Path "c:\temp\Serverlist.txt"

foreach($server in $servers)
{
   #Create a connection to the server
   $connectionstring = "Data Source"="+"$server"+";Initial Catalog=master;Integrated Security=SSPI;"
   $connection = New-Object System.Data.SqlClient.SqlConnection
   $connection.ConnectionString = $connectionstring

   #Create a command
   $command = New-Object System.Data.SqlClient.SqlCommand
   $command.CommandText = "Select * FROM sys.databases"

   #Assign the command to the connection
   $command.Connection = #Connection

   #Create an adapter
   $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
   $SqlAdapter.SelectCommand = $command

   #Create your dataset and fill the result
   $DataSet = New-Object System.Data.DataSet
   $SqlAdapter.Fill($DataSet)
   $DataSet.Tables[0] | Export-csv "C:\temp\output.csv" -append -Force -NoTypeInformation #Output your 
   data to a csv file
}

You have observed in the Export-csv command we have added -append in the end. This will append your data into the csv file retrieved from each server.

-NoTypeInformation property will not put default csv information at the top of your csv file.

Try this script and see if it’s helpful to you. Happy Learning!

My First Powershell Script

August 26, 2023September 1, 2023 SQLAutomationGuruLeave a comment

Hello there, As a DBA, you must do numerous checks on your servers on a daily basis. It could involve things like monitoring disk space, running a health check, checking the status of services, and other minor details.
Instead of logging into each server to acquire the details, don’t you think you should have some type of automation script to get those details for you, and if you can execute them on a regular basis via task scheduler or a job on a daily basis, it will help you a lot?
And this is where Powershell automation comes in helpful.

So here is your first Powershell script to connect to your server and get the data.

<#
   My First Powershell script
#>

#Assign a server name to the $server variable
$server = 'servername'

#Create a connection to the server
$connectionstring = "Data Source"="+"$server"+";Initial Catalog=master;Integrated Security=SSPI;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionstring

#Create a command
$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = "Select * FROM sys.databases"

#Assign the command to the connection
$command.Connection = #Connection

#Create an adapter
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command

#Create your dataset and fill the result
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | Export-csv "C:\temp\output.csv"  #Output your data to a csv file
#or
$DataSet.Tables[0] | Out-GridView   #another way to see the output

This script will return the database names from the SQL server. Just change the select query and you will get the desired output from the server.

This is how the script looks in the editor.