Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

2008 Central Management Server - Schedule Multi Server Query? Expand / Collapse
Author
Message
Posted Friday, October 8, 2010 7:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 16, Visits: 207
Hi Everyone - I have a 2008 Central Management Server that we're able to run queries from against all our SQL servers. I'm wondering if there's a way to do this via the command line or a job so the queries can be automated and rolled up into reporting services reports. Any suggestions?


Post #1001297
Posted Monday, October 11, 2010 7:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 6, 2014 9:22 AM
Points: 141, Visits: 523
Think you may have to do this using the master\target option of a job.
Post #1002150
Posted Monday, October 11, 2010 8:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
There's no way of doing it through management studio.

I accomplished this by using powershell and combining multiple resultset along with @@SERVERNAME into a $variable and pinging a HTML email report with the result.
Post #1002230
Posted Wednesday, November 24, 2010 2:23 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:13 PM
Points: 399, Visits: 821
MysteryJimbo (10/11/2010)
There's no way of doing it through management studio.

I accomplished this by using powershell and combining multiple resultset along with @@SERVERNAME into a $variable and pinging a HTML email report with the result.


This sounds like exactly what I need to do. Can you post some code?



Post #1026201
Posted Monday, December 20, 2010 1:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:10 PM
Points: 306, Visits: 967
Hi shew and others.

Did you find a way to schedule multi-server queries using PowerShell? I would like to do this too and have been looking at a way to have Enterprise Policy Management Framework use queries in addition to policies.

Howard

shew (11/24/2010)
MysteryJimbo (10/11/2010)
There's no way of doing it through management studio.

I accomplished this by using powershell and combining multiple resultset along with @@SERVERNAME into a $variable and pinging a HTML email report with the result.


This sounds like exactly what I need to do. Can you post some code?
Post #1037390
Posted Monday, December 20, 2010 4:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:13 PM
Points: 399, Visits: 821
No, I haven't. Theoretically, you should be able to load the server names into an array and bump through each element in the array with a sqlcmd.exe statement, but I haven't tried it. This would *not* put all of the result sets into a single stream, but it would allow you to run the same statement on all servers from a single command line.

A while back, I did something similar by hardcoding all of the server names in a PowerShell script that accepts the name of a .sql script as a parameter. It's not perfect, but it allows me to execute desired code with a single call. I just have to remember to update the script to add new servers when the environment changes.



Post #1037442
Posted Monday, December 20, 2010 4:29 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:10 PM
Points: 306, Visits: 967
This blog article shows how to script multi server queries through PowerShell that can be scheduled as a SQL Agent job. Unfortunately, outputing to the PowerShell window is not useful for something that is automated. I'd like to write the output to a table on my CMS. Enquired as to how that can be done in another thread.

http://blogs.msdn.com/b/sethus/archive/2009/04/09/execute-a-query-on-servers-in-central-management-server-group.aspx

Post #1037445
Posted Monday, December 20, 2010 6:00 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:13 PM
Points: 399, Visits: 821
PHXHoward (12/20/2010)
This blog article shows how to script multi server queries through PowerShell that can be scheduled as a SQL Agent job. Unfortunately, outputing to the PowerShell window is not useful for something that is automated. I'd like to write the output to a table on my CMS. Enquired as to how that can be done in another thread.

http://blogs.msdn.com/b/sethus/archive/2009/04/09/execute-a-query-on-servers-in-central-management-server-group.aspx



Hmmm... Interesting idea. I need to check this out.

>>Unfortunately, outputing to the PowerShell window is not useful for something that is automated.

sqlcmd.exe and the -o switch can help you get around this limitation of invoke-sqlcmd.

Alternatively, this may help too:

invoke-sqlcmd -ServerInstance $DatabaseConnection_STR -Database dba -Query "select select serverproperty('InstanceName')" -Verbose



Post #1037458
Posted Monday, December 5, 2011 1:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 12:01 PM
Points: 7, Visits: 131
Can someone Please post the code for scheduling a job on Central management server? I am desperately looking for the code.
Post #1216648
Posted Monday, December 5, 2011 1:22 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 3:10 PM
Points: 306, Visits: 967
muhammadrazzaqpk-1032285 (12/5/2011)
Can someone Please post the code for scheduling a job on Central management server? I am desperately looking for the code.


You can create a SQL Agent Powershell job likethis:

$instanceNameList = invoke-Sqlcmd -query "
SELECT [server_name] as Name
FROM [msdb].[dbo].[sysmanagement_shared_registered_servers_internal] SSRSI
join [msdb].[dbo].[sysmanagement_shared_server_groups_internal] SSSGI
on SSRSI.server_group_id = SSSGI.server_group_id
where SSSGI.parent_id = '<use this if you want to limit the included servers to a CMS group>'
" -serverinstance "<your CMS here>"
$results = @()

foreach($instanceName in $instanceNameList)
{$results += Invoke-Sqlcmd -Query "

<your query here>

" -ServerInstance $instanceName.Name}

$results| Where-Object {$_} | Export-Csv <path to save csv file> -NoTypeInformation


It will dump the query results to a text file that can be loaded into your CMS using an SSIS step. Pretty powerful so I suggest that you run the Agent job with a proxy account that only has read access to the servers in the CMS.

The above example has four places that must be changed to replace the <...>

Hope that helps

Howard
Post #1216659
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse