Retrieving Info from Central Management Servers (CMS)

Question

Retrieving Info from Central Management Servers (CMS)

dbaforever

Hall of Fame

Points: 3740
More actions
May 11, 2012 at 7:44 am

#392237

Hi everyone,
I have configured CMS on my 2008 R2 server.
I have a CMS node containing several SQL 2005 server names.
I want to query info from those servers and insert the results into a database that resides on the local instance.
But when I run this within the "2005" node:
Insert into DatabasesInventory.dbo.DatabaseInfo
(DatabaseName, FileName, DateCreated, CompatibilityLevel, AsOfDate)
select name,filename, crdate, cmptlevel, GETDATE() from sysdatabases;
it fails with "Invalid object name 'DatabasesInventory.dbo.DatabaseInfo'."
My question is: Is there a way to write the info to a non-CMS database?
Hope my question is clear.
Thanks, John

Viewing 9 posts - 1 through 8 (of 8 total)

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

John Bates-251278 (5/11/2012)
Hi everyone,
I have configured CMS on my 2008 R2 server.
I have a CMS node containing several SQL 2005 server names.
I want to query info from those servers and insert the results into a database that resides on the local instance.
But when I run this within the "2005" node:
Insert into DatabasesInventory.dbo.DatabaseInfo
(DatabaseName, FileName, DateCreated, CompatibilityLevel, AsOfDate)
select name,filename, crdate, cmptlevel, GETDATE() from sysdatabases;
it fails with "Invalid object name 'DatabasesInventory.dbo.DatabaseInfo'."
My question is: Is there a way to write the info to a non-CMS database?
Hope my question is clear.
Thanks, John

if i understand correctly then you need to create a linked server from your sql2008 instance to each of your 2005 instances

then you can query using 4 part naming

Insert into DatabasesInventory.dbo.DatabaseInfo

(DatabaseName, FileName, DateCreated, CompatibilityLevel, AsOfDate)

select name,filename, crdate, cmptlevel, GETDATE() from mylinkedserver.master.dbo.sysdatabases

MVDBA

dbaforever Hall of Fame Points: 3740 More actions · Answer 2

Thanks, Michael.

No, I'm trying to avoid using Linked Servers.

With CMS you can query many servers via a single query. Info for all your central management servers is returned.

But I want to insert the resultset into a db that resides on the local server (where the CMS servers are configured)

John

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

I would use the data in an SSIS package using a ForEach Loop to perform the actions. I won't use Linked servers for this..

CEWII

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

John Bates-251278 (5/11/2012)
Thanks, Michael.
No, I'm trying to avoid using Linked Servers.
With CMS you can query many servers via a single query. Info for all your central management servers is returned.
But I want to insert the resultset into a db that resides on the local server (where the CMS servers are configured)
John

ah - i understand what you mean - i was thinking of a different CMS (configuration management system)

i don't think you can do what you are asking in this way - you can't pump the results to a single location (as far as i know)

it's either linked servers or SSIS

MVDBA

dbaforever Hall of Fame Points: 3740 More actions · Answer 5

Thanks Mike and Elliot.

Hmmm, can anyone give me a clue for how to do this via an SSIS package?

I'm familiar with Foreach loops but not sure how to be able to reference both "sides" of my environment - insert into the Local db but select from my Central Mgmt server node.

Thanks, John

Jared SSC Guru Points: 61793 More actions · Answer 6

Did you try the server name instead f just the 3 part naming for the INSERT INTO? Not sure that it will work, and if it doesn't then I don't think it can be done.

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 7

Hmm... Can't be done. http://www.brentozar.com/archive/2008/10/sql-server-2008-management-studio-group-execute/ toward the bottom. It is because the SSMS "feature" sends this query to each server individually. Sorry!

Jared
CE - Microsoft

dbaforever Hall of Fame Points: 3740 More actions · Answer 8

Thanks SQLKnowItAll and everyone who contributed some ideas.

I'll start developing an SSIS package. I'm hoping it's possible to have a connection manager for my "2005" node of managed servers. This will be a fun experience.

John