January 1, 2005 at 10:40 am
I just started working for a client that is searching for a replication solution for reporting. None of us has experience with sql server replication.
Situation: The company has developed a custom application that is critical to its operation and they plan to continue to use the software as well as upgrade and license/sell the software. The production database is not huge but a few tables are used extensively. One table receives almost 2 million inserts per day. At present 100 days of history for this active table is stored on the production server and the 101st is archived daily. We want to reduce the history to 7 days with all history available on the report server. We want to minimize latency (30 minutes max) and we don't want to negatively impact responsiveness for the production db and app.
Transaction replication may be the correct approach but we're not sure how to prevent the daily delete of the data from the high volume table from being replicated and we'll have to evaluate overhead. One option is to modify the software to insert to both the production and reporting dbs.
We're just getting started and any insights and suggestions would be appreciated.
Thanks
Mardy
January 1, 2005 at 11:14 am
You can use DTS to copy data to your reporting database. This can be scheduled as a job to run every 30 minutes. You will have to make sure that you have a way to filter out rows already copied. This will allow you to avoid transactional replication and also avoid modifying your software.
Another approach is to set up triggers in the tables to insert the rows in the reporting database using a linked server. I really wouldn't recommend this, though.
Hope this helps,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply