I have an OLTP server and I have another server that serves as the Warehouse. There are a total of around 100 tables and I am inserting/updating the data in the warehouse based on the primary key and Modified-date using an SSIS package and linked server connection. The complete sync process takes around 20-25 minutes and hence the automated job is scheduled to run every 30 minutes. But due to this, I am having a lag of 30 minutes in my warehouse. Do we have any other way out to sync the data between OLTP and warehouse that can reduce the time lag. I don't want to use triggers as it will have impact on the performance of the transactional database.
Asked
Active
Viewed 754 times
0
-
1I think this question is a little too broad for [so]. I would suggest you find out why your SSIS package takes so long and see what you can do about that. You might wind up with questions on optimizing certain queries or tables or something, in which case those (more specific) questions will be a better fit for [so]. – John Saunders Jan 08 '15 at 15:26
-
You could do replication to a copy of your OLTP and put triggers on the copy to refresh your OLAP. – Tab Alleman Jan 08 '15 at 15:35
-
What is your bottleneck? What does a sample SSIS package look like? You mention "linked server" how are you using it? That's a general performance smell in my book – billinkc Jan 08 '15 at 15:41
-
How many rows are you moving? How complex is your logic? For a (reporting?) warehouse, is being 30 minutes behind really a big deal? – Andrew Jan 08 '15 at 15:43