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