3

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.
|improve this question
  • 1
    I wouldn't bother migrating it, especially if it's just testing. I WOULD document any customizations and fixes you make to it and script them out. I've used MDW pretty extensively and after our environment has grown to a certain point, we actually ended up abandoning it. It doesn't scale well and there are some serious performance issues that occur while running the maintenance job. In SQL 2012 they broke one of the views causing the performance of the Query Stats report to go from seconds to literally minutes. I had to revert it back to the 2008 R2 definition in the 2012 database. – Alf47 Jul 2 '14 at 1:43
  • 1
    I would agree with the not migrating, because the data held in it is applicable only to that instance, eg disk space, query performance etc, it's not relevant to your new environment. Set up a clean one. – wBob May 3 '15 at 21:28
3

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.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.