Using Powershell to run scripts against multiple servers

Recently I started digging up Powershell. I am amazed at how elegant and powerful it is, not to mention it’s speed !

To start, I browsed to Start > All Programs > Accessories > Windows Powershell > Windows Powershell where we get the Powershell (PS) command prompt.

You can navigate to the folder of your choice using the common “cd ” format of DOS. For example, to get to G:\Powershell_test_folder, just type : “cd G:\Powershell_test_folder”. I have saved some files in this location, hence the reason to navigate here.

Now I have saved the names of all my target SQL Servers in a text file at that location called AllServers.txt. You can simply add the names one by one. I used the “IP,port_number” format like this :

Server_1

Server_2

Server_n

Now we can add the query that we want to run against these n servers in a Query.sql file at that location. Lets say we want the database names in that server. So save this query in the Query.sql file :

select name from sys.databases 

Now we will run a Powershell script to run this query against all the servers.

But before we do that, we will need to add these two lines in the Powershell command prompt :

PS > Add-PSSnapin SqlServerCmdletSnapin100

PS > Add-PSSnapin SqlServerProviderSnapin100

Here is the PS script (lets call it PScript1.ps1):

$QueryPath=”G:\Powershell_test_folder\Query.sql”
$OutputFile=”G:\Powershell_test_folder\Output.txt”

$ExecuteQuery= Get-Content -path $QueryPath | out-string

“Results — > `r`n`r`n” > $OutputFile

FOREACH($server in GC “G:\Powershell_test_folder\AllServers.txt”)
{
$server
“——————————-” >> $OutputFile
$server >> $OutputFile
“——————————-” >> $OutputFile
invoke-sqlcmd -ServerInstance $server -query $ExecuteQuery -querytimeout 60000 | ft -autosize | out-string -width 4096 >> $OutputFile
}

 

Ok, so how does the script work ? We define three variables using the $ symbol :

  1. $QueryPath : This shows the location of the query file.
  2. $OutputFile : This is where the results of your query appear.
  3. $ExecuteQuery : This will read (get-content or gc) your input query and write the “Results” string to your output file.

Now the FOREACH command will iterate through your servers in the AllServers.txt file. It does this by reading (GC or Get-Content) the AllServers.txt file. It then outputs the name of your server first, followed by the results of your query (in this case the list of databases on your server).

The query is executed using the Invoke-Sqlcmd command let. This is just like running the usual sqlcmd command. The ” | ft” means it will output the result in a formatted table output.

In my case, I used a more complex query to collect the details of the errorlogs. The following query used in the Query.sql file will run the xp_readerrorlog command to find wherever the ‘ERROR’ or ‘FAIL’ statements were recorded in the SQL Server Errorlogs for the past two days :

declare @Start varchar(30);
declare @End varchar(30);
set @Start=convert(varchar(30),getdate()-3,25);
set @End=convert(varchar(30),getdate()+1,25);
exec master..xp_readerrorlog 0, 1, N’error’, NULL, @Start, @End
exec master..xp_readerrorlog 0, 1, N’fail’, NULL, @Start, @End

To execute the PS script, simply type this at your PS command prompt :

PS > .\PScript1.ps1

Powershell is fast and awesome! There is still a lot to learn and study about this. Here are some resources that helped me (thx to Pinal Dave, Raushan,  Sandip Shinde, and others) :

https://blog.sqlauthority.com/2014/11/23/sql-server-fix-the-term-invoke-sqlcmd-is-not-recognized-as-the-name-of-a-cmdlet/

http://www.sqlservercentral.com/scripts/powershell/129948/

SQL Sever – PowerShell for Executing a SQL Query on Multiple Servers

https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

 

 

 

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s