SQL 2008 R2 and SCOM 2007 R2 CU5
So one of the attractive features of CU5 was the ability to now use SQL 2008 R2.
I also found that Windows reporting was now no longer working - after a quick Google I found that the new reporting pack was only compatible with 2008 Report Services.
I was running 2005. Now before I get going I'd like to point out I am NOT a SQL guy. I know a bit about databases, enough to get me in trouble but I am NOT a SQL guy.
Ho hum. Time for a SQL upgrade then. With my paperwork and Change Request (ITIL rules here) in place I dutifully made backups off all the DBs running on the SQL 2005 box.
I followed the Microsoft documentation :
http://technet.microsoft.com/en-us/library/dd789004.aspx
which basically says run, SRSUpdateTool.exe, then run the SQL upgrade and then run SRSUpdateHelpr.msi and finally re-run SRSUpdateTool.exe
This all sounded pretty simple, next, next and finish. I'll have me a shiny new SQL install in no time.
A week later and I finally got things working again.
Here's my story...
I used the correct SRSUpdateTool which came with the CU5 patch. So I think this part was working fine. Indeed the tool ran fine and reported as such.
So on to the SQL update...BLAM!
The SQL 2008 R2 upgrade failed. There was pretty much no way I could have forseen this just a bit of bad luck/misfortune I guess. This meant that the final part of the upgrade refused to run (the Master DB wouldn't mount) so I was left with a partly installed 2008 R2 install and a full 2005 install. Single user mode was no help as I was unable to get a connection. So I decided that one install had to go. Well in fact both did. When I uninstalled 2008 from the system to revert to 2005 the uninstaller - obviously enough, uninstalled the SQL services thereby breaking my 2005 install.
So after totally cleaning off any signs of SQL from the SQL box I started again. I installed SQL 2008 R2 nice and easy (what went wrong with the upgrade then?) I then restored my databases back into the new shiny box and started all the SCOM services.
Nothing is easy is it?
So after trawling through the event logs this generated it turns out I'd made my first true mistake...bit of an obvious one now I think of it but blind panic of losing our monitoring system clouded my judgement :)
Permissions. I'd forgotten to create SQL logins for the accounts used in SCOM. So a click or two later got me back and running. Almost.
I ran into something which to be honest didn't surprise me but was rather naff. Adding a computer to the login of a SQL server doesn't require any validation. Whilst there is a nice AD picker for finding users and groups there is nothing for a computer object. So for a while I was stumped. So I just tried adding it regardless. It works. Looking on Google shows me that I am not alone in thinking this is a bit odd or indeed getting stuck on this.
So now I have my logins all present and correct but I was still getting DW errors. A bit of googling led me to check the DW database, a quick select statement later showed that the DW account in the DB was not the one I'd setup on my new SQL DB logins. Fixed that.
No I had one 2115 event hanging around. This was resolved by "resetting" the Data Warehouse SQL Server Authentication Account and Reporting SDK SQL Server Authentication Account to a space, despite it already being space this worked!
Thanks Google! Well thanks to the dozen or so blogs which led to the final configuration (and fixed a few outstanding issues along the way too :) ).
I've not mentioned the ReportServers bit which was broken and got fixed eventually as I feel this deserves a whole section on its own. Here it is.
After restoring the ReportServer DB and TempDB (which took a while as SQL refused to agree that the backups were ok) I ran the 2008 R2 report server configuration tool.
As per the instructions - yes I was still trying to follow them, I re-ran SRSUpdateTool with PostSQLUpgrade switch and then the SRSSQLUpgradeHelper.msi with the stated switches. As you may have guessed this resulted in SCOM giving a sort of helpful error: ReportServer is not initialised.
This I found from Google means that the encryption key is not working correctly. So I reset the key and deleted the encrypted content. I don't recommend you do this.
After a couple of days of fruitless poking around I finally figured out my backup key password and was able to restore Report Server to an initialised state.
Now I got a "This server does not support scale out". Again a couple of days of bumbling in the dark, I almost resigned myself to wait till SCOM 2012 went RTM to get reporting working again, I found that it is possible to "remove" the Report Server from the scale out list.
Hoorah.
Like I said. I aint a SQL guy.
Hopefully my rant may help you find your way in the dark. One day I am sure someone will write useful error messages.
Oh and after all this. It is worth it. SQL 2008 is noticably faster than 2005 and in the world of SCOM you will know this is a little miracle.
I also found that Windows reporting was now no longer working - after a quick Google I found that the new reporting pack was only compatible with 2008 Report Services.
I was running 2005. Now before I get going I'd like to point out I am NOT a SQL guy. I know a bit about databases, enough to get me in trouble but I am NOT a SQL guy.
Ho hum. Time for a SQL upgrade then. With my paperwork and Change Request (ITIL rules here) in place I dutifully made backups off all the DBs running on the SQL 2005 box.
I followed the Microsoft documentation :
http://technet.microsoft.com/en-us/library/dd789004.aspx
which basically says run, SRSUpdateTool.exe, then run the SQL upgrade and then run SRSUpdateHelpr.msi and finally re-run SRSUpdateTool.exe
This all sounded pretty simple, next, next and finish. I'll have me a shiny new SQL install in no time.
A week later and I finally got things working again.
Here's my story...
I used the correct SRSUpdateTool which came with the CU5 patch. So I think this part was working fine. Indeed the tool ran fine and reported as such.
So on to the SQL update...BLAM!
The SQL 2008 R2 upgrade failed. There was pretty much no way I could have forseen this just a bit of bad luck/misfortune I guess. This meant that the final part of the upgrade refused to run (the Master DB wouldn't mount) so I was left with a partly installed 2008 R2 install and a full 2005 install. Single user mode was no help as I was unable to get a connection. So I decided that one install had to go. Well in fact both did. When I uninstalled 2008 from the system to revert to 2005 the uninstaller - obviously enough, uninstalled the SQL services thereby breaking my 2005 install.
So after totally cleaning off any signs of SQL from the SQL box I started again. I installed SQL 2008 R2 nice and easy (what went wrong with the upgrade then?) I then restored my databases back into the new shiny box and started all the SCOM services.
Nothing is easy is it?
So after trawling through the event logs this generated it turns out I'd made my first true mistake...bit of an obvious one now I think of it but blind panic of losing our monitoring system clouded my judgement :)
Permissions. I'd forgotten to create SQL logins for the accounts used in SCOM. So a click or two later got me back and running. Almost.
I ran into something which to be honest didn't surprise me but was rather naff. Adding a computer to the login of a SQL server doesn't require any validation. Whilst there is a nice AD picker for finding users and groups there is nothing for a computer object. So for a while I was stumped. So I just tried adding it regardless. It works. Looking on Google shows me that I am not alone in thinking this is a bit odd or indeed getting stuck on this.
So now I have my logins all present and correct but I was still getting DW errors. A bit of googling led me to check the DW database, a quick select statement later showed that the DW account in the DB was not the one I'd setup on my new SQL DB logins. Fixed that.
No I had one 2115 event hanging around. This was resolved by "resetting" the Data Warehouse SQL Server Authentication Account and Reporting SDK SQL Server Authentication Account to a space, despite it already being space this worked!
Thanks Google! Well thanks to the dozen or so blogs which led to the final configuration (and fixed a few outstanding issues along the way too :) ).
I've not mentioned the ReportServers bit which was broken and got fixed eventually as I feel this deserves a whole section on its own. Here it is.
After restoring the ReportServer DB and TempDB (which took a while as SQL refused to agree that the backups were ok) I ran the 2008 R2 report server configuration tool.
As per the instructions - yes I was still trying to follow them, I re-ran SRSUpdateTool with PostSQLUpgrade switch and then the SRSSQLUpgradeHelper.msi with the stated switches. As you may have guessed this resulted in SCOM giving a sort of helpful error: ReportServer is not initialised.
This I found from Google means that the encryption key is not working correctly. So I reset the key and deleted the encrypted content. I don't recommend you do this.
After a couple of days of fruitless poking around I finally figured out my backup key password and was able to restore Report Server to an initialised state.
Now I got a "This server does not support scale out". Again a couple of days of bumbling in the dark, I almost resigned myself to wait till SCOM 2012 went RTM to get reporting working again, I found that it is possible to "remove" the Report Server from the scale out list.
Hoorah.
Like I said. I aint a SQL guy.
Hopefully my rant may help you find your way in the dark. One day I am sure someone will write useful error messages.
Oh and after all this. It is worth it. SQL 2008 is noticably faster than 2005 and in the world of SCOM you will know this is a little miracle.
Comments
Post a Comment