Did it really take 3 months to publish another blog entry? Things have been busy, so I am sorry I haven't kept up so far. Will be back a bit bit quicker now
I came across a DPM server where I could not get into the Reporting tab.
It gave an error:
Could not connect to SQL Server Reporting Server because of IIS Connectivity issues.
On the computer on which the DPM database was created, restart the World Wide Web Publishing Service. On the Administrative Tools menu, select Services. Right-click World Wide Web Publishing Service, and then click Start.
The reported error number was 3013.
Of course in my case IIS was running and a restart of the service did not resolve the issue.
Turns out that IIS 7 has some additional security that stopped the connection.
So how to get it working again:
- Start the IIS manager
- Expand Websites and Default Website and click on the virtual directory for the Report Server (named ReportServer$MS$DPM2007$ in my case)
- In the middle of the screen double click on Handler Mappings
- On the right hand side click on the action Edit Feature Permissions
- Check the checkbox for scripts
- restart IIS
You should be good to go!
Came across a blogpost from Kevin Holman today about the behavior of SCOM in combination with the Cluster service when the Clustering Management pack is installed. When the cluster service goes down on a cluster node there is an automatic recovery that immediately starts the cluster service again. Very good so far of course, but what if you want to cluster service to go down because you are doing some kind of maintenance on that node? Well very simple... when maintenance then maintenance! In SCOM put it into maintenance mode! The auto-recovery will not run in that case.
Had an issue yesterday where a customer has an SQL instance on a cluster where the Operations Manager datawarehouse was hosted. Right after starting the SQL services all processors in the machine quickly rose to 100% CPU usage. It was impossible to even stop one of the databases in the instance in order to check which one was the culprit. So I talked to a SQL DBA friend of mine and we came to the following:
Started by checking what processes are running. In SQL go to Management - Activity Monitor and right click to select the View Processes list.
While checking the processes list I found two process-IDs that were blocking. Also looked like they were waiting for eachother in some way and they were both Suspended. That is strange... 100% CPU (on 8 cores) and only suspended processes (and sleeping ones). However one of the two processes listed a Wait Type of CXPACKET.
The CXPACKET indicates that it could be related to parallelism. Looks like it is waiting for the split query (across the cores) to come back with the results (I am not that deep into the nitty gritty of this). Why it would continue to use the amoun of CPU that it did I do not know. Other programs were still able to run (a bit slowed down of course), so it only seemed to affect everything running within that sqlsrv.exe for the instance.
In any case I went into the properties of the Server (in this case the cluster instance) in the SQL manager and went to Advanced and found the Maximum degree of parallelism there. By default it is set to 0. That value allows queries to be executed across all processors. So I set it to 1 in order to force each query to execute against one core at a time. This instantly solved the issue. Unfortunately I will not be able to investigate further into this, but I learned about parallelism in the proces We are guessing a stored procedure caused it.
Some info about parallelism options in SQL 2005 can be found here.
Update 17 december 2009: This procedure was written on SQL 2005. For SQL 2008 I have added some lines at the bottom of the post, so if you are on 2008 please also check there before applying and it will work for you as well.
End of update.
Update 9 ocotber 2012: For SQL 2012, please follow the steps for SQL 2008 mentioned in this article.
End of update.
Everywhere I come to deploy SCOM a common question is to get lots of reports out of it. However after creating the reports and scheduling them for delivery it mostly stops. However as soon as somebody tries to print the reports the printers usually hang asking for Letter size paper.
The default behavior for the PDF rendering in SQL Reporting Services is to render it to Letter paper size. So how to get it to render to A4 paper size?
After some searching with a DBA colleague of mine we came across some MSDN pages explaining the options to create custom rendering options.
What to do:
On your reporting server go to the Rsreportserver.config file located in the reportserver directory. In my case this was at C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer . We need to edit this file, so open it with Notepad or something like that (something that does not break up the XML formatting in the file).
Scroll down to the section starting with Render.
You will see entries like this:
What we want to do here is for instance add a custom PDF extension. So here goes. Create some space below the PDF entry and paste something like the following:
This adds two entries to your report server export drop down list.
- make sure your name is unique (Extension name).
- The overridename entry is what you see in the drop down list.
- pageheight and pagewidth must be defined in Inches like in the example above.
- A4 is defined mostly as 8.3in x 11.7in , but in some cases the result is better when using 8.27in x 11.69 in.
Save the file and immediately you can use these formats. No restarting of services needed here if you are on the SQL 2005 version.
Original page on MSDN covering the topic.
Update 9 october 2013 on SQL 2012 version:
Use the below code and method mentioned for SQL 2008 also for SQL 2012.
Update 17 december 2009 on SQL 2008 version:
In the lines of code please use "PDFRenderer" in stead of "PDFReport". Also you must restart IIS on the box before it will show in reporting services. This is the difference with the 2005 version.
Thanks to Peter Yang from Microsoft for pointing out these changes.
As it seems to be case sensitive I just placed the SQL 2008 Reporting Services code block below for you to cut and paste.
Ran into an issue today while playing around with PHP on Windows 2008. Installed the CGI module for the IIS role (giving you FastCGI) and installing PHP. Followed all the common guides and eventually did not get a simple phpinfo to run. Got an error 500 back saying there was a problem. Thanks. After going through a lot of forum posts I finally found a page at the WebHostingHero telling us about a registry entry that did not exist. After implementing it everything worked as a charm! Good luck to you all!