Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MichaelHLutz
Starting Member
19 Posts |
 Posted - 2009-01-01 : 23:20:01
|
| My team has been given a requirement to apply stored procedure changes in production while users are actively using the system. We realize this is not a best practice but there's nothing we can do about it right now. The names of the procs must remain the same, and the signatures will be backward compatible as well as the logic contained within.Here is our dilemma : If we DROP and re-CREATE each procedure, any active execution(s) of the procs will return an error message and any attempt to execute the proc before the re-CREATE will fail. The error message returned when a proc is executing and is dropped concurrently is "Could not find stored procedure '<proc_name_here>'." It will finish, but it returns an error which poses a problem for us.ALTERing the procedure is not much better, albeit a little better. SQL Server similarly returns an error for any actively executing instances of a procedure if it is ALTERed while it is running. Specifically, SQL Server returns : "The definition of object '<proc_name_here>' has changed since it was compiled.". This only happens if the ALTER runs while the procedure is actively running, and the error applies to every SPID executing the proc.So this made me think : Is there a full-proof way of meeting this requirement? Obviously, ALTERing is better than DROP/CREATE because the window of creating problems is shorter. But it's still not foolproof becuase SQL Server returns an error (it does finish execution with the previous definition, but even just the error message is a problem for us).We're really in a bind here, and we suspect there's not a perfect solution but thought it made sense to throw the question out for validation.Thanks so much for your help. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-02 : 08:12:00
|
| You can alter the proc when there is no server activity or is minimal like (1-2 AM). |
 |
|
|
MichaelHLutz
Starting Member
19 Posts |
Posted - 2009-01-02 : 09:37:58
|
| Good idea. Unfortunately for us, our system is truly 24x7. The system is an elaborate ETL process running on multiple servers that processes two-hundred Gigs data minimum a week.So we have a 24x7 requirement and there really is no "downtime" opportunity, even at night.In a nutshell : What we need is a mechanism to apply a new version of a stored procedure in a live environment that is guaranteed not to cause a SQL Server error, even if there are active executions of the procedure at the time of applying the DDL for the proc.I have been unable to find a solution for this.Is there one???Please help,Mike |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-02 : 12:17:42
|
quote:
Originally posted by MichaelHLutz
Good idea. Unfortunately for us, our system is truly 24x7. The system is an elaborate ETL process running on multiple servers that processes two-hundred Gigs data minimum a week.So we have a 24x7 requirement and there really is no "downtime" opportunity, even at night.In a nutshell : What we need is a mechanism to apply a new version of a stored procedure in a live environment that is guaranteed not to cause a SQL Server error, even if there are active executions of the procedure at the time of applying the DDL for the proc.There is no such thing in SQL Server. You need to find proper time by tracing particular SP and apply the changes.I have been unable to find a solution for this.Is there one???Please help,Mike
|
|
|
|
MichaelHLutz
Starting Member
19 Posts |
Posted - 2009-01-02 : 13:54:14
|
| thanks sodeep, that answers the question!Regards,Mike |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-01-08 : 08:23:41
|
| What about creating 2 Sp's.Sp#1....check some "control" table/variable. if notset, execute SP#2....your real code. if set, wait for some minor time delay and then loop for the test again.outside of SP#1....when you want to update sp#2, set the control variable, use some process to determine sp#2 is not active (wait for all existing processes to end) and then update (alter) SP#2 and then reset the control variable.in this way you may be able to create mini-downtime window which is not visible to the end users (sp#1 works...albeit slowly sometimes...but it shouldn't fail)....but the key for this approach working would be to minimise the length of the total update cycle... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-08 : 09:06:05
|
quote:
Originally posted by MichaelHLutz
Here is our dilemma : If we DROP and re-CREATE each procedure, any active execution(s) of the procs will return an error message and any attempt to execute the proc before the re-CREATE will fail.
No, that is not true. Once the exeuction has started it is following the query plan.Here is a proof.1) Open two new query windows against same database.2) In first window, copy and paste this code snippetCREATE PROCEDURE dbo.uspTwitASSET NOCOUNT ONWAITFOR DELAY '00:00:30'SELECT GETDATE()GOEXEC dbo.uspTwitGODROP PROCEDURE dbo.uspTwit 3) Now execute the code.4) You now have 30 seconds to copy, paste and execute next snippet in second code windowDROP PROCEDURE dbo.uspTwitGOCREATE PROCEDURE dbo.uspTwitASSET NOCOUNT ONSELECT '+46 760 170 900'GOEXEC dbo.uspTwit 5) See the result for second code window.6) Switch back to first code window and wait until procedure finishes.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-01-08 : 09:07:51
|
It is however true that calling the stored procedure will fail during the few milliseconds between the DROP and CREATE as seen in step 4.You can use ALTER instead of DROP/CREATE in code window 2, but I am not sure how that will affect query plans and statistics.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
Foolproof mechanism for applying SP changes?