February 28, 2014 at 8:20 am
I have a transactional replication environment that creates subscribers on another server as a staging area for an ETL process to a data warehouse application on a 3rd server which is the report repository. Currently the ETL process runs every 10 minutes and performs it's function across approx 150+ subscriber databases and consolidates it to the data warehouse.
I have an SLA of 2 minutes. I'd like to rework the ETL process (which run as SSIS job at the moment) to be specific to a single database and fire that one ETL proces when changes have been applied to that subscriber database only. Of these 150+ databases generally only about 8-10 are updating the subscriber at any given time per Repl Monitor. I'm thinking that if I only have a few transactions to apply to a single db the ETL would run in seconds dynamically as the subscriber is update.
The issue is how to fire the ETL process upon completion of updates to the subscriber DB? I'm thinking of using SP_Start_job passing the DBID to update the warehouse but unsure whether this is possible but if so where to trigger it.
February 28, 2014 at 9:13 am
Can you track when changes are made to tables?
The replication process looks like any user sending a DML change to the server. To trigger on that, you'd need a trigger on that table(s) that would kick off the sp_start_job. What I might do is have multiple triggers on the tables that detect changes, and insert a value into a tracking table. Then have a job that runs every minute or two that can kick off your job.
Potentially, you could detect a connection from the distributor agent (with login) as a DDL trigger or extended event and use that, but I'm not sure how reliably that would be.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply