Creating a new Management Data Warehouse and removing the old one

Question

Post reply

Creating a new Management Data Warehouse and removing the old one

Draelith

Ten Centuries

Points: 1140
More actions
February 3, 2013 at 7:56 pm

#288791

When I took on the Role of DBA, i inherited some leftovers from the previous DBA. One such leftover was the fact that MDW was configured. The issue is the fact that the MDW database was stored on the C:\ of my server. Incredibly stupid.
So I have started running the configuration wizzard and made a new MDW database on the correct drives. But I'm not sure on what to do next. Will the wizard now setup the data collection jobs to point to the new database?
How do I remove the old database now? When is it safe to do so?
Thanks!

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

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

Query Shepherd SSCrazy Points: 2988 More actions · Answer 1

Why don't you just query the new MDW-Database and take a look a the timestamp of the latest entries?

Greetz sql pizza

Greetz
Query Shepherd

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 2

Why create a new database? You could have just detached, copied files and attached again. The MDW collectors would have continued using the same database.

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

Query Shepherd SSCrazy Points: 2988 More actions · Answer 3

Richard Fryar (2/4/2013)
Why create a new database? You could have just detached, copied files and attached again. The MDW collectors would have continued using the same database.

Exactly!

Just stop the corresponding collector jobs and deattach, copy and move an reattach!

Greetz
Query Shepherd

Draelith Ten Centuries Points: 1140 More actions · Answer 4

Really? OK, I'll give that a go. Hopefully not too late to point it back to the existing database.

Thanks! 🙂

Draelith Ten Centuries Points: 1140 More actions · Answer 5

Well I have successfully detached, moved the database mdf and ldf files and reattached. So far so good. Now to just monitor the data collection tasks and see what happens.

Draelith Ten Centuries Points: 1140 More actions · Answer 6

First issue:

The Data collection is failing because it's trying to go to the new database i created and I've set it to single user mode. I have since gone back to the wizard and pointed it back to the original database (on the new drives) but it still tries to go to the new database I created.

Should I delete the jobs, stop data collection and then try to setup data collection again?

Query Shepherd SSCrazy Points: 2988 More actions · Answer 7

Hi Drealith,

first of all...if you click on the edit-button you can always edit your last posting and don't need to double- or triple post.

If you chose another Database to log to, you will have change it back to the old DB on the new drive. Logical, isn't it? If you can't do this, then set it up new.

Greetz

sql pizza

Greetz
Query Shepherd

Draelith Ten Centuries Points: 1140 More actions · Answer 8

That's the issue. I ran the wizard again and pointed it to the correct database, but the update still tries to connect to the database I created after the original.

I may try and delete the jobs and then run the wizard again in hopes it recreates them. I'll take a backup first just in case it doesn't.

Query Shepherd SSCrazy Points: 2988 More actions · Answer 9

Query Shepherd

SSCrazy

Points: 2988

February 7, 2013 at 6:58 am

#1585410

Got it working now?

Greetz
Query Shepherd

Draelith Ten Centuries Points: 1140 More actions · Answer 10

Yes. Can't tell the exact steps that made it work but I got it working. It now points to the correct database on the new server. Nice to know that I can simply detach and reattach in the future if need be.

Thanks for everyone's suggestions.