Here we go with the detailed steps of moving the MOM DB to a new SQL server, In my case I assumed that the server will be hosted on SQL active/active cluster, the steps are similar for SQL on stad alone server.
there is 2 tricks in the above setup, First Correct the SQL connection in the SQL reporting or it will not wwork, the second is to make sure that the disk that will host the MOM DB and Logs is a part of SQL server dependencies or the MOM DB will not be created.
1- Uninstall all other MOM Server components (and any MOM Agent of the same management group) that reside on the destination computer.
2- On the designated active SQL cluster Node create the MOM database using the momcreatedb.exe utility (this utlity could be found on the following path: “MOM CD”:\ SupportTools\x86).
3- Stop the MOM service on all MOM Management Servers. If MOM to MOM Product Connector (MMPC) is installed, stop the momcomm service.
4- Back up the current OnePoint database to a file.
5- On the destination computer, Restore the SQL database using the backup created in step 4.
6- Grant "db_owner" privileges to the DAS account. The DAS account was specified when you installed the original MOM database component. To grant these privileges, do the following:
a. In the SQL Server Enterprise Manager navigation pane, expand the SQL instance associated with this database.
b. Expand Security, and click Logins.
c. Right-click the DAS account displayed, and open the properties page.
d. From the Properties page, select the Database Access tab.
e. Select the OnePoint checkbox.
f. In the Database Roles for OnePoint pane, select the "db_owner" check box. Click OK.
7- On the first MOM Management Server start Regedt32.exe, and change the following registry values from the current SQL Server Instance to the new SQL Server Instance SQLservername\instanceame:
· HKEY_LOCAL_MACHINE\Software\Mission Critical Software\DASServer\DataSource Value
· HKEY_LOCAL_MACHINE\Software\Mission Critical Software\Onepoint\Configurations\\Operations\Database Value
8- Restart the MOM service.
1- On the Designated SQL active node install IIS, and enable ASP.Net
2- Install SQL Reporting.
3- Install MOM Reporting As using Normal installation steps.
4- After the installation complete open MOM reporting console.
5- Select the SCDW link
6- Edit the Connection string by Removing the . and replace it with SQLservername\instanceame.
7- Restart IIS service.
8- Edit the MOM DTS package job using the following steps:
· Click Start, then select Settings, Control Panel, Administrative Tools, Scheduled Tasks.
· Right-click the SystemCenterDTSPackage task and select Properties.
· In the "Run" text-box change the /srcserver: parameter to the destination computer as in the example below:
MOM.Datawarehousing.DTSPackageGenerator.exe /silent /srcserver: SQLservername\instanceame /srcdb:OnePoint /dwserver: SQLservername\instanceame /dwdb:SystemCenterReporting /product:"Microsoft Operations Manager"
· Click OK and reconfirm the password.
Regards