locked
Sample query to run on multiple sql servers RRS feed

  • Question

  • Hi Friends,

    Need a sample query that must run on MULTIPLE SQL 2008 SERVERS from a same query window.

    Central mgt sever is not a option, please note.

    Please assist.


    Rgds, Minesh

    Wednesday, September 12, 2012 9:02 AM

Answers

  • One option is to use either Linked server or OPENDATASOURCE.

    In both cases , I'd create a table holding the servers/instances you need to access and then loop through these one by one. You can then dynamically setup each as a linked server or construct the OPENDATASOURCE statement. No matter what, it will require some scripting though.

    You'll also have to make sure that you have permissions to access the servers before you get started at all. Either by having a SQL Login on each of the servers or access the servers using WIndows authentication.

    A seoncd option, is to create a SSIS package to do it. Again you need a list/table with the server/instance names and then you use a "ForEach Loop Container" in your package. This will then loop though each of the instances and here you dynamically change the Connection to your target instance and then run the query. 


    Steen Schlüter Persson (DK)

    • Proposed as answer by Naomi N Thursday, September 13, 2012 4:12 AM
    • Marked as answer by Kalman Toth Tuesday, September 18, 2012 2:46 PM
    Wednesday, September 12, 2012 12:00 PM

All replies

  • Dear Minesh,

    You can write a stored procedure to parse through all the Instances and execute the query on each Instance.

    Regards,

    ManjunathRV

    Wednesday, September 12, 2012 9:10 AM
  • Linked servers

    Exec srv1.master.xp_fixeddrives 
    Exec srv2.master.dbo.xp_fixeddrives 
    Exec srv3.master.dbo.xp_fixeddrives 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Wednesday, September 12, 2012 9:18 AM
    Answerer
  • create linked server at the server on which you are and query needs to be HIT on multiple servers. and use them as suggested by Uri as well.
    Wednesday, September 12, 2012 11:53 AM
  • One option is to use either Linked server or OPENDATASOURCE.

    In both cases , I'd create a table holding the servers/instances you need to access and then loop through these one by one. You can then dynamically setup each as a linked server or construct the OPENDATASOURCE statement. No matter what, it will require some scripting though.

    You'll also have to make sure that you have permissions to access the servers before you get started at all. Either by having a SQL Login on each of the servers or access the servers using WIndows authentication.

    A seoncd option, is to create a SSIS package to do it. Again you need a list/table with the server/instance names and then you use a "ForEach Loop Container" in your package. This will then loop though each of the instances and here you dynamically change the Connection to your target instance and then run the query. 


    Steen Schlüter Persson (DK)

    • Proposed as answer by Naomi N Thursday, September 13, 2012 4:12 AM
    • Marked as answer by Kalman Toth Tuesday, September 18, 2012 2:46 PM
    Wednesday, September 12, 2012 12:00 PM