Multiple Server Queries with SSMS 2008

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jonathan Kehayias » Multiple Server Queries with SSMS 2008

Multiple Server Queries with SSMS 2008

Rate Topic

Display Mode

Topic Options

Author

Message

Jonathan Kehayias

Posted Wednesday, August 13, 2008 9:26 PM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

Post #552398

KenSimmons

Posted Thursday, August 14, 2008 7:02 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:07 AM
Points: 1,221, Visits: 2,614

Pretty cool Jonathan. Yet another reason to upgrade to 2008.

Ken Simmons
http://twitter.com/KenSimmons

Post #552638

RBarryYoung

Posted Thursday, August 14, 2008 12:23 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

Excellent observation, Jonathan. thanks for passing it along. :)

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #552978

Prakash Heda

Posted Monday, August 18, 2008 3:19 PM

Old Hand

Group: General Forum Members
Last Login: Sunday, February 17, 2013 2:38 AM
Points: 369, Visits: 392

Thanks, i was using this technique for a while, what i couldn't figure out it how the resultset of all these servers can be store in one table in one database for future usage?

Appriciate any response...

Thx ~ Prakash

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Post #554625

Jonathan Kehayias

Posted Monday, August 18, 2008 4:51 PM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

The multi server query is actually executed as separate batches on each server, so any statements being run are still batch scoped which means they will complete inside their respective connection. So if you issue a create the table statement, it is run on each server in the database that is selected in the dropdown. The same goes for Inserts. The aggregation of the data and display with server name happens client side by SSMS. This is why you can control the ServerName being ON/OFF with a change to the Options under Tools.

Your question actually points out an extremely valuable use for this that I never actually considered previously. I have seen a number of questions where someone wants to update a database across multiple servers, like a development farm with the same piece of code. I worked in one company that had 24 different development instances to manage consistently. Changing one core procedure would require changing the connection 24 times to deploy it to everywhere. This is easily accomplished with Multi-server queries, and I'll have to call a buddy of mine who actually has a nightmarish development environment like I describe above and tell him about this.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs

Post #554659

Prakash Heda

Posted Monday, August 18, 2008 5:30 PM

Old Hand

Group: General Forum Members
Last Login: Sunday, February 17, 2013 2:38 AM
Points: 369, Visits: 392

yeah thats the problem, we here supporting 100+ environment, without access to the box, only sysadmin access...making a check everyday to ensure all of them are in sync and basic configuration is not changes...ddl changes, security changes tracking are some of the tasks needed to ensure a good control...

as of now using sqlcmd to do this tasks...it was quite painful though

lets hope somebody has explored that option and have right solution...

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Post #554674

timothyawiseman

Posted Wednesday, August 20, 2008 1:51 PM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900

That was extremely useful, thank you.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Post #556081

mishaluba

Posted Thursday, August 21, 2008 10:22 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525, Visits: 617

What a great tip. Thank you! It infuriates me that Microsoft wouldn't document better something as useful as this.

Post #557049

« Prev Topic | Next Topic »

Permissions