August 13, 2008 at 9:26 pm
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]
August 14, 2008 at 7:02 am
Pretty cool Jonathan. Yet another reason to upgrade to 2008.
August 14, 2008 at 12:23 pm
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]
August 18, 2008 at 3:19 pm
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
August 18, 2008 at 4:51 pm
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]
August 18, 2008 at 5:30 pm
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
August 20, 2008 at 1:51 pm
That was extremely useful, thank you.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
August 21, 2008 at 10:22 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply