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

----------------------------------------------------------------------------------
Postings in the same series:
Part   I – Along came a theory
Part III – The Migration 
----------------------------------------------------------------------------------
In the second posting of this series I will describe in more detail Phase I. When you haven’t read the first 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 I:

1. Is the new SQL Server going to be physical or virtual? Also check what Microsoft officially recommends here;
2. Is the new SQL Server going to be clustered or not? Involve management and let them tell you how they look upon Monitoring. Is it business critical or not?
3. What version of SQL Server are you going to use (Standard or Enterprise)?
4. Provisioning of the new SQL Server, installing it with the features AND correct COLLATION settings required by OM12;
5. Preparing the security of the new SQL Server so SCOM can access it;
6. Adjusting the Master Database with a special script so it understand typical SCOM messages, piped into the event log;
7. Enabling CLR on the SQL Server so Group calculation works in SCOM R2;
8. Export of all custom RDLs residing on the current SQL 2005 server, used by SCOM;
9. Make sure the SCOM environment is healthy. Check SCOM (in the Console and OpsMgr event logs on the RMS and MS servers) and solve any serious issues before moving on to Phase II;
10. DON’T deploy a SCOM R2 Agent on this server!!!!
11. Make a list with the SCOM service accounts and their passwords, required in Phase II.

As stated before, it won’t be a detailed step-by-step guide about how to install SQL Server 2008 R2 SP1 CU#2, but I will highlight the most crucial steps in the processes. 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 4
When installing SQL Server 2008 R2 SP1 CU#2 also install the SQL Feature Full-Text Search:

and select the correct Collation Settings, SQL_Latin1_General_CP1_CI_AS:
 
Step 5
Logon to the SQL 2005 Server and open SQL Server Management Studio. Go to Server name > Security > Logins and look for the SCOM R2 Service Accounts (typically there are at least four of them: Action, SDK, Data Warehouse Write and Data Warehouse Read). It goes without saying all these accounts are AD accounts.

Write down the details. The most important ones here are the pages Server Roles (always Public, but check and double check!) and User Mapping. Make a screenshot of the latter page since it narrows down the margin of error compared to writing down the settings. Example:
  
Recreate the Logins on the new SQL 2008 R2 SP1 server. Of course, not all settings from User Mappings can be applied yet (the SCOM R2 databases aren’t there yet…) but at least the required Logins are in place. Example:
 

Step 6
Run this script against the Master database so it becomes aware of SCOM R2 specific events which can be logged in to the Windows Event log:
 

Step 7
Enabling CLR in order to have Group calculation working. Even though this must be run against the OperationsManager database itself, it’s good to have the required Stored Procedure already available:

sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'clr enabled', 1; GO RECONFIGURE; GO

Step 8 Now all custom made Reports in SCOM must be exported.
Mind you, these aren’t the Reports you have saved to Favorites nor the Published Reports. Reports like that are Linked Reports, like shortcuts which refer to other existing Reports. These Linked Reports won’t be exported but have to be recreated later on.

Another approach here could be running these Reports one by one and save them to a MP of their own. Also the Reports which are present in the MPs won’t have to be exported since the MPs will upload them automatically to the new SSRS instance.

What I mean with Custom Reports are the ones which are made with Report Builder or BIDS for instance. Identify those Reports and their locations. A tool will be used to export the RDLs. It requires .NET Framework 4.0. so when it’s not there we have to install it. The stand alone installer can be found here for both architectures. See this posting of mine about how to use the tool to export the Reports.

Since ALL Reports will be exported, delete the ones which are present in the MPs and keep the custom made Reports. These will be uploaded later on into the new SSRS instance.

Step 9
Check the health of the SCOM environment. Make sure all is OK and nothing amiss. Check the SCOM Console for the health of the MG and also check all OpsMgr eventlogs on the SCOM R2 MS servers. Only when all is well, it’s time to move on to Phase II.

Conclusion
As you can see, there is a lot to do. But with Phase I you can take your time and prepare it step-by-step. In the next Phase the move of the SCOM R2 databases and SCOM R2 Reporting will take place. And this is the Phase where it all happens. Nice! See you’ll next time!