x

Execute a Query against multiple servers

Is there a way that I can a query against multiple servers?

I need to change all the passwords for "sa" on multiple servers. Can I easily do this in one step or do I have to run this against each server separately?

more ▼

asked Oct 19, 2009 at 10:57 AM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left

6 answers: sort oldest

Do you have SQL Server 2008? If so you can take advantage of the Central Management Server feature which lets you register multiple servers on that server and execute one query against multiple servers from there.

BOL on how to Create a CMS:

http://msdn.microsoft.com/en-us/library/bb934126.aspx

Nice Wiki on CMS and how to use it (w/ video):

http://sqlserverpedia.com/wiki/Central_Management_Server

If you DONT have a SQL Server 2008 available in house then I suggest installing SSMS Tools pack:

http://www.ssmstoolspack.com/

This will allow that functionality along with a WHOLE lot more. A MUST have for any SQL pro and its free!

more ▼

answered Oct 19, 2009 at 11:26 AM

Jorge Segarra gravatar image

Jorge Segarra
419 2

(comments are locked)
10|1200 characters needed characters left

Click on a group of servers in Management Studio SQLServer 2008. Click on "New Query" and you will see at the bottem of the screen that you are connected to all the servers in that group, a server count is displayed instead of a server name, and any SQL will run against all the servers. However, I do like to keep the sa password different on each server.

more ▼

answered Oct 19, 2009 at 11:28 AM

John Stiney gravatar image

John Stiney
1

(comments are locked)
10|1200 characters needed characters left

Keep in mind that the Central Management Server must be a SQL Server 2008 instance Jorge.

more ▼

answered Oct 25, 2009 at 12:23 PM

Tim Ford gravatar image

Tim Ford
211 1

(comments are locked)
10|1200 characters needed characters left

Red Gate have a tool which does this and a bit more than is built in to SSMS 2008: SQL Multiscript (http://www.red-gate.com/Products/SQL_multi_script/index.htm).

Disclaimer: I work for Red Gate.

more ▼

answered Oct 25, 2009 at 01:14 PM

James Moore gravatar image

James Moore
178 2 2 3

(comments are locked)
10|1200 characters needed characters left

I talk for myself, but every time I think in repetition and multiple servers think about powershell. My suggestion would be:

1 - If I have only one password for all servers . Create a .Txt with the name of all servers in the 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  " Cannot Alter login at "  $svr
    		$Error.Clear()
    		continue;
    	   }	

    $sql = "ALTER LOGIN [sa] WITH PASSWORD='NEWPASSWORD'"
    Invoke-Sqlcmd -ServerInstance $svr -Database "MASTER" -Query $Sql -ErrorAction stop

}

2 - If I have Different passwords for each server. Put the password in front of each server, separated by commas

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 " Cannot Alter login at " $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
}
more ▼

answered Oct 26, 2009 at 02:07 AM

Laerte Junior gravatar image

Laerte Junior
488 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x344
x126

asked: Oct 19, 2009 at 10:57 AM

Seen: 10191 times

Last Updated: Sep 28, 2012 at 03:56 AM