MOM 2005: How not to migrate your reporting databases

Winging it is never a good idea, but sometimes, when the documentation is lacking, one has no other choice, right?

We made a couple of serious mistakes when we migrated our reporting databases from one server to another recently, and I thought I would share them, so you don’t have to make the same mistakes.

The first mistake was to do this without guidance. No, seriously.

After migrating the OnePoint database flawlessly, and reinstalling Reporting Services recently, we really thought this one would be a breeze – after all, how much different could it be from reinstalling RS? We tested our theory in a lab, and it worked fine. In production, things went a little differently.

On the old server, we stopped the SQL services, and copied the MDF and LDF files of the ReportServer, ReportServerTempDB and SystemCenterReporting to the new server. Since our SystemCenterReporting db was a little big and the old server was a virtual one, this took a little while. In the mean time, we installed SQL 2000 Reporting services on the new server – but I suspect we missed something when we did this – and then installed the MOM reporting components – this at least created the SCR db, so I think it worked.

Then, we stopped the SQL services on the new server, and made backups of the database files that were created in the previous steps. We then replaced these files with the files we copied from the old server, and started SQL again.
Great, the databases came up successfully.
Reporting, on the other hand, was not quite there.
Ok, next up, change the SCDW connection in Reporting itself to point to the new server. Great. The reports are there. *phew*
But the SQL services on the old server had been started again – damn MOM agent is too efficient *grin*. Stop the SQL services on old server, reporting is fooked. Ok. This one we know how to fix.
Run rsconfig again. Stop the MOM agent on the old server, then stop the SQL services. Ok, reporting works again, after having to assign an additional permission here and there. Or, I should say, we can run reports manually. Subscriptions are simply not there.
Fire up SQL on old server, export the jobs to a text file and run on the new server. Ok, goodie, this is starting to look better. Another permission tweak here and there. Now, we are almost in business. We can see the schedules – just still not sure if they are going to execute.
Who needs reporting anyway? This seems like a better idea anyway: