Data Collection Centralised Database -MDW

Question

Data Collection Centralised Database -MDW

SQListic

SSCertifiable

Points: 6904
More actions
April 17, 2014 at 11:20 am

#396622

Hi All,
I Enabled Data Collection on one of the server and planned to make it as Centralised Management Data Warehouse I configured data collection on it and can view reports. Next, I went to other server and configured "Set up data collection" to use my first instance as the centralised Database. But the issue is I can only see reports of first server. Am I missing something here.
I did exactly as explained in this video
http://www.youtube.com/watch?v=qq495N3-kkM

Viewing 12 posts - 1 through 11 (of 11 total)

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

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

My first question would be are you sure your remote data collection is loading your MDW correctly.

what is the status of the agent jobs on your remote(not the one with the MDW database) server. you could also check if data from the remote instance is being load. run this in the MDW database and see if both the MDW server as well as the other one show up in results

SELECT DISTINCT instance_name

FROM core.source_info_internal

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

SQListic SSCertifiable Points: 6904 More actions · Answer 2

Hi Robert,

Thanks for the reply. I ran the query, I was not able to see any other instance name except the server that MDW is hosted on. Also, my sql agents jobs are failing. I think this is because of accessibilty, Thanks for guiding me

Currently I am following this link

http://technet.microsoft.com/en-us/library/bb933864.asp

HELPED

Robert Klimes SSCoach Points: 17865 More actions · Answer 3

what error are the sql agent jobs showing?

The most probable issue is the service account that sql agent is running under does not have access to write the the MDW database.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

SQListic SSCertifiable Points: 6904 More actions · Answer 4

I can see them as access denied issues.

On Centralised database(MDW-Primary Server) I can see all jobs running sucessfully, but all jobs on other server(Secondary) where data collection is enabled are failing.

Do i need to have same AD account that runs SQL Agent on both servers or do i need to create proxy account and run all jobs on Secondary server ?

Can you explain how to setup these jobs on secondary server.

Thanks for looking into this

Robert Klimes SSCoach Points: 17865 More actions · Answer 5

you dont need to have the same AD account running sql agent. What you will need is to grant permission to the SQL Agent service account on your remote server to the MDW database. I think you will need to add the remote sql agent user to the mdw_write database role in the MDW database.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

SQListic SSCertifiable Points: 6904 More actions · Answer 6

My SQL agent on secondary serevr is running on local system , how can i grant SQL agent of Secondary server to have access to MDW database on primary server?

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

On the target MDW database you have to add to the mdw_writer role the windows user that runs the data collection process on the source instances. If you want to control under which account the collection set runs, you can set up a proxy account.

-- Gianluca Sartori

Robert Klimes SSCoach Points: 17865 More actions · Answer 8

spaghettidba (4/18/2014)
If you want to control under which account the collection set runs, you can set up a proxy account.

Because your sql agent account is local system on the secondary, you will need a proxy account(http://technet.microsoft.com/en-us/library/ms175834.aspx)

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

frederic.chapelon SSC Eights! Points: 851 More actions · Answer 9

Hi guys

I'm reaching out because I think I reached my limits and I have no idea of what's happening

I'm trying to setup a MDW to monitor our company's test servers activity.

The MDW is hosted by a SQL Server 2014 (CU6) instance

At the moment, I'm trying to monitor another 2014 instance and a 2012 SP2 (CU5) instance.

I followed the wizards to create the MDW and the data collectors.

For the account, as we're using the same local account on these servers, I decided to go with it as it provides me access to the MDW server.

I should also mentioned that I applied the "fix" to remove system databases from the collection (so I can see IncludeSystemDatabases: False) in my collectors.

The SQL Server agent jobs do not fail but I cannot see any data in my MDW.

The server stats look fine to me.

For the disk usage I can see the monitored server databases but only the system databases are available. I get the error

"A data source has not been supplied for the data source DS_TraceEvents."

For the query data collector, the job returns the message "the step did not generate an output" and I cannot see any of the query stats in the MDW database.

I've been looking all over the internet (and will still do) but I was hoping one of you would have faced teh same issues.

Thanks in advance for your help

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

I strongly suggest starting another thread for your question.

Post back the url here and I promise I will try to help you.

-- Gianluca Sartori

frederic.chapelon SSC Eights! Points: 851 More actions · Answer 11

Done

the topic is there http://www.sqlservercentral.com/Forums/Topic1684348-3411-1.aspx

Thanks for the reply (and the advice 🙂 )