Executando uma Query em Múltiplos Servidores


 

Se eu quiser trocar a senha de SA de todos os meus servidores ?

Posso usar o Central Management Server do SSMS ? Com certeza ….

MAS POWERSHELL ROCKS !!!!!!

(gostei de ficar falando isso né..esquenta não..tem dia que não tomo meu remédio ..UHAAA.UHAAA (risada macabra) !!!!)

1 – Se eu tenho uma senha somente para todos os servidores. Crie um .txt com o nome de todos os servidores e, por exemplo, coloque na pasta C:\PS\Servers.txt.

server1
server2
server3\instance1
server3\instance2

 

foreach ($svr in get-content "C:\PS\Servers\servers.txt"  )
{
         trap  [Exception] {
                $Err = $_.Exception.Message
                Write-Host $Err  " Não posso alterar o login em "  $svr
                $Error.Clear()
                continue;
           }   
    $sql = "ALTER LOGIN [sa] WITH PASSWORD=’NEWPASSWORD’"
    Invoke-Sqlcmd -ServerInstance $svr -Database "MASTER" -Query $Sql -ErrorAction stop
}


 

2 – Se eu tenho senhas diferentes para cada servidor. Coloco a senha na frente do nome separado por virgula.

server1,PWD1
server2,PWD2
server3\instance1,PWD3
server3\instance2,PWD4

foreach ($svr in get-content "C:\PS\Servers\servers_pwd.txt"  )
{
         trap  [Exception] {
                $Err = $_.Exception.Message
                Write-Host $Err  " Não posso alterar o login em "  $server[0]
                $Error.Clear()
                continue;
           }   
    $server = $svr.Split(",")
    $sql = "ALTER LOGIN [sa] WITH PASSWORD=’" + $server[1] + "’"
    Invoke-Sqlcmd -ServerInstance $server[0] -Database "MASTER" -Query $Sql -ErrorAction stop
}

E mais um script ao som de :
Every breath you take
Every move you make
Every bond you break
Every step you take
I’ll be watching you”

 

Every breath you take

The Police

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell. 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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s