SystemCenterReporting Reindex Script

Can be found here. It was referred to here and here, however, the links within those pages are no longer extant.

You'll have to register, and I give no warranty as to how workable this is: however, it's taken me half an hour to find the scdw_reindex1.zip or the scdw_reindex.zip file, so I thought I'd link.

State 38

Error: 18456, Severity: 14, State: 38.

Message
Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]

And the Application Event log detail generally shows something like:

In Bytes

0000: 17 48 00 00 0E 00 00 00 .H......
0008: 1G 00 00 00 55 00 B9 00 ....S.E.
0010: 4F 00 43 00 54 00 4F 00 R.V.E.R.
0018: 66 00 51 00 4C 00 54 00 N.A.M.E.
0020: 00 00 00 00 00 00 00 00 ........
0028: 07 00 00 00 6D 00 61 00 ....m.a.
0030: 73 00 12 00 65 00 72 00 s.t.e.r.
0038: 00 00 ..

Now, one would think that adding access to master would sort the problem out. Except it doesn't. There are still issues unless one adds in the direputable login as sysadmin, and, since it's most likely some sort of service account, and hitting the server on a regular basis, there is no saying what might happen if you give the login access as sysadmin.

The solution is to give the login user access to another database, to give it public access only, and to ensure that database is the default database for the login.

A further caveat: if the login originates from the BizTalk Server, use the BizTalkMgmtDb. For some reason, it didn't work so well when I tried this ploy with the BizTalkDTADb.

"I can't take this database offline!"

An anguished cry, which I have heard twice today. The problem is, if you try to take a database offline, it will not actually complete until the current transaction has ended (or been forcibly killed), as there is a lock on the database. Killing a transaction is a bit untidy. As we find here "The ALTER DATABASE statement waits indefinitely if there is any lock on the database." One generally ends up with 'Database is in transition...Error 952' when trying to ascertain what the databases is up to, and very little seems to have an effect, including restarting SQL in some cases: and that assumes that restarting SQL is even an option.

Fortunately, if one uses a bit of T-SQL, one can persuade the last transaction to rollback neatly, unlocking everything, and reducing hassle. It's a lot nicer than going KILL SPID.


USE master

GO

ALTER DATABASE databasename

SET OFFLINE WITH ROLLBACK IMMEDIATE

GO

And there we have it. Neatly and tidily takes the database offline, it doesn't hang around in a transactional

SQL Configuration Manager Suddenly Unavailable

Last night, I installed SP1 onto a SQL 2008 Cluster. This morning, looking at another issue, I decided to mosey into the Configuration Manager, in order to double check which Ports SQL was operating on.

Imagine my horror when I got the following error, regardless of the account I used to try and open the tool:

"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers or above with SQL Server Configuration Manager. Invalid class [0x80041010]"

Googling around suggested several solutions involving changing folder permissions, and also running something called mofcomp. This in particular seemed relevant to my situation, where an installation had recently been run on the server. It would seem that, occasionally, the .mof files get corrupted during an installation. They're Managed Object Format files, and used for storing system configuration information.

They can be found in the Shared folder for the 32 bit components of your SQL Server, for SQL 2005 and above. Thus, on a 64 bit cluster, they were here:

C:\Program Files (x86)\Microsoft SQL Server\100\Shared

And running this command in cmd fixed the problem. Phew.

C:\Program Files (x86)\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files (x86)\Microsoft SQL Server\10\Shared\sqlmgmproviderxpsp2up.mof"

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.

OSQL -E

Sometimes, you get a black-box installation by a third party supplier: and, when you politely enquire as to whether you can install SSMS on the server so that you can pull out pertinent information for your SQL Estate Audit (because, naturally, the installation on the server is such that you cannot connect remotely: you just know that the server is there), the answer is no. Grrr.

But not the end of the world. Via the command prompt, we can open a connection to that instance of SQL, and run whatever queries we fancy with a local administrator's account (because, let's face it, if they haven't installed SSMS, and they're a third party supplier, the likelihood is that they haven't disabled the BuiltIn\Administrators group....).

Start off by checking the Services on the Server, to get an indication of what you've got: also check SQL Server Configuration. This will let you know how many instances of SQL are on the server, and whether they have a name other than the default servername. On the server I'm looking at, I've got two instances of SQL Express. I can connect to one by typing the following in the command prompt:

OSQL -E

For the second, I need to be a bit more specific, and type

OSQL -E -S ServerName\InstanceName

Once I've got OSQL running, I can query merrily. Every T-SQL statement that I can run in SSMS or Query Analyzer can be run in OSQL. I can execute stored procedures. I can change databases. I just need to remember that instead of hitting 'F5' to run my statement, I need to end my statement with 'GO', and press enter. It's worthwhile not running 'SELECT *', since the formatting leaves something to be desired, and it's a bit tricky....

So, what might my useful statements be? I've used a selection of the following today

To list my databases
SELECT name
FROM sys.databases
GO

To check authentication mode

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
ELSE 'Unknown'
END
GO

To check recovery mode

SELECT recovery_model_desc
FROM sys.databases
GO

To check collation

SELECT name, collation_name
FROM sys.databases
GO

To check database owner

SELECT name, SUSER_SNAME(owner_sid)FROM sys.databases
GO

To check SQL Version, Windows Version and SQL Service Pack

SELECT @@VERSION
GO
SELECT ServerProperty('ProductLevel')
GO

To check database size

USE dbname
GO
EXEC sp_spaceused
GO

It's pretty easy to find out what you need by searching for the criteria, with an additional search of 'T-SQL' slapped at the front of the string. More additions will be made here as I come to need them, however, this got me what I wanted for my spreadsheet....