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?
asked Oct 19 '09 at 10:57 AM in Default
Steve Jones - Editor ♦♦
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:
Nice Wiki on CMS and how to use it (w/ video):
If you DONT have a SQL Server 2008 available in house then I suggest installing SSMS Tools pack:
This will allow that functionality along with a WHOLE lot more. A MUST have for any SQL pro and its free!
answered Oct 19 '09 at 11:26 AM
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
2 - If I have Different passwords for each server. Put the password in front of each server, separated by commas
Keep in mind that the Central Management Server must be a SQL Server 2008 instance Jorge.
answered Oct 25 '09 at 12:23 PM
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.
answered Oct 25 '09 at 01:14 PM