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.
-
3Transactional 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– Kin Shah2015年08月06日 14:38:30 +00:00Commented Aug 6, 2015 at 14:38
-
How far behind production can the data in the reporting server be?Aaron– Aaron2015年08月06日 18:04:20 +00:00Commented Aug 6, 2015 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– Sir Swears-a-lot2015年08月11日 10:01:16 +00:00Commented Aug 11, 2015 at 10:01
1 Answer 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.
-
Report server also need real time data..Atul Yadav– Atul Yadav2015年08月06日 15:33:17 +00:00Commented Aug 6, 2015 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– datagod2015年08月07日 06:06:21 +00:00Commented Aug 7, 2015 at 6:06
-
Instead of an anonymous downvote I think it would be more constructive to post why you disagree with my answer.datagod– datagod2015年08月11日 17:19:26 +00:00Commented Aug 11, 2015 at 17:19
Explore related questions
See similar questions with these tags.