June 2, 2006 at 12:01 pm
I need help in finding a utility that can move database objects and run scripts in a set order on a scheduled basis. Currently our area has become out of control due to programmers send us stored procs, views, etc in scripts that they want moved from Qual to Prod, and the moves need to occur off hours. We can't schedule them using SQL Agent since the scripts are too long to paste into a job step. (don't go down the cmdshell route, security rules won't allow it) We also get monster long scripts of updates they want run too that they need the DBA to run since they don't have permissions on the databases. Quite often we get requests of "run these two schema change scripts, move these 6 stored procs, and then run these 4 data update scripts" that need to be done off hours and in a certain order. We need something that will show the output of the scripts run, and if something fails, send us a page (e-mail) to note that it failed. Does anyone know of a product that has this type of capability? All replies greatly appreciated!!
June 5, 2006 at 8:00 am
This was removed by the editor as SPAM
June 5, 2006 at 8:41 am
I know it would be rather difficult to manage, but I'd imagine you could do a lot of what you want via DTS. You could take thier scripts and create Sprocs if they're too long and call the sprocs from the execute SQL task. DTS also should allow you to move your sprocs ad such to other databases through the transfer tasks. Then everythign could be scheduled via SQL Agent. You could even perhaps set up a procedure to have the SQL agent call the DTS Job from within a sproc that you'd send global variables to for what needs to be done.
June 5, 2006 at 9:14 am
There's always good old fashioned DOS Batch files. Use OSQL to fire up the script in SQL and MSTASK to run the batch file on schedule.
June 5, 2006 at 10:57 am
Both replies from Luke and Bill are technically correct if you would be talking about the same job on a regular basis. As I understand your post, this is not the case here. You are talking about schema and data changes for the production database which is called "UPGRADE" and is run once. I would not let it run unattended even if it was tested in the QA. I had upgrades that ran fine in Development, even better in QA, they just did not run well in production because of the server differences or security differences or previous historical issues or just would not run. If the upgrade is requested to be done OFF HOURS it means that downtime is not really an option. You would not want to be responsible for the failed upgrade because it was not supervised correctly.
What I would do is to talk to your manager so he/she would schedule you to work off hours for an additional bonus or time off or he/she may negotiate with the application support for the scheduled maintenance hours.
Regards,Yelena Varsha
June 5, 2006 at 2:26 pm
I agree with Ten. Once those scripts are passed from the deveolper to the DBA group to run in QA or Prod, they are now your responsiblity. If you schedule these request without viewing them, how could you be certian of what is being executed? How would you know if the developer was trying to sneak something in. I don't know if your being audited, but if you are, I can't imagine this approach would fly with the auditors. It's your responsibilty to view what is going into the database. That's why you have a job!!!!!
June 5, 2006 at 2:52 pm
We have reviewed them, and have tested them in our Dev, QA and in some cases several other environments against copies of our prod databases. In our case we have 89 SQL servers and the amount of moves to prod can be several objects on several servers for many systems over the course of a night. Hence why we wanted to find a product that will do the moves and notify (page) us when there are failures. Right now the manual process is not getting us anything for an audit log, and the auditors aren't happy with that! On our mainframe side we have an automated move process with documentation, approvals, etc, for SQL server we have nothing. That is why we are looking to see if there is an app that builds the move changes, and allows them to be scheduled and noted in a history to make our auditors happy and get the manual process (which they don't like) out.
June 5, 2006 at 5:52 pm
Take a look at NAnt http://nant.sourceforge.net Its free and has a lively community.
Its intended as a build tool, but I am currently working with it to do my deployments too. It will require some customization, but since you have already vetted the sql, you could set it up to run all the sql files in a particular directory against a server, then move the files to another directory for audit trail purposes. Each activity (NAnt calls them targets) has an OnSuccess action and an OnFailure action so you can take the appropriate actions in each case. SMTP email is built right in so you can mail your distro with the status.
I have several scheduled build/deploy jobs and it does a wonderful job. I have it mail me if it succeeds or fails, with the appropriate message.
June 7, 2006 at 6:22 am
Check out Embarcadero's Change Manager, maybe that will do what you need.
Mark
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply