what is the best way to replicate database for SSRS
replication, sql-server, sql-server-2008-r2
Solution
You need Transactional Replication for your case. Here is why you would not need the other 4 cases:
Mirroring
- This is generally used to increase the availability of a database server and provides for automatic failover in case of a disaster.
- Typically even though you have more than a single copy of the database (recommended to be on different server instances), only one of them is active at a time, called the principle server.
- Every operation on this server instance is mirrored on the others continuously (as soon as possible), so this doesn't fit your use case.
Log Shipping
- In this case, apart from the production database servers, you have extra failover servers such that the backup of the production server's database, differential & transactional logs are automatically shipped (copied) to the failovers, and restored.
- The replication here is relatively scheduled to be at a longer interval of time than the other mechanisms, typically ranging from an hour to a couple of hours.
- This also provides for having the failver servers readies manually in case of a disaster at the production sites.
- This also doesn't fit your use case.
Merge Replication
- The key difference between this and the others is that the replicated database instances can communicate to the different client applications independent of the changes being made to each other.
- For example a database server in North America being updated by clients across Americas & Europe and another one in Australia being updated by clients across the Asia-Pacific region, and then the changes being merged to one another.
- Again, it doesn't fit your use case.
Snapshot Replication
- The whole snapshot of the database is published to be replicated to the secondary database (different from just the log files being shipped for replication.)
- Initially however, for each type of replication a snapshot is generated to initialized the subscribing database, i.e only once.
Why you should use Transactional Replication?
- You can choose the objects (Tables, Views, etc) to be replicated continuously, so if there are only a subset of the tables which are used to reporting, it would save a lot of bandwidth. This is not possible in Mirroring and Log Shipping.
- You can redirect traffic from your application to the reporting server for all the reads and reports (which you can also do in others too, btw).
- You can have independent batch jobs generating some of the more used reports running on the reporting server, reducing the load on the main server if it has quite frequent Inserts, Updates or Deletes.
Problem
I have installed SQL server database (mainserver) in one instance and SQL server database for RerportServer in others. what is the best way to replicate data from mainServer to report Server? Data in mainServer changes frequently and actual information in the ReportSever is very important. And there is many ways to do this: - mirroring - shipping log - transactional replication - merge replication - snapshot replication are there some best-practices about this? Thanks