15

We have a 3rd party vendor trying to integrate 2 different applications where both DBs reside on our SQL Server instance with 150+ other DBs, and they want to create a MSDB job to "synch" the 2 different applications every 5 minutes (at first they wanted to run it every minute).

My initial hunch is that they should instead do this somehow in the Application tier with a Windows scheduled job, or perhaps even a dreaded trigger (which we typically resort to in situations like this).

I prefer to keep MSDB jobs reserved for DBA tasks as much as possible to reduce the clutter there, and have also run into slow querying of MSDB when viewing job histories with super active jobs like this (which also drown and drop out important job histories of more important things like backup histories). But then again, maybe my preferences are wrong and I need to make some space for the Application tier in MSDB and roll up my sleeves and fix problems of job histories taking forever to load when I need to retain a lot more history entries to capture the important stuff like backups (or purge the hyper active job entries).

Another issue I have is that now I need to give this vendor "sysadmin" rights instead of only "dbo" rights on only their DBs when they perform their upgrades via their GUI and hope they don't blow up the instance where my mission critical DBs are (one of the downsides of consolidation).

I guess I can put them on another "isolated" instance where we put all the vendors that don't play nice, but then we need reconfigure the Applications to point to the new SQL instance (sigh unfortunately not trivial in this case).

The vendor already pushed back on my concerns talking about how bad triggers are. So, I "googled" on this a bit and came up empty. Has anyone seen any link out there "authoritative looking" that this is a bad idea and I can refer them to it? Or should I embrace their approach?

I don't believe I've ever posted in a sql forum before asking for help, so hopefully my inquiry is properly framed.

EDIT: We're running SQL Server 2008 Enterprise R2 x64 SP1 (Thanks for pointing out that I forgot to mention version!). Hmmm, hopefully they don't need to change their MSDB upgrade scripts when we go to a newer version.

Thanks for your time! Rich

  • Nicely worded it is. Perhaps you can add a tag with the specific version you use (and also mention in the question the edition.) Not sure if there are differences between Standard and Enterprise but it may be relevant. – ypercubeᵀᴹ Jun 27 '13 at 15:42
  • You can always tell the job to purge its own history (you can delete from the MSDB tables quite easily), so "slow querying of history tables" should not be a factor. I'd be more nervous about letting an external process handle this, precisely because I'd have less control over history. Also if 5 minutes is "current enough" why turn to triggers which will affect every single DML operation in real time? – Aaron Bertrand Jun 27 '13 at 15:50
  • PS I highly doubt any of their job creation scripts would have any changes between 2008 R2 and 2012 unless they actually change the job functionality (which wouldn't be a version-specific thing unless they take advantage of some new feature). The job script itself should not change. – Aaron Bertrand Jun 27 '13 at 15:51
  • 2
    You don't necessarily have to give them sysadmin to let them modify SQL agent jobs--check out msdn.microsoft.com/en-us/library/ms188283(v=sql.105).aspx – SQLFox Jun 27 '13 at 15:51
  • Thanks for the quick response everyone! I'll embrace their approach and look at the purge along with not giving them sysadmin. – rzuech Jun 27 '13 at 16:10
12

IMO your vendor is actually on the right track.

An application layer job would require him to redo a lot of out-of-the-box SQL Agent features (eg. logic not to run a job that is already running, come up with a security and credentials store solution, integrate job results with error reporting and results tracking in SQL etc etc etc etc). And, most importantly, provide a backup/HA/DC solution for the scheduling. You do not want your disaster recovery story to be 'and after you finish recovering the standby server go create these 50 NT scheduler tasks'. So he is right in pushing back against using the system scheduler, it has nothing of the features and capabilities the Agent has.

He is even more so right in pushing back against triggers. Replacing a periodic job with a synchronous trigger increases request latency, increases cohesion and coupling (think schema changes...), increases downtime risk due to sync issues (trigger error->app error vs. job error->fix and retry later), dramatically increases deadlock problems (due to cross updates between tracked/trackee) and has many many more problems.

The easiest solution is indeed a Agent job, and msdb is by no means reserved for DBAs. A more fancy solution would be to use conversation timers and internal activation, which would have some advantages over Agent jobs, primarily due to containment (everything is inside the app DB, think mirroring failover scenarios), but I can totally understand if your vendor is reluctant to try out something that requires a very specific know-how. BTW I do hope you don't mean one job every 5 min per DB.

As for sysadmin permissions: the name of the game is code signing. You can give any permission to specific procedures, inspected and validated by you, by signing them with your private certificate.

  • Here is a link to your answer on Stack Overflow that shows an example of conversation timers and internal activation: stackoverflow.com/a/4079716 – Jon Seigel Jun 28 '13 at 1:11
  • 1
    I marked this as the accepted answer which seems to be the consensus. The biggest thing I took away from all this is it's okay for the Applications to use frequent MSDB jobs, and I'll just need to use the links here provided to do it in a proper manner for security. Very insightful everyone, and thanks for your comments and time! – rzuech Jun 28 '13 at 13:00
2

My personal preference would be to use triggers to handle at least some part of the synchronization. I don't particularly care for scheduled polling synchronization, as you have to deal with potential conflicts, stale data, performance impact of the repeating job, etc. If they want to do it as aggressively as 1 to 5 minutes, I'm guessing it's to mitigate conflicts and staleness, and the immediacy of a trigger would address that.

If it's all happening within the same server, you're probably fine putting the sync code within the triggers, or having the triggers call a stored procedure that synchronizes each affected record. If the synchronization spans servers, or you want to make sure that having one database offline won't prevent the other database from being usable, look into using Service Broker to handle asynchronous updates. I've done this to synchronize sales entry figures with CRM data between two different servers, while allowing for either server to be taken offline without affecting the other application. Once it comes back up, Service Broker delivers the messages and updates the data on the remote server.

And there's really nothing inherently bad about triggers, but like most aspects of T-SQL, it's very possible to write code that performs horribly. I wrote an article about the common pitfalls of triggers, if that helps any.

Writing Well-Behaved Triggers

  • Yes the 2 DBs are on the same server instance. Personally, I would've done triggers too and their Applications are pretty small potatoes relative to some of the pretty heavier duty DBs we have there. But, I don't think I can really convince the vendor otherwise since I can't really point to any "best practice" stuff out there regarding not to use MSDB jobs for Application synching. Plus I respect Aaron's opinion quite a bit so I'm not going to press them. db2 I read your link and found it quite informative, and Service Broker is another good option I didn't even consider. Thanks! – rzuech Jun 27 '13 at 18:34
  • Yeah, if you're ever worried about triggers failing (offline database, schema changes, etc), then Service Broker is the way to go, assuming you want (near) instantaneous synchronization. – db2 Jun 27 '13 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.