Not sure if this is the best site for this question because I am looking for opinions rather than a technical answer. Sorry, in advance if this posting is not appropriate. Just let me know and I will remove it. I posted on another SQL Server site and have not received any opinions so I am trying this one.
We are running SQL Server 2012 EE SP3 and SSRS (2012).
I am looking for some opinions on the pros and cons of using Visual Studio (VS) or SSRS Report Manager for deploying SSRS Reports created using VS. I am a DBA and I have played with both methods however I prefer to use Report Manager and not a developer-centric tool. We are a small shop (2 developers).
With VS, I right click the Report Project, select Properties, fill out the Report Property Page by setting Overwrite Options, Target Folders and URL, TargetServerVersion, and click OK. Then right click the Report Project and click Deploy.
In playing around with deploying using VS, I was able to upload a higher version SSRS report (created with VS 2016) to the SSRS 2012 Report Server and the report worked (executed successfully); I do not want this type of thing to be allowed. This tells me that the VS deployment is doing something(s) behind the scenes to make things work. However, the same higher version SSRS report did NOT work when it was deployed with Report Manager (SSRS 2012). It failed with "The definition of this report is not valid or supported by this version of Reporting Services." In addition, I was able to upload an SSRS Report (created with VS 2012) which contained 'unused datasets'. I do not want this type of thing to be allowed. After further research, I found out that 'unused datasets' are not a 'good practice' because they are executed each time the SSRS Report is executed. Google "SQL Server Reporting Services Best Practices for Performance and Maintenance" by Ghanesh Prasad.
I prefer Report Manager because it is a one-stop shop in that it allows me to manage the entire report server reporting activities. I can manage folders/reports, set the security of folders and items, deploy and publish reports, create subscriptions, etc. With Report Manager, I use the following steps for a report that uses shared datasets. (There are less steps if the report has embedded datasets.) Of, course I create the data source.
- Upload each dataset (.rsd file).
- Link each dataset to the data source (creates shared datasets)
- Upload each report definition file (.rdl file)
- Link each report dataset to the shared dataset in SSRS
I also believe that using Report Manager, provides me (DBA) more control over what is being uploaded to our SSRS. It helps me keep the SSRS cleaned of clutter, reports created with higher versions of VS, and 'unused datasets' (I request the developers to list or identify the 'used datasets' in the report.) This is important since our SSRS runs on our database server.
Again, just looking for some opinions on the pros and cons of using Visual Studio (VS) or SSRS Report Manager for deploying SSRS Reports created using VS.
Thanks in advance.