Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CMS configuration

Posted on 2013-05-11
14
Medium Priority
?
314 Views
Last Modified: 2013-06-01
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?
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
14 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39158186
There has to be a documentation for CMS.  This application related more than database related.
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39158215
CMS is related to Database. I'm talking about central management server, which comes with SQL.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39158376
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39158407
I believe it's doesn't store in msdb. When create a temp variable, it stored in a each corresponding server.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39166772
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
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39167352
Yes, I ran the query and i want to import the resultant to one database.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39167463
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
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39169745
Does anyone have tried this? Is there any other work around solution in SSMS?
Apart setting up the linked server?
0
 
LVL 5

Author Comment

by:VIVEKANANDHAN_PERIASAMY
ID: 39176810
Any experts call?Please help. I still looking for an solution.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39200584
Perhaps explaining what you are trying to achieve exactly may help experts help you better.  We may for instance recommend a workaround solution.
0
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 39202556
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question