A question that comes up a lot is whether SQL 2008 R2 is supported for SCOM 2007 R2 and what to do while installing a new SCOM installation on that version of SQL.
If you do a fresh installation of SCOM on a SQL 2008 R2 you will find that you cannot connect to the SQL server. It will refuse to install the database component. In that case you will have to use the dbcreatewizard to create the databases (for operationsmanager and operationsmanagerdw databases).
Support for System Center Operations Manager 2007 R2 that runs on a SQL Server 2008 R2 database
http://support.microsoft.com/kb/2425714
Also you will first need to go to SQL 2008 R2 with CU5 or higher. Watch this post from Kevin pointing at the issues and fixes:
http://blogs.technet.com/b/kevinholman/archive/2011/02/04/if-you-are-using-sql-2008-r2-for-opsmgr-db-s-you-need-sql-2008r2-cu5.aspx
When upgrading from SQL 2008 SP1 to R2 version you might refer to this posting from Marnix:
http://thoughtsonopsmgr.blogspot.com/2011/02/upgrading-from-sql-server-2008-to-r2.html
And a posting from my friend Walter Eikenboom with videos:
http://systemcenterdynamics.wordpress.com/2011/01/12/installing-scom-2007-r2-on-sql-2008-r2/
