17-08-2009 Update: In Step 4 the SRSUpgradeTool.exe is used. There are some bugs in this tool. Microsoft has acknowledged it and is working on a new version of this tool. In this blog posting of mine I write about and refer to a webpage of Microsoft where a workaround can be found.
In three previous blog postings I already wrote about the upgrade process:
- Part I: Why SQL 2008 and how to test an upgrade?
- Part II: Potential pitfall after pre-upgrade process
- Part III: Potential pitfall after installing SP1 for SQL 2008
This posting will tell the whole story together about how to go about it. In the end I will add certain aspects which I myself would do.
All steps described here are based on OpsMgr R2. If you are still on OpsMgr RTM/SP1 STOP NOW.
Step 1: Preparation
Every upgrade needs preparation, no matter how small or big the upgrade is going to be. When upgrading to SQL 2008 I see it as rather big from an OpsMgr point of View since no SQL = no OpsMgr.
So run validated backups of the databases and database server as well. To be safe, make backups of the unsealed MPs and the RMS. Better safe than sorry. When SQL Reporting Services is hosted on another server, backup this server as well.
Read Part I for some additional information.
Step 2: Pre-upgrade steps – SRSUpgradeTool.exe
When OpsMgr Reporting services is being used, before upgrading to SQL 2008, a certain tool has to be run.
This tool restores three config files which were backed up during the installation process of OpsMgr Reporting.This needs to be done. When the SQL 2008 installation detects the customized security extensions it will block the upgrade, until they are removed.
Run this tool (SRSUpgradeTool.exe) on the SRS server:
SRSUpgradeTool.exe /InstanceName:<SQLInstance> /Action:
PreSQLUpgrade
Now the upgrade documentation tells to restart SRS services and open Reporting Services Configuration afterwards. Don’t be alarmed when an error is displayed in that console. Check Part II for more information.
Step 3: Upgrading to SQL 2008
Upgrade to SQL 2008 NOT to SQL 2008 with SP1. If you do, the SRSUpgradeTool.exe won’t work anymore!
Read Part I about how to upgrade to SQL 2008 (do’s and don'ts) and where to find more information about how to upgrade to SQL 2008.
Step 4: Post-upgrade steps – SRSUpgradeTool.exe
Now the SRSUpgradeTool.exe has to be run once again, in order to update the registry entries for the installed components of OpsMgr Reporting to point to new SRS folder location.
Run this tool (SRSUpgradeTool.exe) on the SRS server:
SRSUpgradeTool.exe /InstanceName:<SQLInstance> /Action:
PostSQLUpgrade
Step 5: Post-upgrade steps - SRSUpgradeHelper.msi
Now the tool SRSUpgradeHelper.msi has to be run in order to place the OM reporting files on the new SRS folder and set the SRS configuration.
Run this tool (SRSUpgradeHelper.msi) on the SRS server:
SRSUpgradeHelper.msi DATAREADER_DOMAIN=<DOMAIN> DATAREADER_USER=<DATAREADERUSERACCOUNT> MGSERVER=<RMSSERVER> SRS_SERVER=SRSSERVER
Step 6: Install SP1 for SQL Server 2008
A good idea is to make a backup of the SQL server and related databases first before starting the installation of SP1 for SQL 2008.
Check Part III about an error message which might popup after the upgrade to SP1 for SQL 2008.
When all is well, everything is working correctly: SQL 2008 SP1 is up and running and OpsMgr is doing fine. To be sure, open the OpsMgr Console and go through it. Check whether all is working as it should.
Go to the reporting pane and make a report to see whether all is working.
Advise
So when all is OK, you want to monitor this SQL 2008 server as well since it is the heart of OpsMgr. When you haven’t already imported the MP for SQL 2008 remember to install SQL Distributed Management Objects (SQL-DMO) on the SQL server. Otherwise OpsMgr cannot monitor SQL 2008 Server.
In the MP guide delivered with the SQL MP there is a detailed explanation where to get this software and how to install it. Believe me after upgrading to SQL 2008 this is child's play. :)
15 comments:
Hi Marnix,
I want to upgrade from SQL 2008 SP1 to SQL 2008 R2. I am getting "Custom Security and Custom Authorization" present errors.
Can I run SRSUpgradeTool.exe on upgrading from SQL 2008 SP1 to SQL 2008 R2? On your post it is mentioned for SQL 2005 to SQL 2008. Will this still work in my case. Please guide me. I have only two days left to upgrade but facing this issue.
Thanks in advance!!!
Hi Bsuresh.
The tool is only menat fir moving from SQL 2005 to SQL 2008 R2. In your case this tool isn't required.
When do you get that error exactly? Please reply only once and not eight times :).
Cheers,
Marnix
Thanks for you reply. Would you mind to see the below link for detailed issue:
http://social.technet.microsoft.com/Forums/en-US/operationsmanagergeneral/thread/d116b8a2-9250-4fac-8d67-0f8de2b2e364
By the way, earlier post was not published and it gave that entered Two Words mentioned were wrong.. Sorry for that...
NP. My guess is thatSQL can't upgrade to SQL Server 2008 R2 since the SCOM security extensions for SQL Server Reporting Services (SSRS) are in place. SCOM adds these in order to integrate that SSRS instance into the SCOM Console and adhering it to the SCOM security like user roles.
One way to go about it is to remove SCOM Reporting while keeping the Data Warehouse. Then run the resetsrs.exe tool which resets SSRS back to the original security settings. Than upgrade to SQL Server 2008 R2. However this can be a challenge and must be done bu staff who know what they're doing. It will be a challenge to get it right and some erros might pop up.
So perhaps it is better to use the tool used for upgrading from SQL 2005 to SQL 2008. It does the same (removing the SCOM security extensions before the upgrade) and putting them back after the upgrade.
BUT there is a newer version of that tool, found in the recent cumulative updates. Use that tool and not the one on the installationmedia of SCOM R2 since that one is flawed.
This also a challenge but lighter compared to the first solution I described.
Good luck.
Cheers,
Marnix
Thanks for your information...
My Datawarehouse is in different server. I have ReportServer, ReportServerTempDB ,etc.. in Reporting Server.
I will proceed the below steps:
1. Uninstall SCOM 2007 R2 Reporting from Add/Remove programs.
2. The I will run resetsrs.exe tool which resets SSRS back to the original security settings
3. Upgrade to SQL Server 2008 R2 from SQL 2008 SP1.
4. Install SCOM 2007 R2 Reporting Services.
On top of this, what all the things I should backup? Could you please shed some light here?
In cases like this you could backup the OpsMgr and OpsMgrDW databases and the SSRS server with its databases so there is way back when you start removing SCOM Reporting.
Good luck!
Marnix,
While upgrading from SQL 2008 SP1 to R2 RTM, DB Engine was succesfully upgrade but had a problem with SQL Reporting upgrade bcoz of some dll files were used. So rebooted the server and tried to upgrade SQL Reporting, during this time it showed nothing to Upgrade. I was shocked and tried patching with R2 SP1. This time it told that SQL Reporting was not upgraded.
So, I uninstalled SQL Reporting and Pacthed SQL DB with R2 SP1. It was success.
Later tried installing SQL Reporting and it was success.
Sad news is that default pages http://servername/reportserver and http://servername/reports are not working.
Because of this facing problem with SCOM reports as you know.
While accessing above sites, getting The item / cannot be found (rsItemNotFound) error.
Please help me on sorting this. Thanks in advance!!!
Hi.
Sorry for my late response but I was a bit busy lately. Seems like SSRS isn't working properly (duh!).
Run the resetsrs.exe tool in order to make sure SSRS is set back to it's basics again and test it in order to see the webcomponent of SSRS is OK now. Hope this helps.
Cheers,
Marnix
Thanks for your reply Marnix though it is bit late :P
I have copied the files from \bin of MSRS10 to new \bin folder.
After this followed below linkhttp://www.laschinger.net/?p=119 and now the \reports and \reportserver page is working.
Later I have installed SCOM reporting and it was success. But after that reports were not being deployed in Report Server. I am getting 31569 Error in RMS.
Error details:
Data Warehouse failed to request a list of management packs which contain reports deployed to SQL Reporting Services Server. Report deployment process failed to request management pack list from SQL RS Server. The operation will be retried.
Exception 'WebException': The proxy name could not be resolved: 'proxy.domain.com'
Not sure what to do :(
Hi,
I am a bit at a loss here. Everything seems to be in place and yet it doesn't work.
Normally when SSRS isn't functional, SCOM Reporting won't install. It will fail instead showing some errors.
In your case you succeeded in installing SCOM Reporting while SSRS was broken.
I don't know where to look anymore. Try the TechNet Forums or contact Microsoft CSS.
I am sorry man.
Cheers,
Marnix
we need to move our Scom 2007 r2 CU7 DB on a SQL2008 r2 sp1 or sp2 from Sql 2005.
Do we still need to use the srsupgradetool.exe or is it as easy as 1-2-3 and Finish ?
Found info on the subject but from CU4...
Thank you
Hi.
The tool is still required. By memory, CU#7 contains an updated version of that same tool. However, my experience is that's way easier to remove SCOM Reporting and reinstall it on the new SQL server. Goes a lot faster. See here: http://thoughtsonopsmgr.blogspot.nl/2011/12/preparing-for-migrating-to-om12-moving.html
Kind regards,
Marnix
In step 6 "special Script" Do I still need to apply that script if I all ready applied CU7 ?
Step 10 : you mention to NOT install scom 2007 r2 agent on SQL 2008 machines... But the agents are already installed on all our SQL 2008 clustered and few DB are already in production. Would that situation create an issue ? We can stop agent for 5 to 10 min max.
Thank you
Marie-Anne
Hi Marie-Anne
In this posting I don't see a Step 10. Did you meant to comment on another posting? Since some of the comments you make don't seem to relate to this posting.
Not installing an agent is only valid when you want to install SCOM Reporting ont hat SQL box. When an Agent is already present the changes are likely that the installation of SCOM Reporting will fail. Afterwards when the installation is successfully finished you can install (and should) an SCOMAgent on that server.
Cheers
Marnix
Thanks for the info. Step 6 was in post no.2. I posted the question there.
As for the agent, this is ok. the report service is on another SQL that still runs sql2005 and we will not migrate it.
Post a Comment