0

I am facing issue of time out and slow data response so I did search on Google and found that I could create a server which replicates main database data to secondary database and both on same server instance; main database used for real time read and write and the Report server will get data from the secondary database so when someone runs a large report then it will not affect the main database and the application will run with out any issue.

What replication solution I have to use for situation? I am using SQL Server 2012 Standard Edition.

3
  • 3
    Transactional replication is the one that will fit your situation. If there is a timeout issue then it has to be blocking. Check the wait stats and see if the table is having proper indexes and stats are up-to-date. – Kin Shah Aug 6 '15 at 14:38
  • How far behind production can the data in the reporting server be? – Aaron Aug 6 '15 at 18:04
  • Before you invest significant time and effort to replicate your dbs onvthe same server i would highly recommend trying to identify your bottleneck. You may find that the issue can be resolved by tuning and maintenance. Replication on the same server might actually be counter productive. You will be taking cpu, ram resources from your production instance and doubling your storage and io requirements. How big is the db. Server spec? Unless you can move/spread the workload to another machine i wouldn't have much confidence in this approach. – Sir Swears-a-lot Aug 11 '15 at 10:01
-1

If the data in the reporting database does not have to be real-time, the I suggest you do not implement replication but instead go with log shipping.

3
  • Report server also need real time data.. – Atul Yadav Aug 6 '15 at 15:33
  • Then you better be prepared to seriously fine tune your code, and beef up the hardware. To keep the database updated in near-real-time while reports are running transactions are being processed is no easy task. – datagod Aug 7 '15 at 6:06
  • Instead of an anonymous downvote I think it would be more constructive to post why you disagree with my answer. – datagod Aug 11 '15 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.