2008 Central Management Server - Schedule Multi Server Query?

  • 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?

  • Think you may have to do this using the master\target option of a job.

  • 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.

  • 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?

  • 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?

  • 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.

  • 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

  • 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

  • Can someone Please post the code for scheduling a job on Central management server? I am desperately looking for the code.

  • 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

  • Thanks a lot Haword, I really appreciate your help. However I am getting syntax errors on it. Lets say my instance name is "myserver\dba" (where myserver is my machine name and dba is instance name for my CMS server . I have pasted the query exactly how I put it in the powershell script (with valide server and instance name) but getting error which is posted at the End of this script: Can you please assist me where I am doing things wrong? I don't need where clause for now.

    Thanks again for your help.

    $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 "

    -serverinstance "myserver\dba"

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

    "SELECT a.name, b.type, MAX(b.backup_finish_date)

    LastSuccessfulBackup,

    CAST((GETDATE() - MAX(b.backup_finish_date)) AS NUMERIC(5, 2)) IntervalInDays

    FROM master..sysdatabases a

    LEFT OUTER JOIN msdb..backupset b ON a.name = b.database_name

    where b.type='D'

    GROUP BY a.name, b.type

    ORDER BY a.name, b.type" -ServerInstance $instanceName.Name}$results| Where-Object {$_} | Export -Csv E:\Backup_log -NoTypeInformation

    Message

    Executed as user: myuser. A job step received an error at line 5 in a PowerShell script. The corresponding line is '-serverinstance "myserver\dba" '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Missing expression after unary operator '-'. '. Process Exit Code -1. The step failed.

  • 1260221107 (12/8/2011)


    ...

    For God's sake don't SPAM here.

  • Dev (12/8/2011)


    1260221107 (12/8/2011)


    ...

    For God's sake don't SPAM here.

    This user has done it on a number of topics now and is getting quite annoying, I have gone through the uses last posts and reported them all

    There is just no need for stuff like that

  • anthony.green (12/8/2011)


    Dev (12/8/2011)


    1260221107 (12/8/2011)


    ...

    For God's sake don't SPAM here.

    This user has done it on a number of topics now and is getting quite annoying, I have gone through the uses last posts and reported them all

    There is just no need for stuff like that

    In fact I reported many of them... I was just hoping this SPAMer should see it shouldn't SPAM any more. Else it would be really hard to report each individual post as SPAM.

  • are you guys talking about me being a spamer? I have subscribed for this topic and seeking for help.

    This is first time, I have actually replied to any topic, usually I find by just reading.

    Please clarify which user you talking about?

Viewing 15 posts - 1 through 15 (of 27 total)

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