Wednesday 23 June 2010

Oooh! Squee! New Tool

Microsoft® SQL Server® 2008 R2 Best Practices Analyzer.

Tells you what could be improved in your installation, and how to go about it

  • Gathers information about a Server and a Microsoft SQL Server 2008 or 2008 R2 instance installed on that Server
  • Determines if the configurations are set according to the recommended best practices
  • Reports on all configurations, indicating settings that differ from recommendations
  • Indicates potential problems in the installed instance of SQL Server
  • Recommends solutions to potential problems

Sweet. Note: there have been some problems with installing it owing to Workgroups problems and Kerberos issues. Solution here.

More Fun And Games with SCOM

For various reasons, chief among them the requirement for a more scalable deployment and the fact that the SCOM Server was "really starting to struggle" according to our Chief System Support Analyst (and, he should know: he's the one that's using the server all the time), we decided to shift the OperationsManager database, the OperationsManagerDW database and the ReportingServer databases onto a shiney new SQL Server. I am all about the shiney, after all. I am a girl geek.

Now, I thought it would be simple enough. Shift the OpsManager db. Shift the OpsManagerDW db. Shift the Report Server stuff. There are some nice articles on how to do this. Including a whole article about moving Report Server databases. Go and have a look at them.

Firstly, notice how the article about moving Report Server is almost identical to the one about moving OperationsManagerDW? That was the first gotcha. If you are moving the entire SQL Server, you need to follow the instructions in the article about moving in the document entitled 'How to Move the Operations Manager Reporting Server in Operations Manager 2007'. Ignore the one about 'How to Move the OperationsManagerDW Database in Operatios Manager 2007', other than the information about the Logins (step 9 onwards) and data sources (step 13 onwards).

Second gotcha? There is no sensible time to move SQL Server Reporting Services (SSRS) if you have custom reports. If you move it before the SCOM Report Server component, then there is the risk that installing SCOM's Report Server will wipe the custom reports. If you move it after the SCOM Report Server component, then there is the risk that you destroy the SCOM Report Server: it relies on a functional instance of SSRS.

The mistake I made was to move OperationsManagerDW according to that document's instructions, and then try to move the ReportServer having moved over the Report Server Databases part way through the process. This was silly: the ReportServer component did not install properly, it destroyed SSRS to the point that I had to reinstall it, and the IIS Application Pools refused to run:

Source W3SVC
Category (0)
Event 3221226474
Computer SQLSCOM

Message
Application pool 'ReportServer' is being automatically disabled due to a series of failures in the process(es) serving that application pool.

Source W3SVC
Category (0)
Event 2147484657
Computer SQLSCOM

Message
A process serving application pool 'ReportServer' terminated unexpectedly. The process id was '7468'. The process exit code was '0xffffffff'.

(there were several errors with the exit code '0xffffffff')

These linked to:
Error loading configuration file: Root element is missing.
w3wp!library!5!22/06/2010-09:20:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.

Report Server's Logs also gave me:
An internal error occurred on the report server. See the error log for more details., Incorrect security descriptor version;

and

w3wp!library!7!06/22/2010-11:01:07:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information., Unable to load assembly Microsoft.EnterpriseManagement.Reporting.Security;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.EnterpriseManagement.Reporting.Security' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.EnterpriseManagement.Reporting.Security'
at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
at System.Reflection.Assembly.Load(String assemblyString)
at Microsoft.ReportingServices.Diagnostics.ExtensionClassFactory.LoadAssembly(String name)


If I tried running the application pools under the Local\System Account, well, they didn't fail, but Reporting Services refused to function. I had service unavailable messages, I had unable to connect messages, and I had a lovely set of errors for which I was referred to the Installation Log File for SCOM Report Server, and to look for 'value 3'. I had error 108 all about me.


In other words, I had a partial installation of SCOM Report Server, and it destroyed SSRS. Nothing wanted to work.

I fixed this by:

Backing up everything I could think of.
Uninstalling and Reinstalling SSRS (I had tried to revert back to the original databases on the server, with the original .snk file - and every other .snk file I could find. No dice). With SP3 (as it was SQL 2005).
Using Add and Remove Programs to get rid of the SCOM Report Server, which also took out the Data Warehouse component, but not the Data Warehouse itself. Windows was under the impression SCOM Report Server was properly installed - trying to repair that particular component made no difference whatsoever.
Using the resetsrs.exe tool, as part of the SCOM installation disk to make sure that SSRS was working properly.
Keeping a close eye on IIS and Application Pools while installing.
Treble checking that SSRS was working: without SSRS working, SCOM Report Server won't install or work.
Stopping the SCOM Services.
Reinstalling SCOM Report Server, and only installing the Report Server element.

This worked: however, I'd lost my custom reports. I will, at some point, try shifting SSRS to another server to see what went wrong with moving SSRS - at the moment, I'm quite happy with the default reports. The custom reports can be recreated based on the screenshots we took of them as a backup before we failed to move SSRS. It wasn't the end of the world to lose them.

The final hiccup with the whole thing was the removal of SQL from the original SCOM server. This went without a hitch: until we got a whole batch of errors to inform us that the Scheduler Data Source Module Failed Initialization: The Microsoft Operations Manager Scheduler Data Source Module has some invalid configuration. The IIS Discovery Probe Module Failed Initialization: an Error initializing IISDiscoveryProbe from config. The answer, after a lot of swearing was here. Turns out that the MSXMLS parser got uninstalled with SQL: I had no idea that there was a dependency for that, and we simply reinstalled that. All was then well.

If I were going to do this again, I would make very sure that I could restore SSRS properly before I did anything else. I would also test moving everything in a VM, and test moving it in different orders, to work out how best to get SSRS and all custom reports across.