0

I am considering implementing transactional replication on a 70GB main db (soon to migrate to SQL server 2014) with medium-high level CRUD activites. We use SQL Reporting Services (2005-> 2014) for heavy overnight report generation and medium ad-hoc report generation during the business hours.

My thoughts are using transactional replication with 1 new VM for main (publisher) db, 1 new VM for distributor + 1 new VM for subscriber and it's own read-only db for SSRS + 1 new VMN for Report Catalog + 1 VM Report server (5 new VMs).

I intend to use SSRS to retrieve data from subscriber (read-only, with subset of tables from the publisher), not from publisher.

My questions if I want to save some VMs (without loosing too much performance): -Can I put the Report catalog on the same server as the subscriber?, or -Combine distributor and subscriber db on the same VM, but leave Report catalog on a separate VM?

Any other recommendations? Thanks in advance. WM

WML
  • 173
  • 13

1 Answers1

1

The set up we currently for two of our clients is as follows

Publisher (VM) - Server hosting the main application database. Subscriber (VM) - Server hosting subscriber database, distribuitor (we use a pull subscription) and report server.

The database size is approx 10gb, also medium to high CRUD

The process works, one of the down sides is when the client runs intensive queries against the subscriber using a table the transactional process is trying to update. The transactional replication process does not commit transactions to the subscriber. Next the error messages are not helpful at the best of times. If you can find another solution eg availability groups with a read only replica might be better solution.

SQLBobScot
  • 694
  • 5
  • 20
  • Thanks...but wouldn't AG with a read-only replica have the same latency problem? Or the problem for AG will be minimal if the AG is configured in Async-commit mode? Excuse my ignorance. – WML Apr 21 '16 at 03:55
  • In my case DR is not a high requirement – WML Apr 21 '16 at 03:57
  • You make a good point about the latency, l will check as l am not sure. – SQLBobScot Apr 21 '16 at 08:13