CODEWITHSUNDEEP
sql-serverreporting-services
WML
WML

Reputation: 185

SQL Server reporting database catalog and Reporting Server

I am doing some research on migrating our SSRS 2005 to 2015. ATM I have a DB VM that host the production DB, Report DB, Report TempDB, and also run the Reporting Services/Server. Highly problematic as you can see.

I am considering the following scenarios for the new environment (don't worry about the $$$ atm):

DR is not an issue, the agreed RTO is 4 hrs(!)

We don't have a lot of users, but a lot of reports (> 200), some are graphic intensive. Ad-hoc plus off-hours report snapshots created. We are experiencing some timeout issues. (once or twice a day)

I am looking for one of the scenarios above that gives the best performance.

Can anyone suggest which one is the most sensible approach? Can provide more info.

Thanks in advance. WM

Upvotes: 0

Views: 182

Answers (1)

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

If your "production db" is some kind of OLTP system then this should be completely separated from any reporting or analysis activity. If you have the resources then I suggest creating an AlwaysOn group (the successor to mirroring in SQL 2005) and use the secondary replica as the source for your reports, e.g.

  1. production db (1 VM)
  2. AlwaysOn secondary replica of production, in read only mode (1 VM)
  3. ReportServer db & reporting services (1 VM)

This keeps your production db from being impacted by reporting queries, by offloading the work to the replica (which also doubles as your HA/failover db) and keeps the report server components nicely packaged up on a single VM, which should be sufficient based on your description of the workload.

Upvotes: 1

Related Questions