April 29, 2009 at 7:16 am
Can Report Server run on a separate from DB box?
We suspect Report Server causes problems for production db
and are planning to move it to another server
but I don't have much experience with Report Server.
Can you recommend any good online resources
about Report Server (SQL Server 2005)?
April 29, 2009 at 7:30 am
It can. One of my customers has a reporting services box with a data warehouse on it. We have two other databases on seperate servers that I access using the data warehouse reporting services install. This makes it easy for me because everything is in one place. You will have to install a sql server database on the other machine though.
If the problems you speak of are performance related, seperating reporting services off probably won't help because the report processing of the queries will continue to run on the server that is being queried.
Here's a good place to start for ssrs report administration:
http://msdn.microsoft.com/en-us/library/aa337469(SQL.90).aspx
April 29, 2009 at 7:38 am
One more:
http://msdn.microsoft.com/en-us/library/aa337424(SQL.90).aspx
April 29, 2009 at 8:09 am
Hi,
A common solution is to use SQL Server Replication to create a publication made up of a subset of your production database data.
The publication can then be scubscribed to by an alternative server, thereby seperating your reporting from your OLTP platform.
You can even take it a step further and split the Report Server Web Service out onto a seperate server also i.e. dedicated web server, if you wish to isolate the query processing from the rendering of reports.
Hope this makes sense but feel free to contact me directly if you need assitance.
April 29, 2009 at 9:27 am
So they want me to move Report Server from C:\ drive to D:\ drive
on that production server.
Can it be done?
When Report Server is installed where its program files are saved?
April 29, 2009 at 9:35 am
Sure it can be done. With more details about the problems you are trying to solve we can give you some better advice. Moving the reporting services install is probably not going to make a huge difference.
April 29, 2009 at 9:37 am
So to move ReportServer stuff from C to D
I guess i need to re0install Report Server?
Is that correct?
Or maybe i just can change he temporary files location
fro Report Server?
April 29, 2009 at 9:45 am
I would really not bother moving the installation files from one drive to another as, unless someone has evidence to the contrary, it will provide no noticeable performance benifit in my opinion.
April 29, 2009 at 9:48 am
One further consideration, I notice you state that you believe Reporting Services to be the source of your performance issues.
Before going ahead with any changes, I would suggest establishing with 100% certainty, what is cuasing the performance issues.
If it is indeed Reporting Services, which component is consuming the resource, is it the query processing or the Report Server Web Application, i.e. you may want to look at IIS configurations, Application Pool settings etc.
April 29, 2009 at 10:42 am
This morning "ReportServerTempDB_log.LDF" grew to 7 GB
and caused disk space problem on C:\ drive.
So I detached and attached it on D:\ drive
It's part of the bigger investigation.
The client is experiencing frequent TIMEOUT errors
in their .NET web application
and they believe it's caused by long-running queries
but this all is being discussed in another Thred:
http://www.sqlservercentral.com/Forums/Topic705499-146-3.aspx?Update=1
I guess for now temporarily i can forget about ReportServer.
i will go back to Traces and try to figure out what SQL is taking too long to execute.
Thanks.
April 29, 2009 at 10:46 am
this query will help:
--http://www.sqlservercentral.com/articles/DMV/64425/
use master
SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID) -- Ignore this current statement.
ORDER BY 1, 2
April 29, 2009 at 11:11 am
An essential move not having your Reporting Server database on the C:\ Drive (which I assume is where your OS is intalled).
Hopefully your system database are also not sitting there ? 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply