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:
 

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:

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:
  

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):

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

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

Restore runs for a while and when all is OK:

Repeat the same steps for the Data Warehouse database:
 

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.

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

‘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

Modify it so it refers to the new SQL Server:

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:

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

Modify it so it refers to the new SQL Server:

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:

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…

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

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

And:

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).

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:

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’ :).