OM12 SP1 Known Issue: Data Warehouse Database Gets Funny Names & Placement

Even though the Release Notes for OM12 SP1 are clear about the file names for the Data Warehouse when running a clean install of OM12 SP1, there is another caveat to reckon with as well.

The Caveat
In situations the SQL Server isn’t using the default locations for the databases, log files and temporary database, the previous mentioned issue goes a bit further.

At a customers location the SQL file locations were customized like this:

• User database directory: D:\SQLDatabases;
• User database log directory: E:\SQLLogs.

Normally when OM12 is installed a Data Warehouse database is created with the name OperationsManagerDW. The files are placed in the correct folders. But now this happened:

• The database file was named: SQLDatabases.mdf and located in the folder D:\SQLDatabases;
• The log file was named: SQLLogs.ldf and located in the folder E:\SQLLogs.

Ouch! That isn’t nice at all. Yes, OM12 SP1 functioned correctly but it’s not the way it should be. So it was time for a remediation.

The Remediation
This is a straight forward procedure. However, when you’re not familiar with SQL Server don’t hesitate to involve a colleague who has more knowledge and experience with it. Watch and learn so you can perform similar tasks like these in the future.

High level steps:

1. Open SQL Server Management Studio with SA permissions;
2. Select the Data Warehouse database (OperationsManagerDW) and run a backup of it using these settings:
   1. General: Full backup;
   2. General: Backup to disk;
   3. General: File name: OperationsManagerDW.bak;
   4. Options: Overwrite all existing backup sets;
   5. Options: Reliability: Verify backup when finished (this one is VERY important since it tells you whether the backup is valid or not).
3. When the backup and the verification are ended successfully, go to all OM12 SP1 Management Servers and stop the OM12 services:
   1. System Center Data Access Service;
   2. System Center Management;
   3. System Center Management Configuration.
4. Remove the Data Warehouse database (OperationsManagerDW) in SQL Server Management Studio by right clicking it and selecting delete;
5. Wait until the database is removed successfully. Now it’s time to restore the backup made in Step 2;
6. In the Restore database screen select the page Files. Checkmark the option Relocate all files to folder. (This is how it looks in SQL Server 2012 SP1, in SQL Server 2008 R2 it might look different!)
   
   The correct locations should be shown now. Otherwise correct them manually;
7. Now it’s time to modify the filenames since these are still wrong:
   
8. You can adjust them by typing the correct names:
   
9. Restore the database as you normally would restore a SQL database;
10. When finished successfully, restart the OM12 services you stopped earlier in Step 3. Check the OpsMgr event log on the OM12 Management Servers but they should be fine.

Recap
Since this is a known issue, run this procedure right after you’ve installed the very first OM12 SP1 Management Server. Saves you a lot of hassle and since OM12 SP1 is still under construction it won’t affect any thing. The procedure is straight forward and not any kind of rocket science.

Update
Fellow MVP and much respect friend Bob Cornelissen has also written a posting about how to solve this issue. He uses another approach (drop and re-attach). Want to know more? Go here. Thanks Bob for sharing!