Prepare your environment to Operations Manager 2012 – Part II Updating the SQL Server

Hi All,

As promise, the second part of this series will show you to how to update your Operations Manager 2007 R2 infrastructure from SQL Server 2005 to SQL Server 2008 R2 SP1.

Pre-requisites

Resources

I invite you first to read my previous post : Prepare your environment to Operations Manager 2012 – Part I Actions Plan

Marnix Wolf, an Operations Manager MVP, already posted a series about the same subject, and I really advise you to read it :

Preparing for migrating to OM12: Moving from SQL 2005 to SQL 2008 – Part I: Along came a theory
Preparing for migrating to OM12: Moving from SQL 2005 to SQL 2008 – Part II: Phase I – The Preparations
Preparing for migrating to OM12: Moving from SQL 2005 to SQL 2008 – Part III: Phase II – The Migration

 

Infrastructure

Below, the pre-requisites from a infrastructure point of view :

  • Your current SCOM 2007 R2 Infrastructure must be at least Cumulative Update 4 (CU4) Level, but my personal advice it’s to update it first to the last Cumulative Update, the CU5.
  • A brand new server running Windows Server 2008 R2 SP1.
  • .Net Framework 3.5.1 role installed.
  • Microsoft SQL Server R2 2008 source +  SP1 Package.
  • Add you SQL Service Account to the local administrator group of the new server.
  • Backup all your SCOM 2007 R2 Infrastructure.

SQL Server 2008 R2 installation

let’s starting
image

We’re starting a new SQL installation
image

Select SQL Server Feature Installation
image

Check at least the check boxes below
image

Just click on next.
image

Use the default instance, or create a new one, depending of your environment.
image

Review the information and click on next.
image

Configure your Run As Account for the SQL Server.
image

In the Collation Tab, select SQL_Latin1_General_CP1_CI_AS and click next.
image

Specify the SQL Admins for your new SQL Server.
image

Select Install the Native Mode Default Configuration and click Next.
image

Just review the information and click on Next.
image

Just review the information and click on Next.
image

Just review the information and click on Install.image

Installation is in progress.
image

Installation Completed Successfully.
image

It’s now time to install the SP1, run the SQLServer2008R2SP1-KB2528583-x64-ENU.exe that you have previously downloaded on Microsoft Website.
image

image

Accept the license terms and click next.
image

Review the information and click on next.
image

Review the information and click on next.
image

Review the information and click on update.
image

Update is in progress.
image

SP1 has been successfully installed.
image

 

Report Server Uninstallation

You must back up any custom reports that were authored outside of Operations Manager 2007. For more information about this, see Moving the Report Server Databases to Another Computer in the SQL Server 2008 Books Online (http://go.microsoft.com/fwlink/?LinkId=151513).

Go to your current SCOM reporting Server, Add and Remove Programs menu and uninstall the System Center Operations Manager 2007 R2 Reporting Server.
image

Uninstallation in progress.
image

 

SQL Server 2005 : Backup the DBs

Go on your SQL Server 2005, start the SQL Management Studio, select OperationsManagerDB, Tasks, Backup.

image

Select the backup location for your Operations DB.
image

In the options, check Verify backup when finished and click on OK.
image

The backup of the Operations Manager DB completed successfully.
image

Do exactly the same operations than above for the Data Warehouse DB.
image

We’ve got now, two DB Backup files.
image

 

SQL Server 2008 R2 SP1 : Restoring the DBs

It’s now time to go on your brand new SQL Server 2008 R2 SP1 server to restore the two databases.

Start the Sql Management Studio and choose Restore Database.
image

Select restore from Device and add the backup file of your OperationsManager DB and click OK
image

Check the Restore box next your DB name.
image

In the Destination for restore, you are now able to specify the OperationsManager DB.
image

Review all the information, and click on OK.
image

Do exactly the same for the Data Warehouse DB.
image

Both DBs are now available.
image

Now, we have to authorize the SCOM service accounts to connect to these newly restored DBs. For that simply go Sql Management Studio, Security Logins and do a right click, chose new login.
image

In the User Mapping part, we have to copy exactly the same rights that we have on the SQL Server 2005.

On the SQL Server 2005 :
image

Replicate the exact same configuration, for each Maps to the SQL Server 2008 R2 SP1.
image

If you are using several account to connect to the different Database, apply the same than what we did above for all your accounts. For my lab environment, I just have a second account.
image

We have to Enable the CLR on the Operations Manager DB on the SQL Server 2008 R2 SP1 server. Just copy past the query below.

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘clr enabled’, 1;

GO

RECONFIGURE;

GO

image

Click on Execute and check the result.
image

Now, in the OperationsDB, search for the table dba.MT_ManagementGroup.
image

Select Edit Top 200 Rows.
image

Change the SQLServerName_6B1D1BE8_EBB4_B425_08DC_2385C5930B04 value from your SQL Server 2005 server name to the SQL Server 2008 server name.
image

Go on your OperationsManagerDW Db, select the table dbo.MemberDatabase.
image

Select Edit Top 200 Rows.
image

Change the ServerName value from your SQL Server 2005 name to the SQL Server 2008 name.
image

Enable the SQL Broker, for that, take a look to Alexander’s post : http://scug.be/blogs/scom/archive/2008/11/22/the-sql-server-service-broker-for-the-current-opsmgr-database-is-not-enabled.aspx

 

RMS & MS : Changing the link to the DB server

We now have to change the SQL server reference on the RMS server and every MS to point to the new SQL Server. For that, stop the three System Center Services.
image

Launch the RegEdit and look for the key HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft Operations Manager3.0Setup

image

We have to change the DatabaseServerName value to point to our new SQL Server.
image

Start the 3 Operations Manager Services on the RMS and the System Center Management service on the MS. 
image

In the Event Logs, you must see these events.
image

image

 

Last but not least, the reporting server.

Microsoft released a KB for running Operations Manager 2007 R2 on SQL Server 2008 R2 KB245714. http://support.microsoft.com/kb/2425714

Open Computer Management, in the Computer Management MMC snap-in, expand System Tools, expand Local Users and Groups, and then click Groups.

Locate the following group:
SQLServerReportServerUser$<hostname>$MSRS10_50.<SQLInstanceName>
image

Rename the group to the following by removing "_50" from the group name:
SQLServerReportServerUser$<hostname>$MSRS10.<SQLInstanceName>
image

Install System Center Operations Manager 2007 R2 Reporting.
image

image

image

Select only the Reporting Server Part.
image

Specify the name of you RMS server.
image

Specify the name of your new SQL Server.
image

Review the information and click on Next.
image

Click on Next.
image

Click on Next.
image

Click on Install.
image

Installation in progress.
image

Installation done.image

The Reporting is Back in SCOM Console.
image

After you install System Center Operations Manager 2007 R2 Reporting, rename SQLServerReportServerUser$<hostname>$MSSRS10.<SQLInstanceName>
image

back to original name SQLServerReportServerUser$<hostname>$MSSRS10_50.<SQLInstanceName>
image

We could now remove the SQL Server 2005 and enjoy our new SQL Server 2008 R2 SP1.

I hope this post will help you to prepare your environment to be ready for SCOM2012. The next post of this series will show you how to migrate your RMS role from Windows Server 2003 SP2 to Windows Server 2008 R2 SP1.

Christopher Keyaert – Ops Mgr MVP

mvp_avatar

Tweet about this on TwitterShare on FacebookShare on LinkedInShare on Google+Email this to someoneShare on TumblrPin on PinterestDigg thisShare on RedditFlattr the authorBuffer this pageShare on StumbleUpon
This entry was posted in Uncategorized. Bookmark the permalink.

0 Responses to Prepare your environment to Operations Manager 2012 – Part II Updating the SQL Server

  1. shianx says:

    Hi,

    I’d like to ask on this process, is there no need to install the data warehouse component?

    Thanks,

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>