CMS configuration

I have configured CMS in my sql 2008 instance. I want to import all the result to a database present on the same instance.

How to do it?
LVL 5
VIVEKANANDHAN_PERIASAMYAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

When using SSMS you have 3 destinations for the results - text/grid/file.

On a single server you can write a select into query. You can run this query from SSMS/SQLCMD ... a bunch of places. The insert is not dependant on the query tool. The results still come back to a text/grid/file destination.

When using SSMS to issue a multi-server query, the results come back to a text/grid/file destination.

If every server you query has a linked server pointing to server and a database blah, then you could write

insert server.blah.dbo.SomeTableName( columns ... )
select
  columns
from dbo.someSourceTable
where
  somewherecondition.

So what I've written here is a generic insert statement that explicitly states which server to store the results in, that could run from SSMS/SQLCMD ... has nothing multi-server about it.

But there is the overhead of maintaining all the links etc.

I suggest a different approach: Write a vbscript/powershell script that queries each server in turn and stores the results in a central server/database/table

Here is key snippet from my powershell script. Its a little messy as it has the logic to select the server name from a table of SQL Instances, but you should get the idea.

Regards
  David

foreach( $s in Invoke-Sqlcmd -Query ‘select distinct ss.FQDN as ServerName from dbo.SQLServer ss where isWMIWorking = 1 and ( DateDecommissioned is null or DateDecommissioned > dateadd( day, datediff( day, 0, getdate()), 0 ))’ -Database _dba -ServerInstance aadrdb0012\mtdata_test ) {
    $d = Get-WmiObject -computername ( $s.ServerName ) Win32_LogicalDisk -filter "DriveType=3" | foreach { 
        add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd HH:mm:ss")) 
        add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Size/1GB),2)) 
        add-member -in $_ -membertype noteproperty UsedGB $([math]::round((($_.Size - $_.FreeSpace ) /1GB ), 2 ))
        add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2)) 
        add-member -in $_ -membertype noteproperty PercentUsed $([math]::round(((1 - [float]$_.FreeSpace/[float]$_.Size) * 100),2)) -passThru 
        } | Select UsageDT, SystemName, Caption, VolumeName, SizeGB, UsedGB, FreeGB, PercentUsed

Open in new window

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
There has to be a documentation for CMS.  This application related more than database related.
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
CMS is related to Database. I'm talking about central management server, which comes with SQL.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Sorry I thought CMS stood for Content Management Server.  Sorry, I have never used Central Management Feature. My guess is the usage is stored in msdb but I hope somebody will confirm.  In this case, backing up / restoring msdb database should help migrate but I am not sure where content is stored.
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
I believe it's doesn't store in msdb. When create a temp variable, it stored in a each corresponding server.
0
 
David ToddSenior DBACommented:
Hi,

Are you talking about multi-server queries, where you can select node on the CMS Tree in Registered Servers etc.

I think that the queries are managed by SSMS rather than by the database engine, so I don't believe that results can be stored in a database.

Regards
  David
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
Yes, I ran the query and i want to import the resultant to one database.
0
 
David ToddSenior DBACommented:
Hi

I couldn't find how to do this myself, so my workaround was a power shell script with list of servers in a table. Results stored in a table.

You can't just store results from a query in a table.

Only way is if all servers in node have same linked server, pointing to same server, then multi-server query can be an insert.

HTH
  David
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
Does anyone have tried this? Is there any other work around solution in SSMS?
Apart setting up the linked server?
0
 
VIVEKANANDHAN_PERIASAMYAuthor Commented:
Any experts call?Please help. I still looking for an solution.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Perhaps explaining what you are trying to achieve exactly may help experts help you better.  We may for instance recommend a workaround solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.