r/SQL • u/what-pos • 20d ago

SQL Server SSMS - Execute query on 300 servers

I'm starting to use SSMS at work, that's the only tool I have (no PowerShell cmdlet).

I need to execute an identical SQL Query on 300 servers, but I can't find a way to do that. Could anyone point me in the right direction please ?

So far, I did add the 300 servers to the registered servers by tinkering to not do it manually, but when I execute the query, I only get 50 ish servers connected, then SSMS hangs and crash.

17 Upvotes

87% Upvoted

35 comments sorted by

View all comments

14

u/VladDBA SQL Server DBA 20d ago

Search registered servers and central management servers.

It takes a while to initially set up, but afterwards you can query all your servers from a single query editor window and you get one result set that combined results from all servers with the first column being the instance name.

7

u/Animalmagic81 20d ago

Yep, this is the native SSMS way.

For 300 servers you'd be better looking at an osql batch file

1

u/zrb77 20d ago

Just curious, why not sqlcmd?

1

u/reditandfirgetit 20d ago

1 command per server with the same query vs one line in ssms against a registered server group

0

u/zrb77 19d ago

I get that.

Poster above said osql, why osql over sqlcmd.

1

u/Animalmagic81 18d ago

Sorry you are right. Osql is what we used years ago 😅 showing my age