September 20, 2011 at 8:51 am
Do you always wait until after-hours to deploy a stored procedure or function to a production system? Is there any risk of a quick DROP/CREATE of a proc interrupting processing?
If it's something like a table/col update or data manipulation script, we will always wait until after-hours, but a proc change seems like it would be less invasive to an end-user of the app.
Just wanted to see what opinions were out there on the subject.
September 20, 2011 at 9:03 am
mxhxr (9/20/2011)
Do you always wait until after-hours to deploy a stored procedure or function to a production system? Is there any risk of a quick DROP/CREATE of a proc interrupting processing?If it's something like a table/col update or data manipulation script, we will always wait until after-hours, but a proc change seems like it would be less invasive to an end-user of the app.
Just wanted to see what opinions were out there on the subject.
We do this in production during business hours, but we are relying on the the developers and application team to have taken appropriate precautions to ensure that a release to production will not impact the users.... so we do it, but with the Caveat of "well, you told us to do it now".
September 20, 2011 at 9:04 am
I believe any change on a production system would fall inside a change window which would require approval from all concerned teams. In most cases, the approved change window usually falls during the non-business hours or weekends unless the required change falls under the emergency category. Meaning, that change is required to be implemented immediately otherwise the system would crash thereby affecting business.
Whether it is a simple change or a complex one, it is quite tough to get approvals from management if you want the change executed during normal working hours.
M&M
September 20, 2011 at 9:10 am
It would follow "it depends". Pushing a procedure to production during business hours is something that is done consistently (depending on the change, impact, etc).
Not entirely sure of the lifecycle, but I recall reading something that if code is running the procedure at the time, and a drop was issued, either the drop would wait or the proc is in memory at that point and won't swap in the new proc until its not being run. Going to try that now
Cheers
http://twitter.com/widba
http://widba.blogspot.com/
September 20, 2011 at 9:18 am
Of course it depends on lots of things. In a production environment changes can be made during working hours if the risk of impacting business activity is really low and if rollback procedure will not interrupt users.
Imagine that your stored procedure is used to generate reports and needs to be corrected, you can imagine doing your change during BH, after a global communication that during an interval that particular report will not be available. After the change you get a user validation and you can announce that the change finished and that the improved report is available again (or that you rolled the change back and that the report is available).
Even if a user would access this report during the change and he gets an error or even worse gets false results because the new stored procedure is wrong and you will rollback to the previous one, he was warned, but most important, no data gets corrupted.
Should you risk data corruption or inconsistence and the rollback would consist in a full database restore, I advise doing the change during off hours.
September 20, 2011 at 11:31 am
Thanks for the input- we have defined maintenance windows in place, but I was wondering if there were any drawbacks to deploying procs/functions from purely a technical perspective.
@WI-DBA I would be curious to know what the results of your tests are. I'll run some myself as well.
September 20, 2011 at 11:42 am
mxhxr (9/20/2011)
Thanks for the input- we have defined maintenance windows in place, but I was wondering if there were any drawbacks to deploying procs/functions from purely a technical perspective.@WI-DBA I would be curious to know what the results of your tests are. I'll run some myself as well.
Purely technical, no.
Of course assuming the FE can handle the changes.
I do it here for reports. But those reports are ran once an hour and I work with only 1-2 persons at a time.
They know there can be a 1-5 second window where they can request the report and it'll error out. But that happens like once a year.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply