1

I work with an environment that uses Merge Replication to publish a dozen publications to 6 a dozen subscribers every 10 minutes. When certain jobs are running simultaneously, deadlocks and blocking is encountered and the replication process is not efficient.

I want to create a SQL Server Agent Job that runs a group of Merge Replication Jobs in a particular order waiting for one to finish before the next starts.

I created an SSIS package that started the jobs in sequence, but it uses sp_start_job and when run it immediately starts all the jobs so they are running together again.

A side purpose is to be able to disable replication to a particular server instead of individually disabling a dozen jobs or temporarily disabling replication completely to avoid 70+ individual disablings.

Right now, if I disable a Merge Replication job, the SSIS package will still start and run it anyway.

I have now tried creating an SSIS package for each Replication Job and then creating a SQL Server Agent job that calls these packages in sequence. That job takes 8 seconds to finish while the individual packages it is calling (starting a replication job) takes at least a minute to finish. In other words, that doesn't work either.

The SQL Server Agent knows when a Replication job finishes! Why doesn't an SSIS package or job step know? What is the point of having a control flow if it doesn't work?

Inserting waits is useless. the individual jobs can take anywhere from 1 second to an hour depending on what needs replicating.

Chris Morgan
  • 115
  • 1
  • 12

1 Answers1

0

May be I didn't see real problem but it is naturally that you need synchronization point and there are many ways to create it. For example you could still run jobs simultaneously but let first job lock a resource that is needed for second, that will wait till resource will be unlocked. Or second job can listen log table in loop (with wait for a "minute" and self cancel after "an hour")...

Roman Pokrovskij
  • 9,449
  • 21
  • 87
  • 142
  • I need the jobs run in a specific sequence and I prefer not to create intentional blocking. Other transactions at the subscribers depend on the sequence and resources. – Chris Morgan Feb 16 '14 at 13:31
  • I see. Than if not intentional blocking why not synchronizing in the loop with tracking the log table? I think the exotics like synchronizing on SQL SERVER Alerts is less preferable way. Actually you can add new steps "on success start new job", you even can add those steps form another job, or create job in job, job also can delete itself, but still I would prefer transparent synchronization with loop and tracking events table. – Roman Pokrovskij Feb 17 '14 at 17:06
  • I think I follow you and sounds like a good approach, but I don't know how to do these things in this context. Can you be more specific in how to do it or point to a source that I can study? Thanks for the response. – Chris Morgan Feb 23 '14 at 20:30