Upgrading from SQL Server 2008 to R2 – Part I – The Beginning aka Preparation

Since the end of September 2010 SCOM R2 officially supports SQL Server 2008 R2. Originally this support was divided in two phases:

1. Phase One: Support for SQL Server 2008 R2 for new SCOM R2 installations;
2. Phase Two: Support for an existing SCOM R2 deployment upgraded to SQL Server 2008 R2.

When CU#3 came out only Phase One was supported. An upgrade scenario where SQL Server (2005/2008) is upgraded to SQL Server 2008 R2 wasn’t supported in 2010.

Time for Phase Two
With CU#4 this support statement has been changed and from that point on, one can upgrade the SQL server instance hosting the SCOM DBs and Reporting instance to SQL Server 2008 R2:

But how to go about it? How to deal with the upgrade? What are the do’s and don’ts? I already posted a series of postings about how to upgrade from SQL Server 2005 to SQL Server 2008, to be found here. Therefore I will not repeat myself completely and post a new series about how to upgrade from SQL Server 2008 to SQL Server 2008 R2. Basically these are the same steps when you upgrade from SQL Server 2005 to SQL Server 2008 R2.

But like any other upgrade, preparation is KEY. Which is more than obtaining the installation media of SQL Server 2008 R2. The first posting in this new series will be all about PREPARING the upgrade. So let’s start.

1. Health
   Any upgrade will only succeed when the product to be upgraded is healthy. GI>GO (Garbage In > Garbage Out) is definitely at play here. So make sure all is just fine. In this case, make sure SQL Server is running like clockwork. Check the DBs, check the event logs of the SQL server, check SCOM, the Reporting Tree (create some Reports in order to see all is just fine), open Report Manager (the url), check the SQL Server Logs in Microsoft SQL Server Management Studio. And only when all is OK, you are in the clear. Whenever you find something, fix it, run through the same list AGAIN and only when all is OK, move on to Step 2.
   
   
2. Backup Backup Backup and VERIFY…
   Make sure there is always a way back. No matter what. So a backup is required here. But not only of the DBs of SCOM but of all DBs (except for the Temp DB of course). But the system DBs like the Model DB needs to be backupped as well. Besides that, the whole SQL server needs to be backupped as well, including the System State. And some verification is needed as well.
   
   
3. SCOM > SQL > SCOM > SQL…
   Basically, what I am trying to say here is that SCOM is NOTHING without SQL. Without SQL no SCOM. Period. So when SQL gets hosed, so will your SCOM environment. So be sure to have all the service accounts of SCOM (Action, SDK, DW Read & DW Write) with their passwords at hand. Also a backup of the Encryption Key. I know, it might sound over estimated, but it is better to be safe than sorry. And again, this information should be at hand ALWAYS, so a good training and verification it is.
   
   
4. Change Management
   Many people just want to start, click > Next > Next > Finish. But ICT Shops who go like that are short lived in today’s world. So some good Change Management is needed here. Just go with the flow of your organization and be good. It can safe you a lot of troubles and even your job/position.
   
   
5. Testing, test, test…
   When  you have a test SCOM environment at hand, do not hesitate and USE it. Create your own walk-through document. Of course, you can’t cover everything but it will certainly help you out in most circumstances. So test it.
   
   
6. Software
   Besides the installation media, also download the CU#5 for SQL Server 2008 R2 since it fixes an annoying issue which could make you look stupid like: Look boss, I have upgraded successfully the SQL server to R2 and now… SCOM dies? Wow!
   
   
7. Compatibility
   When the SQL Server/instance to be upgraded only hosts the SCOM R2 DBs and Reporting instance for SCOM R2, there is no problem. But when you run a SQL Server which hosts other DBs/applications as well, ascertain your self that those DBs and applications are fully supported on SQL Server 2008 R2 as well. Otherwise: don’t do it! 
   
   
8. CU#4 for SCOM R2
   As stated before, an upgrade scenario to SQL Server 2008 R2 is only supported from CU#4 for SCOM R2. So when CU#4 isn’t in place or you aren’t running SCOM R2, do not upgrade. Upgrade to SCOM R2 CU#4 first, make sure all is fine and OK and then move on to SQL Server 2008 R2. And don’t run all these upgrades in one day! Take your time and consider weeks instead of days…
   
   
9. Read, read and RTFM
   Just clicking away isn’t the signature of an ICT Professional. Anybody without brains can do that. It is the signature of a potential natural disaster. So stay away from it. Read and inform yourself. When you have DBAs, talk with them. They are equipped with a mouth. And brains. So talk with them. Communicate. Share. Read. And LEARN. Some useful links:
   http://technet.microsoft.com/en-us/library/dd789004.aspx
   http://msdn.microsoft.com/en-us/library/ms144267.aspx
   http://technet.microsoft.com/en-us/library/bb677622.aspx
   
   A small but important give away: CU#4 already contains some good information about the upgrade to SQL Server 2008 R2:
   
   This tool is going to save you a lot of frustrations…

As you can see, preparation is key. And when that has been done, it is time for the upgrade. The next posting will be about that. So stay tuned.