Big Data, Data Analytics and Data Science | BI-BIGDATA.com

This is 50^th Article on Bi-BigData.com and we are getting great response from the community. This article will be covering how to use PowerShell and SMO to connect multiple SQL Server instances and execute a pre defined SQL command against all of them. This is applicable when we have same database available on multiple SQL Server Instances where you want to execute same SQL query. We are going to use the Invoke-Sqlcmd cmdlet to achieve our mission.

To achieve this we need to identify the available SQL Server instances to run your SQL Query on. You can create a text file in C:\PowerShell folder called ServerInstances.txt and put every instance name with line delimiter. For example:

SQL2012
MyDomain\SQL2008R2
PRODUCTION\SQL2012

We can use Inline SQL or use SQL file as parameter to Invoke-Sqlcmd cmdlet to make it more dynamic.

#This PowerShell Scrip is well-suited with PowerShell V3.0
#import SQL Server module
Import-Module SQLPS -DisableNameChecking

#get all the instances and temporarily store them in a variable
$ServerInstences = Get-Content "C:\PowerShell\ServerInstances.txt"

#the global query we want to execute.
#this can be *.SQL  file which can be passed in Invoke-Sqlcmd
$SQLQuery = @"
CREATE TABLE [dbo].[PS_Table]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
CONSTRAINT [PK_PS_Table] PRIMARY KEY CLUSTERED ([ID] ASC)
)
"@

#he database we want to execute it against, regardless of the instance
$DBName = "MyDatabase"

#iterating through all instances.
$ServerInstences |
ForEach-Object {
#For each instance, we create a new SMO server object
$ServerObject = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $_
#use the Invoke-Sqlcmd cmdlet to execute the query
#we are passing in the pipeline is the instance name, which is $_
Invoke-Sqlcmd `
-ServerInstance $_ `
-Database $DBName `
#-InputFile "C:\PowerShell\SQLScript.sql"
-Query $SQLQuery
} 

Hope this helps.

Thanks

Sandip