Configuring SQL Server management data warehouse (MDW) for data collection

management-data-warehouse

I am configuring a data warehouse on an SQL Server to collect data. However, the process is not finishing and it stays as per below for hours until it is manually stopped:

Have you ever encountered this? I searched online but found no similar issues and from YouTube videos it seems that the process finishes quite quickly.

Furthermore, once this is done, can you collect data from a VM that is on a different domain from the VM where the management data warehouse DB is (no trusting exists between the two domains).

Best Answer

As mentioned by you, that the repository server from where you running the MDW is in different domain than the target computer and no trust exist, probably the reason you might not see the job running it's step for connecting. Try catching some error if possible from error logs or event viewer.

Try using repository server running on same domain or create a trust between 2 and give it a shot.

Related Solutions

Sql-server – Migrate Management Data Warehouse to a New Instance

I understand this is an old thread, but it was the first hit when I searched "how to move mdw database" on Google.

I had configured MDW (Data Collection) on a SQL Server 2008 R2 production server, and I hosted the MDW database in a QA instance. Later on I had to move the QA instance to a different machine, with a different name (migration and name change mandatory for business reasons) and I wanted to keep the MDW data that was already collected. I could not find anything on that, so I gave this procedure a try and it worked for me:

Stop all data collectors in the monitored server (production in my case)
Back up the MDW database, optionally put it offline (old QA server)
Restore the MDW database to the new host instance (new QA server)
Re-configure the monitored server to use the MDW database in its new location (Right click on Data Collection, select Configure Management Data Warehouse. Select "Set up Data Collection" and click Next. Click on the ellipsis button and connect to the new instance, then from the drop down list select the MDW database that you restored in the previous step. Click Next, then Finish)

The data collectors should start automatically after the Configure Management Data Warehouse wizard finishes, and you can view the MDW reports in the new database location.

Then again, this worked for me in a SQL Server 2008 R2 environment, I am not sure if it works in SQL Server 2012.

Sql-server – Separating system databases from MDW SQL Server 2008 R2

Join via snapshots_internal to source_info_internal which has the server instance name, eg

SELECT du.*, sii.instance_name
FROM [snapshots].[disk_usage] du
    INNER JOIN core.[snapshots_internal] si ON du.snapshot_id = si.snapshot_id
        INNER JOIN [core].[source_info_internal] sii ON si.source_id = sii.source_id
WHERE du.database_name = 'tempdb'

Best Answer

Related Solutions

Sql-server – Migrate Management Data Warehouse to a New Instance

Sql-server – Separating system databases from MDW SQL Server 2008 R2

Related Question