Monday, September 13, 2010

How to move the OperationsManager Data Warehouse Database to a new SQL server

 

Stop all services on RMS and MS servers

a. On opsmgr-rms2 stop the following
·         Opsmgr config
·         Opsmgr sdk
·         Opsmgr health
b. On opsmgr-ms2 stop the following
·         Opsmgr heatlh

Backup database opsmgr-sql2

a. Backup the following
D:\backup\OperationsManagerDW.bak

Uninstall the Reporting Data Warehouse component opsmgr-sql2

A.      Click Start, click Control Panel, and then select Add or Remove Programs.
B.      In the Add or Remove Programs dialog box, select System Center Operations
Manager 2007Reporting Server, and then select Change.
C.      c. In the System Center Operations Manager 2007 Reporting Setup Wizard, on
the Operations Manager 2007 Maintenance page, select Modify, and then click Next.
D.      d. On the Custom Setup page, configure the Data Warehouse component with
the This component will not be available option.
E.       e. Complete the wizard.
Note
This does not physically remove the OperationsManagerDW database from SQL
Server.

4. Delete the OperationsManagerDW database opsmgr-sql2

A.      In Microsoft SQL Server Management Studio, navigate to Databases.
B.      Right-click OperationsManagerDW, and then select Delete.
C.      c. In the Delete Object dialog box, ensure that the Delete backup and restore
history information for databases and the Close existing connections options are
both checked

5.Install the Reporting Data Warehouse component Opsmgr-sql1

A.      run SetupOM.exe to install the Reporting Data Warehouse component as
follows
B.      On the System Center Operations Manager 2007 Setup page, select Install
Operations Manager 2007 Reporting.
C.       the System Center Operations Manager 2007 Reporting Setup wizard, on
the Custom Setup page,
·         configure Data Warehouse component for installation
·         Configure Reporting Server with the This component will not be
available option
D.      d. Database instance is opsmgr-sql1
E.       e. Database Files Location
·         D:\Data for both data and log file locations

6.Delete the New OperationsManagerDW On the new Data Warehouse server opsmgr-sql1

A.      In Microsoft SQL Server Management Studio, navigate to Databases.
B.       Right-click OperationsManagerDW, and then select Delete.
C.      In the Delete Object dialog, ensure that the Delete backup and restore history
             information for databases and the Close existing connections options are both checked.

7.restore the OperationsManagerDW From opsmgr-sql2 to opsmgr-sql1

A.      Copy the backup of the database from \\opsmgr-sql2\d$\restore
B.      b. On SQL1 open SQL Server Management Studio
·         In the left Colum Expand opsmgr-sql1 and right click on databases
·         Select restore database
·         Source for restore (do this before setting destination)
C.       Select "From device" click on the navigate button on the right
D.      Select file for Backup Media and say add
E.      Navigate to D:\Restore and select OperationsManagerDW.bak
               and say ok twice
F.       4. Select the most recent backup and say ok.
Destination for Restore
A.      Click on the dropdown arrow beside "To database"
B.      Select OperationsManager
C.       Place a checkmark beside OperationsManagerDW-Full Database Backup
D.      4. Click on OK
E.      5. Progress is shown in the bottom left corner. When complete
                   there will hopefully be a successful window popup. Say ok.
F.       6. You are done restoring the database

8. Add SDK Account

On opsmgr-sql1 hosting the OperationsManagerDW database, add the correct
permission for the Login of the Root Management Server on which the SDK
Account is running, as follows:
A.      Open Microsoft SQL Server Management Studio, and in the Object Explorer
pane, navigate to Security and then expand Logins.
B.      b. Add the SDK Account
·         Right click on Logins and select New Login
·         For login name use domain\mom_sdkconfig
·         Use windows authentication
·         Say ok
C.      Settings for SDK Account
·         Open Microsoft SQL Server Management Studio, and in the Object
Explorer pane, navigate to Security and then expand Logins.
·          Right click domain\mom_sdkconfig select properties
·          Under "select a page" on the left side click on User Mapping
D.      In the right window under "Users mapped to this login: place a
check beside OperationsMangerDW.
E.       In the right window under "Database role membership for:
OperationsManageDW" make sure the following items are checked:
·         OpsMgrReader
·         db_datareader

9.Add Data Warehouse Read account

On opsmgr-sql1 hosting the OperationsManagerDW database, add the
correct permission for the Login of the Root Management Server on which
the Data warehouse write Account is running, as follows:
A.      Open Microsoft SQL Server Management Studio, and in the Object Explorer
pane, navigate to Security and then expand Logins.
B.      Add the Data Warehouse read Account
·         Right click on Logins and select New Login
·         For login name use domain\DW_read_Account
·         Use windows authentication
·         Say ok

10. Settings for Data Warehouse write Account

A.      In the right window under "Database role membership for:
OperationsManagerDW" make sure the following items are checked:
·         db_datareader
·         OpsMgrReader

11.Add Action Account

On opsmgr-sql1 hosting the OperationsManagerDW database, add the correct
permission for the Login of the Root Management Server on which the Action
Account is running, as follows:
A.      Open Microsoft SQL Server Management Studio, and in the Object Explorer
pane, navigate to Security and then expand Logins.
B.      Add the Action Account
·         Right click on Logins and select New Login
·         For login name use domain\Action_Account
·         Use windows authentication
·         Say ok
C.      Settings for Action Account
·         In the right window under "Database role membership for:
OperationsManagerDW" make sure the following items are checked:
·         OpsMgrWriter
·         db_owner

12. Start the OpsMgr SDK Service On the Root Management Server, opsmgr-rms2

13. Modify the data source on SQL Server Reporting Services server Opsmgr-sql2

14. Log on to opsmgr-sql2.

A.      In Internet Explorer, open http://opsmgr-sql2/reports
B.      On the SQL Server Reporting Services Home page, ensure that you are
viewing the Contents page. Select Show Details.
D.      In the list that is displayed, click Data Warehouse Main.
E.       In the Data Warehouse Main properties page, in the Connection string text
box, change the name of the database server to opsmgr-sql1.
F.       Click Apply.

15.Update the registry to point to new Data Warehouse server Opsmgr-sql2

A.      Locate the key HKEY_LOCAL_MACHINE\Software\Microsoft\MicrosoftOperations Manager\3.0\Reporting.
B.      b. Create the following key as a string value
·         Name: DWDBInstance
·         Data: opsmgr-sql1

16. Configure the OperationsManager database with the name of the new Data

Warehouse server as followsOpsmgr-sql1

A.      On the server that hosts the OperationsManager database, open SQL Server
Management Studio and navigate to Databases, OperationsManager, and then to
Tables.
B.      Right-click dbo.MT_DataWarehouse, and then select Open Table.
C.      Change the value in the
MainDatabaseServerName_16781F33_F72D_033C_1DF4_65A2AFF32CA3
column to the name of the new Data Warehouse server opsmgr-sql1.
D.       Close SQL Server Management Studio to save your changes.

17.Configure the OperationsManagerDW database with the name of the new Data Warehouse server as follows Opsmgr-sql1

A.      On the new Data Warehouse server, open SQL Server Management Studio and
navigate to Databases, OperationsManagerDW, and then to Tables.
B.      b. Right-click dbo.MemberDatabase table and select Open Table.
C.      c. Change the value in the ServerName column to the name of the new Data
Warehouse server. Opsmgr-sql1          
D.      d. Close SQL Server Management Studio to save your changes.

18.Restart services Opsmgr-rms2, opsmgr-ms2

A.      On the Root Management Server,opsmgr-rms2
·         OpsMgr Config Service
·         OpsMgr Health Service
B.      b. On all Management Servers, opsmgr-ms2
·         OpsMgr Health Service.

19. To verify a successful move of the OperationsManagerDW database

a.       Verify that you can successfully run a report from the console.
b.      Ensure that the health state of all management servers in the management
group are Healthy.
If the health state of any management server is Critical, open HealthExplorer,
expand Availability - <server name>, and then continue to expand until you can
navigate to Data Warehouse SQL RS Deployed Management Pack List Request
State. Check the associated events to determine if there is an issue accessing
the OperationsManagerDW database.
c.       Check operating system events:
·         Open the operating system's Event Viewer. Navigate to Event Viewer,
and then to Operations Manager.
1.       In the Operations Manager pane, search for events with a
Source of Health Service Module and a Category of Data Warehouse.
The move was successful if event number 31570, 31558 or 31554
exists.
There is an issue accessing the OperationsManagerDW database if
event number 31563, 31551, 31569, or 31552 exists.
d.      Check events in Operations Manager:
·         In the Operations Manager console, select Monitoring.
·         Navigate to Monitoring, Operations Manager, Health Service Module
Events, and then to Performance Data Source Module Events.
Search the Performance Data Source Module Events pane for events with a Date and Time
that is later than the move.
There is a problem with the OperationsManagerDW database if events with a Source of
Health Service Module and an Event Number of 10103.