Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CorpDirect
Yak Posting Veteran
92 Posts |
 Posted - 2005-12-13 : 11:31:51
|
| Hello all,We're starting to see a pretty heavy impact with reporting from our production transactional database, to the point of interfering with transactions, and would like to do something to lessen or redirect the load. Reporting is only going to grow -- we're giving clients web access to some of their data -- so it may be a better solution to report from a different database.We need to have minimal lapse between source data and reportable data, and must be able to use the reporting database throughout the time it is being updated/refreshed from source. Can replication help with this?If it would help I would be glad to explain the environment and business requirements in greater detail.Thanks for your input,Daniel |
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2005-12-13 : 17:35:36
|
| More thoughts -- I really don't know that much about how replication works so wanted to get feedback on whether this will be an effective solution for us.Our reporting applications are sometimes used to generate several reports in a minute, and typically hundreds a day, with increasing use as we go along. Since the data reported needs to be very current, we are considering using replication with a frequent interval, like so:Order Processing DB (transactional) --> [REPLICATION] --> Reporting DB (read-only)For our business processes, replication every 3 minutes would not be too often. From the system perspective, is this an excessive load on a system where hundreds or, potentially, thousands of records in dozens of tables can change in that interval?Since reporting from the live database is negatively impacting workflow at this time, I am concerned about the impact of replication as well. Does replication use any kind of locking when reading data from the replication source?The reporting applications are using SELECT only (views, stored procedures, etc.), but locking issues are encountered when the order processing system tries to write to the same tables while reports are being generated. Could the replication process encounter (or cause) some of the same issues?If anyone could give me a high-level overview on the considerations and impact of replication, that would be very helpful!Thank you,Daniel |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-12-15 : 01:07:40
|
| From experience, this is not a problem...we've been using this type of setup for 2 years now and it is working nicely with no locks or blocks encounteredthe only problem that you might encounter is if a replication error that may be caused by change in schema or any error that might invalidate replication... in which case you need to run snapshot and this will lock up the tables while generating the script and bulk copy depending on the total rows affected--------------------keeping it simple... |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2005-12-19 : 13:08:06
|
| Thanks Jen!I've one other question before trying this in a test instance:We use a single named instance for our production environment, with databases for various apps. I'm sure research and/or trial will tell me if I can replicate selected tables to another database in the same instance, but my question is, which is the better choice for performance?Would it be better to have the replication source (publisher) and the replication target (subscriber) in seperate instances, or would it work better to keep everything within the single existing production instance?Thank you,Daniel |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-12-28 : 01:13:25
|
| sorry for the late reply daniel...hope you have accomplished this. if this is the typical reports/production setup, i suggest you use two servers (one for prod and another for reports), otherwise you'll probably have the same performance issuehope this helps...--------------------keeping it simple... |
|
|
|
|
|
|
|
|
replication for reporting?