Multiple Server Queries with SSMS 2008

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    Comments posted to this topic are about the item Multiple Server Queries with SSMS 2008

    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[/url]

  • KenSimmons

    SSCertifiable

    Points: 7822

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

  • RBarryYoung

    SSC Guru

    Points: 143327

    Excellent observation, Jonathan. thanks for passing it along. 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Prakash Heda

    SSCertifiable

    Points: 5538

    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

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    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[/url]

  • Prakash Heda

    SSCertifiable

    Points: 5538

    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

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    That was extremely useful, thank you.

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

  • Misha_SQL

    SSCertifiable

    Points: 5386

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

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply