SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a new Management Data Warehouse and removing the old one


Creating a new Management Data Warehouse and removing the old one

Author
Message
Draelith
Draelith
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 127
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!
Query Shepherd
Query Shepherd
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 454
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
Richard Fryar
SSC-Addicted
SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)SSC-Addicted (495 reputation)

Group: General Forum Members
Points: 495 Visits: 1172
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
Query Shepherd
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 454
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
Draelith
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 127
Really? OK, I'll give that a go. Hopefully not too late to point it back to the existing database.

Thanks! Smile
Draelith
Draelith
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 127
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
Draelith
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 127
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
Query Shepherd
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 454
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
Draelith
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 127
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
Query Shepherd
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 454
Got it working now?

Greetz
Query Shepherd
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search