Scripts to Deploy SProcs - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Scripts to Deploy SProcs

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 05:35:36

For years we've done our SProc Create scripts like this:


IF EXISTS MySproc ... DROP MySproc

... log the attempt at creating this SProc ...

CREATE MySproc

GRANT EXECUTE ON MySproc TO SomeGroup(s)

The snag with this is that if there is a syntax error the DROP runs, but the CREATE fails.

I did consider changing the scripts to use ALTER but ditched that:

I would need to create a "stub" SProc if there was no existing Sproc, and a syntax error in the ALTER would then leave the Stub behind (which might go unnoticed). Also, ALTER would leave the permissions in place - which is good and bad, for us we would prefer to have the specific GRANT EXECUTE statement in the Script run - i.e. no additional permissions accidentally left behind.

Anyway, I parked the whole thing and we stuck with what we had.

I now have a very slow connection to a server which takes anything up to a minute to re-create an SProc. Clearly fixing something that is running live is going to be disruptive.

ALTER would solve this, so would cut & paste the code to a Terminal Services session so I can run it direct from SSMS on that machine ... but I aint going to do that either!

Then I wondered about:

IF EXISTS TEMP_MySproc ... DROP TEMP_MySproc

CREATE PROCEDURE TEMP_MySproc

IF EXISTS TEMP_MySproc
BEGIN
DROP MySproc
RENAME TEMP_MySproc to MySproc
GRANT EXECUTE ON MySproc TO SomeGroup(s)
... log that this Sproc was created successfully ...
END
[/code]
the Rename part should be fast, and maybe putting it in a TRANSACTION BLOCK would make things that wanted to execute it wait until the replacement was available?

Thoughts, suggestions and any potential GotCha's would be appreciated. Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-14 : 08:12:02

Another option you might consider is a separate schema for creating new objects, and switch them over to the final schema if and when the deployment succeeds:

CREATE SCHEMA new
GO
CREATE SCHEMA Xfer --placeholder for switching
GO
CREATE PROC new.ABC ...
GO
IF OBJECT_ID('new.ABC') IS NOT NULL BEGIN
   ALTER SCHEMA Xfer TRANSFER dbo.ABC
   ALTER SCHEMA dbo TRANSFER new.ABC
END
-- add code to drop all Xfer schema objects

Granted it's a fair amount of code editing in source control but you're never in danger of losing an object or data, you can always switch back if something goes wrong.

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 09:10:13

Hmmm ... interesting, I would never of thought of Schemas; thanks for that.

I reckon I could have have an SProc that did that, which might mitigate the source change effort ...

Always create a new Sproc in "XFER" schema.

At the end call usp_PutLive('MySprocName')

that could check if MySprocName exists in XFER, and if so move the original from "dbo" to "ARCHIVE" schema (pre-deleting anything that might have been there) and then move the new "XFER" version to "dbo".

usp_PutLive could also winge if it found anything else lurking in XFER (i.e. failed from some previous attempt)

For large rollouts (where we concatenate all SProc Create scripts into a single "upgrade" script file) we could just have one usp_PutLive call at the end - to move ALL Objects in XFER to dbo.

usp_PutLive could also log the names of things that it moved and, I suppose, things that it was asked to move that did not exist (i.e. failures). Might be some useful reports that came out of that - anything that failed that did NOT have a more recent Success log entry - i.e. things in a larger batch that had failed.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-14 : 09:36:14

quote:
usp_PutLive could also winge if it found anything else lurking in XFER (i.e. failed from some previous attempt)

Or you simply truncate that entire schema before each deployment, as it's never intended for anything else. You can also limit access and permissions to the XFER schema so that only a dedicated deployment login/user can create in it, and that login/user cannot execute/create/drop in any other schemas.

Kristen
Test

22859 Posts

Posted - 2010-07-14 : 10:24:58

I'm liking this more and more. Thanks Rob.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources