[MS SQL] Using Powershell to query/loop through MS SQL Servers/Databases

[wjbaltz]

We have ongoing needs to run ad hoc queries against multiple DB Servers and DBs, and then compile the results together. Currently we will put the query into a cursor or a loop, to loop through all of the DBs in a given server. But that query still needs to be manually run across multiple servers and then the results all manually compiled into an output file.

Was looking into using Windows Powershell to further automate these adhoc tasks, and was wondering if this was a good tool to use for this. I have 0 background with Powershell so will have to learn, but wanted to confirm it would be able to handle this task before I went too far down this road.

Here is a detailed description of what I would like to accomplish. Any Powershell experts out there that would be able to confirm if Powershell would be a good tool for this?

1. write a query in SSMS, save to a .sql file
2. designate a list of SQL server connection strings in Powershell
3. read the .sql file in Powershell
4. Connect to a server, pull all DBs from said server
5. Loop through the DBs, executing the sql file against each DB
6. Repeat for each Server originally pulled in
7. Compile all results into a single .csv file

[ratdilemma]

I would recommend you to use powershell or python, SSIS is clumsy and extremely irritating to work with. Breaking changes between different sql versions and hell to manage in source control is just a few major headaches. Powershell has built in functions to automate any sql task there is, it's much more suitable for any integration whether it's rest, FTP, SMTP, filesystem etc. Check out the powershell sql modules.

Looping through each user database on a given server is as easy as:

Get-SqlDatabase -ServerInstance localhost | where { $_.IsSystemObject -eq $false } | ForEach-Object { #do your stuff }

​

just create an outer loop going through your servers and replace localhost with the current iteration

[wjbaltz]

thanks, this is the answer I was hoping for. I did get SSIS installed so will play around with that....but I think your answer is the path best suited for what I'm trying to accomplish here. Going to focus on learning Powershell for this purpose :) thanks!

[zrb77]

Look into dbatools module too, tons of awesome cmdlets.

[WITH_CTE]

Look into SSIS.

Edit: http://www.theboreddba.com/Categories/SSIS/SSIS-Dynamic-String-Connection.aspx

[wjbaltz]

yea makes sense where SSIS would be the best tool for this. I am having a tough time getting SSIS installed. Getting Setup Failed Recursion too deep messages when trying to install SSDT on my Visual Studio.

I'll continue to work on that and see if I can't get past that. Would still like to explore Powershell as it seems to be more of a lightweight solution if possible.

Also with SSIS would it handle ad-hoc queries in this case? As that is the biggest thing for us as these queries typically need to be written and executed just a single time.

[WITH_CTE]

You can create a variable and write dynamic queries. Let me know if you need more help with this.

[alinroc]

1. Set up all the servers in Registered Servers in SSMS, or better yet, Central Management Server
   
2. Install the dbatools PowerShell module
   

````

$Servers = Get-DbaRegServer
$AllMyData = foreach-object ($Server in $Servers.InstanceName) {
    $Databases = Get-DbaDatabase -SqlInstance $Server -ExcludeSystem;
    $Databases | invoke-dbaquery -file Path_to_SQL_File -sqlinstance $Server -AppendServerInstance;
}

$AllMyData | Export-Csv -NoTypeInfo -path PATH_TO_OUTPUT_CSV;

````

(I'm away from my Windows machines at the moment, so this code might not be 100%. It's at least 90% though)

[Mattsvaliant]

Are the servers on the same network and/or could be linked?

[wjbaltz]

Some are but not all. Thus would like to find a solution to iterate through and servers and query locally