January 7, 2010 at 2:35 pm
Simply put, you can run a query against all servers in the central management server group using SSMS. Does anyone know how to create a reporting services datasource that would mimic this action?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 9, 2010 at 9:47 pm
I have not seen this functionality to define a data connection as a registered CMS, within SSRS, nor within SSMS SQL Agent to create a scheduled job using the registered CMS connection. I've also tried to find a connection object type within SSIS and have not had any luck. I was very disappointed after developing an auditing solution, that I could not automate my queries using a scheduled job.
I ended up creating a CMDB database with all of my tables and views on the CMS server. I created linked server definitions to all of my remote SQL servers. Then ran my query manually against my registered list of servers on the CMS to populate the CMDB database. All of my SSRS reports are then pulled from CMDB database. Since the audit schedule is quarterly, running the query manually to get all my report data is not going to be a huge problem, but I'd prefer it were fully automated.
Thinking back on this though, I will probably rearchitect this to be a push process from the remote servers, rather than the pull via the CMS. I'm running into too many problems with all of the remote SQL 2000 servers, in that when I execute my query, it's failing to connect. The odd thing is, the linked server definition works from the remote SQL 2000 server to the SQL 2008 CMS and I can add the data from the SQL 2000 server without any errors. I dont' seem to have this same problem with SQL 2005 servers.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply