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
Best Answer
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
msdbis 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.