Recently I got a question of a customer to move the Opsdb Datawarehouse (DW) to another drive because the disk on which it was originally installed was not big enough. In fact they wanted to move the DW to an iscsi disk to boost performance.
To verify whether there would be an issue or it would be a straight forward move I did some browsing on the biggest manual out there… The internet!
However all that came up were actually moves from one server to another but not from one drive to another on the same server…
I did some testing in my lab and thought I ‘d share the outcome with you.
First of all this is your DW you are tempering about. Make sure you have proper backups of your db and read the entire blog before proceeding. Just to be on the safe side. It would be a shame that you lost all your data older than 8 days (if this is your grooming setting) because of a bad manipulation.
Ok enough said. Let’s get things started.
These are the steps I followed and in my case everything went smoothly without any problems.
First of all (again) take backups of your dbase and secondly plan a SCOM down time. To be absolutely sure that there’s no interference or blocking of the DW dbase you need to shutdown your RMS, any MS and GW servers in your environment (or at least in the management group of which the DW is part of). Some sources just drop the connections to the dbase which is an option as well but I prefer the first option. In my opinion it’s safer to do it like this.
Connect to the SQL server where your DW and open up the Microsoft SQL Server Management Studio:
Open up the connection to your DW. In my case it is residing on my VSERVER05.
Again better safe than sorry. Backing up!
The DW can be very big so it could be that it needs some time to perform the backup. When it’s finished.
At this point shutdown your environment. This means RMS, MS and GW’s. This sounds like a draconic measure but it ensures that your environment is completely shutdown and no queries are made to the dbase.
When this is done we can proceed to move the dbase
Take the DW offline by right clicking it and choosing “Take Offline”
A small dialog will popup and eventually of all goes well it will tell you the dbase is offline successfully. Notice the red arrow on the DW dbase.
Now take the ReportServer$OpSDBDW and ReportServer$OPSDBDWTempDB offline as well. Note that these dbases can have a different name in your environment or could not be present.
Note: My OpsdbDW is installed in a separate SQL instance. Be cautious with restarting your SQL service as this impacts all dbases under this instance.
When all the dbases are down they can be detached. This is done by right clicking the dbase > tasks > “detach”.
Choose the option to drop the connections to the dbase and hit OK.
Now we can copy (yes copy) the data. Again better safe than sorry and make a copy of the data rather than moving it.
After the copy has been done we are going to attach the copied DW to the SQL
Right click Databases and click Attach:
Select your dbase and attach:
In this case I’m moving my DW from E: to F: drive.
NOTE: It’s not automatically selecting the correct log file. Make sure you select it manually by clicking on the icon behind the path in the lower section.
When the attach is completed successfully you will dbases are moved to your new drive.
Start your SCOM environment again by starting your RMS first and then your MS and or GW servers you might have.
Just to be on the safe side verify whether you’re able to generate a report in the reporting view of your console with data older than 7 days (when your grooming settings are different you need to modify this to make sure you have a report with data older than your grooming setting.
If all goes well you now have successfully moved your dbase to another drive and you are free to delete the initial copy on your old location.