April 8, 2010 at 11:07 am
We have a need to have a updatable 24-7 database running and another copy of DB that is used for analysis and reporting. The need for the second is due to unknowns like "what ifs" and I don't what nothing to effect the updatable DB.
So what I have originally done to keep both of these db in sync as much as possible was to use log shipping and kept it in standby/ readonly mode. However one the requirements was to keep in sysnc as much as possable. So we were log shipping every 15-20 min or so. Problem with this is that the restore process requires that there are no active connections. So I set up a script so that if a connection was idle more then 3600 sec then kill it. Then the restore happens.
Going forward, I can make a case to have the data sync changed to a couple of time a day also we have found the reporting data is not hit hard at all. We are also in the process of consolidating database servers and the reporting database is to be migrated to the same box as the updatabase DB is located.
So for my couple of questions, I ask the experts.....
Is there really a need to have a seperated DB if they are going to be on the same server anyway, just to cover the "what ifs"?
I do relize that if updates are happening then the reporting users may be blocked while the updates are happening. We are not on SQL 2008 yyet so I can not use the Resouce governor to controll a bad query.
Also, Would you continue down the log shipping method to keep the two insync or would you choose replication or other means? Wat about snapshots? Would that be any benefit in this configuration?
Thanks for your help!
April 8, 2010 at 11:26 am
I would not see the sense in keeping a copy of the database on the same server for "what ifs"- as long as you are running in full recovery mode and take regular full and tlog backups. I WOULD see the benefit of having separate instances for transaction processing (inserts, updates and deletes) and reporting (selects).
This seems like the perfect candidate for transactional replication.
April 8, 2010 at 1:57 pm
I agree with NJ-DBA that transactional replication may be the right solution for this scenario.
The Production-Reporting tandem of databases is a valid strategy - usually a first step in the Production-DWH path.
The whole idea of this Production-Reporting tandem is to take all reporting workload out of Production which on a heavily hit production environment usually is a good idea. Having said that, Production and Reporting should be running on different host computers.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 8, 2010 at 9:36 pm
Transactional replication popped into my mind too. But you will need to have primary keys on the tables. If you don't have primary keys, then you have to do snapshot replication which may not be good with large tables because the entire table gets copied each time.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply