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

management-data-warehousemigrationsql serversql-server-2012

Is there a defined procedure to follow for moving a SQL Server 2012 Management Data Warehouse to a new server\instance?

We intend to setup MDW on a test SQL Server instance first, but will then want to migrate it to a production installation. I can't find any information on this. Two points which are concerns are:

  1. To migrate the databases without losing data.
  2. How to update the registered instances to report their data to the new MDW instance.

Best Answer

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:

  1. Stop all data collectors in the monitored server (production in my case)
  2. Back up the MDW database, optionally put it offline (old QA server)
  3. Restore the MDW database to the new host instance (new QA server)
  4. 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.

Related Question