Sql-server – Copy SSRS reports to another server

SSRS Planning a Deployment would be a good article to read through.

You can read specifically on the SSRS databases here. As it states:

A report server is a stateless server that uses the SQL Server Database Engine to store metadata and object definitions. A Reporting Services installation uses two databases to separate persistent data storage from temporary storage requirements. The databases are created together and bound by name. By default, the database names are reportserver and reportservertempdb, respectively.

You general, with large reporting requirements, will run SSRS databases on one server (shared SQL Server instance) and then the SSRS components on another. Keep in mind though how licensing works, you will have two licensed SQL Server servers with this type of setup. I normally see the report server databases reside on the same server as a data warehouse. I have not seen any issue with the SSRS databases being on a shared instance with the data warehouse. As it states in the article they are only storing information for SSRS (execution log, schedules, report definitions, etc.).

The "meat" of what SSRS does resides with the SSRS component services. SSRS processes the data the report is requesting and then also works out rendering it, all of this is resource intensive if there is a high load. It will depend on what your specific needs are if it justifies a standalone server. I generally will see SSRS installed on the web server with the application that uses the reports.

To determine if I need a standalone report server I would probably consider things like number of reports (many small or many large), the frequency (internal report server for monthly reporting or serving up user reports for external web application). You may also look at the report design or standard used by the developers, if the reports do a high amount of data processing on the report side, versus letting the database engine do it.

Does Transactional replication suit my problem the most among SQL Server replication mechanisms?

Out of all of the replication options - Transactional sounds like it is what helps you the most here. It gives the minimal latency and it doesn't need to be (but can be) bidirectional. You aren't merging changes and don't need Peer to Peer.

That said - Replication isn't "free" - you need to administer and monitor it and a poor setup could make performance problems worse. You should learn about the best options for performance and the best setup for performance and keep that in mind and keep your replication environment monitored.

Transactional replication is "easier" than Merge replication. But that doesn't mean you will always have a smooth sail and can always get away without someone with replication experience helping out.

If I have an opportunity to migrate from SQL Server 2008 to SQL Server 2012 would transactional replication have some breaking changes? I've read an article on technet that there might occur errors?

Unfortunately this is an "it depends" type of question. We could theoretically give you answers from our own experiences, but you will need to test. If you do bump into issues they will not necessarily be impossible issues that you cannot fix, they will require testing and tweaking.

Isn't SQL Server 2012 Always On mechanism better to solve my problem(I'm considering this option as last one, as I'm still using 2008 R2, and yet migration to 2012 is planned by not soon)?

In SQL Server 2012 AlwaysOn has something for you here that works potentially. Availability Groups are an answer here with the concept of an Active Secondary for reading purposes. But there are licensing concerns - you need to be in Enterprise on both the primary and the read only/active secondary. With the replication approach you still need to be licensed on both ends, but this is a good option for sure. It, too, has overhead associated and some administrative concerns that you should learn about - but it makes a great option.