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:
- 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;
- Removal of the SCOM Reporting functionality (we KEEP the Data Warehouse of course!);
- Stopping of the Health Service on RMS and all MS servers;
- Stopping the Configuration and SDK service on the RMS;
- 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);
- Stop the SQL Server service (and all other related SQL services) on the old SQL Server;
- Restore of the SCOM databases OpsMgr and OpsMgrDW on the new SQL 2008 R2 SP1 server;
- Adjustment of the registry keys on the RMS, so the new SQL server is used;
- Adjustment of the registry keys on all MS servers, so the new SQL server is used;
- 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;
- Enabling SQL Broker Service on the SCOM R2 database;
- Starting all SCOM related services on the RMS and checking the OpsMgr event log for any error;
- Launching the OpsMgr Console on the RMS to see whether all is OK;
- 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;
- Installation of SCOM Reporting on the new SQL server;
- Checking SCOM Reporting by opening the SCOM R2 Console: is the Reporting Wunderbar present?;
- Checking the successful upload of data into the Data Warehouse (OpsMgr event logs of the RMS and MS servers);
- Restore of custom folders in SSRS and upload of custom RDLs to the correct Folders;
- 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.
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:
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:
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.
Adjustment of registry keys on the RMS so the correct SQL Server is referred to:
‘Save’ the changes and close the registry editor.
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.
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;
sp_configure 'clr enabled', 1;
Go to Tables > MemberDatabase and search for the record ServerName
Close the table, but stay in SQL Server Management Studio.
Enabling SQL Broker Service: See this posting of mine, Step 2.
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…
Checking the successful upload of data into the Data Warehouse on the RMS and MS servers:
Events like these indicate successful uploads of data into the Data Warehouse.
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.
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’ :).