Friday, December 2, 2011

Preparing for migrating to OM12: Moving from SQL 2005 to SQL 2008 – Part III: Phase II – The Migration

----------------------------------------------------------------------------------
Postings in the same series:
Part  I – Along came a theory
Part II The Preparations
----------------------------------------------------------------------------------

In the third posting of this series I will describe in more detail Phase II, the actual migration from SQL Server 2005 to SQL Server 2008 R2 SP1 . When you haven’t read the first and second posting of this series please do so now since otherwise you’ll be missing out the big picture here.
For the completeness here is the list of questions/steps which make up Phase II:

  1. Backup of your SCOM environment (SQL, RMS, MS servers and databases). This is step enables the fall-back scenario and not Step 5 since the SCOM R2 environment is already affected by then;
  2. Removal of the SCOM Reporting functionality (we KEEP the Data Warehouse of course!);
  3. Stopping of the Health Service on RMS and all MS servers;
  4. Stopping the Configuration and SDK service on the RMS;
  5. Backup the SCOM databases: OperationsManager and OperationsManagerDW (because no more data comes in these backups are the most current ones and won’t be outdated in any kind of way);
  6. Stop the SQL Server service (and all other related SQL services) on the old SQL Server;
  7. Restore of the SCOM databases OpsMgr and OpsMgrDW on the new SQL 2008 R2 SP1 server;
  8. Adjustment of the registry keys on the RMS, so the new SQL server is used;
  9. Adjustment of the registry keys on all MS servers, so the new SQL server is used;
  10. Adjustment of some entries on both SCOM databases on the new SQL Server so the new SQL server is correctly referred to in the database AND enabling CLR for the OpsMgr database;
  11. Enabling SQL Broker Service on the SCOM R2 database;
  12. Starting all SCOM related services on the RMS and checking the OpsMgr event log for any error;
  13. Launching the OpsMgr Console on the RMS to see whether all is OK;
  14. When all is well, starting the Health Service on the MS servers, one by one and checking the OpsMgr event logs on those servers whether all goes well;
  15. Installation of SCOM Reporting on the new SQL server;
  16. Checking SCOM Reporting by opening the SCOM R2 Console: is the Reporting Wunderbar present?;
  17. Checking the successful upload of data into the Data Warehouse (OpsMgr event logs of the RMS and MS servers);
  18. Restore of custom folders in SSRS and upload of custom RDLs to the correct Folders;
  19. Checking whether all Reports show up again in SCOM (this might take an hour or so).

As stated before, it won’t be a detailed step-by-step guide , but I will highlight the most crucial steps. In the list above the steps which are printed in blue will be explained by me in more detail. Let’s start since there is a lot to share.

Step 2
The SCOM R2 Reporting functionality must be removed now. Of course we KEEP the OperationsManagerDW database!

Open the SSRS server hosting the SCOM Reporting component and log on with admin permissions. Go to Control Panel > Add or Remove Programs and select System Center Operations Manager 2007 R2 Reporting Server > Remove > Yes:
image 
image
image

The removal of SCOM R2 Reporting takes a few minutes. When it’s finished it won’t show a dialogue. The ‘installer screen’ just disappears. Open the SCOM R2 Console and the Reporting Wunderbar should be gone now:
image

Step 5
In order to create the backups one can use SQL Server Management Studio. This tool uses the correct VSS writer so a viable backup will most certainly be created. Also assure the backup is validated:
image  

Step 7
Restoring the SCOM R2 databases to the new SQL 2008 R2 SP1 Server: Use the same medium for this procedure as you used for creating the backups. In this case SQL Server Management Studio. Connect to SQL instance and log on with an account which has sufficient permissions.

Click right on Database > Restore Database. A Wizard is started now. Select the option From device and click on the selection button (red circle):
image

Select for Backup Media File > Add. Select the backup file of the OperationsManager database > OK > OK
image 

Select the option Restore. Option To database: select from the dropdown menu OperationsManager. Option: To a point in time select Most recent possible > OK.
image

Restore runs for a while and when all is OK:
image

Repeat the same steps for the Data Warehouse database:
image 

Step 8
Adjustment of registry keys on the RMS so the correct SQL Server is referred to:

Log on to the RMS with local admin permissions and open the registry editor. Go to: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup.
image

Change the key DatabaseServerName so the new SQL Server is referred to:
image

‘Save’ the changes and close the registry editor.

Step 10
Adjustment of some tables in both SCOM databases so the correct SQL Server is referred to. Open SQL Server Management Studio, logon with admin permissions on the SQL instance.

OperationsManager database
Go to Tables > MT_ManagementGroup > click right and select the option Edit Top 200 Rows. Search for record SQLServerName_6B1D1BE8_EBB4_B425_08DC_2385C5930B04
image

Modify it so it refers to the new SQL Server:
image

Close the table.

Enabling CLR on the OperationsManager database
Select the database > click right > new query > copy this code

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

> Execute:
image

OperationsManagerDW database
Go to Tables > MemberDatabase and search for the record ServerName
image

Modify it so it refers to the new SQL Server:
image

Close the table, but stay in SQL Server Management Studio.

Step 11
Enabling SQL Broker Service: See this posting of mine, Step 2.

Step 15
Installing SCOM R2 Reporting:

First and foremost, check the correct working of SSRS by opening this URL on the SSRS instance in IE (running with elevated permissions!): http://localhost/Reports:
image

When this screen is shown all is well. Otherwise fix any issues before continuing.

Before starting the installation first go through the steps mentioned in KB2425714. Otherwise the installation of SCOM Reporting won’t succeed.

Run the installation media of SCOM R2 with elevated permissions and an account which has admin permission locally on the SSRS server, within SCOM and permissions within the SQL instance hosting the Data Warehouse database. Otherwise the installation will fail…
image

Go through the setup wizard but don’t forget to DESELECT the Data Warehouse database:
image

Enter all the required information and later on SCOM R2 Reporting is installed successfully:
image

And:
image

Step 17
Checking the successful upload of data into the Data Warehouse on the RMS and MS servers:

Check the OpsMgr event log on these servers for this kind of events: Category: Data Warehouse, EventID: 31554 (there are others as well).
image

Events like these indicate successful uploads of data into the Data Warehouse.

Step 18
Recreating the custom folders in SSRS and uploading the RDLs of the custom reports:

Recreate the custom folders in SSRS by using the web interface for SSRS http://localhost/Reports. Create the required folders:
image

Upload the RDL files of the custom Reports into the correct folders. This is also done within the web interface for SSRS.

Conclusion
As you can see, migrating from SQL Server 2005 to SQL Server 2008 R2 SP1 CU#2 isn’t something to be taken lightly. However, it can be done and in a good manner as well.

Planning and preparation are key here. And don’t hesitate to practice it in a lab environment. No licenses are required since you can download all the required software as trial versions and have a go at it. Test it, document it and try it. So you get the hang of it and know what you’re doing.

This way you can migrate safely to SQL Server 2008 R2 SP1 CU#2 for SCOM R2 without too much hassle.

In the last posting of this series I will describe the last steps, like cleaning up the ‘mess’ :).

10 comments:

devi said...

Hi,

Why you didn't uninstall the Opsmgrdw component from the SCOM reporting server? could you please clarify

devi said...

Hi,

As per the technet, if we move the Opsmanager DW database we have to change the registry in the reporting server. However you have not mention in your Blog. I could understand that you removed the SCOM reporting server role at the beginning. therefore it wont require to update the registry key. Could you please confirm the same? (registry edit is not required for Opsmgr DW after restore the DB to new server)

Marnix Wolf said...

Hi Devi.

Since it is a migration, you want to preserve the data warehouse. Therefore the DW db is kept and not removed.

Cheers,
Marnix

Marnix Wolf said...

Hi Devo.

As the series tells it is all about MOVING to SQL server 2008 R2 from SQL 2005. Not an inplace ipgrade nor just a move of the DW db ro another instance. Therefore this approach is chosen and with installing SCOM Reporting on the new SQL server, the reg keys are created by the installer so no need to repeat that.

Cheers,
Marnix

devi said...

Thanks for your explanation, Basically I am Appln developer and now i am working in SCOM. So i could say that i am newibe in the SCOM.

We have the dedicated server for OPsmgrDW and OPSmgracs database and one reporting server in our environment. We are plan to upgrade to SQL 2008 as OM 2012 supports only SCOM DB sql 2008.

I just checked the SQL DB server where SCOM DB's are hosted and i couldnot find the SCOM reporting server component in the server. So if we migrate to SQl 2008, no need to install the SCOM DW component. Please clarify. (I may guess that my ex colleague may install the DB via DB create wizard)

Thanks in Advance

Marnix Wolf said...

Hi Devi.

Welcome to the world of SCOM. Guess as an app developer there is whole world of Management Pack authoring for you to explore :).

About SCOM Reporting. It is coreect you don't find it on the SQL servers hosting the databases since SCOM Reporting is installed on the server hosting the SSRS instance. My guess is when you check the SSRS server, hosting Reporting services for SCOM, you will find the SCOM Reporting instance there.

Cheers,
Marnix

jos Boers said...

I recently used this procedure and got a problem with the reporting afterwards.
When using existing report definitions (favorite or others)we get a report with the data until 8/8/2012.
When using new report definitions we get a report with the data from 8/8/2012 till today
The reason behind this is that there are 2 Management Groups in our datawarehouse :
ManagementGroupRowId ManagementGroupGuid OwnerManagementGroupInd ManagementGroupDefaultName ConnectConfirmedDateTime ConnectRejectedind ConnectRejectedDateTime WriterLoginName DWCreatedDateTime DWLastModifiedDateTime
2 5D9FEAF0-2A34-44BC-A2FB-65BD614EF21C 0 ISZ SCOM Mgmt Group 2010-06-21 13:19:02.030 1 2012-08-08 06:12:38.333 ISZ\CJH589 2010-06-21 13:19:01.983 2012-08-08 06:12:38.333
3 4027523F-319A-0EB8-D87A-AD5180A8F6AC 1 ISZ SCOM Mgmt Group 2012-08-08 11:19:24.330 0 NULL ISZ\CJH589 2012-08-08 11:19:24.233 2012-08-08 11:19:24.233
ManagementGroupRowId ManagementGroupGuid OwnerManagementGroupInd ManagementGroupDefaultName ConnectConfirmedDateTime ConnectRejectedind ConnectRejectedDateTime WriterLoginName DWCreatedDateTime DWLastModifiedDateTime
2 5D9FEAF0-2A34-44BC-A2FB-65BD614EF21C 0 ISZ SCOM Mgmt Group 2010-06-21 13:19:02.030 1 2012-08-08 06:12:38.333 ISZ\CJH589 2010-06-21 13:19:01.983 2012-08-08 06:12:38.333
3 4027523F-319A-0EB8-D87A-AD5180A8F6AC 1 ISZ SCOM Mgmt Group 2012-08-08 11:19:24.330 0 NULL ISZ\CJH589 2012-08-08 11:19:24.233 2012-08-08 11:19:24.233

devi said...

Hi,

Is it mandatory to uninstall the scom reporting services before moving the data warehouse database?

Please advice

Thanks

Marnix Wolf said...

Hi Devi.

No it isn't but makes it far more easy, ehich is based on my personal experiences of course.

Cheers
Marnix

devi said...

because i just did that and facing issues with event id of 31552 and 31553 that failed to store event data set in DW database

Workflow name: Microsoft.SystemCenter.DataWarehouse.StandardDataSetMaintenance

Instance name: Event data set.

also when i ran the DW properties , it didn't show any of the events data but DB has around 800 GB for events.