Making Deployments Simpler with Re-runnable Scripts

  • Comments posted to this topic are about the item Making Deployments Simpler with Re-runnable Scripts

  • DROP and CREATE for an object works great until you run into one that has any kind of security assigned. If you drop and create, rights granted (or expressly denied) are lost.

    Instead, use this form:

    IF NOT EXISTS ( <whatever> )

    EXEC ( 'CREATE PROCEDURE dbo.Fubar AS SELECT 1')

    Go

    ALTER PROCEDURE dbo.Fubar <real stuff goes here>

    Go

    This way, if the script that contains this ever needs to be run again (to restore a previous version of the sproc, for example), you won't lose any rights that might have been granted since it was created.

  • Nice article Ed,

    However, the first example with the Stored Procedure, I'd like it more the other way around, CREATE if it does not exist and then ALTER. That way you always preserve the permissions granted on the object. As soon as you DROP it, that information is lost and permissions must be recreated.

    If you do granular permissions, that's a very important point.

    Cheers!

  • Joe Garrick (11/17/2015)


    DROP and CREATE for an object works great until you run into one that has any kind of security assigned. If you drop and create, rights granted (or expressly denied) are lost.

    Instead, use this form:

    IF NOT EXISTS ( <whatever> )

    EXEC ( 'CREATE PROCEDURE dbo.Fubar AS SELECT 1')

    Go

    ALTER PROCEDURE dbo.Fubar <real stuff goes here>

    Go

    This way, if the script that contains this ever needs to be run again (to restore a previous version of the sproc, for example), you won't lose any rights that might have been granted since it was created.

    Writing the same thing at the same time :-D:-D

  • sqldoubleg (11/17/2015)


    Joe Garrick (11/17/2015)


    DROP and CREATE for an object works great until you run into one that has any kind of security assigned. If you drop and create, rights granted (or expressly denied) are lost.

    Instead, use this form:

    IF NOT EXISTS ( <whatever> )

    EXEC ( 'CREATE PROCEDURE dbo.Fubar AS SELECT 1')

    Go

    ALTER PROCEDURE dbo.Fubar <real stuff goes here>

    Go

    This way, if the script that contains this ever needs to be run again (to restore a previous version of the sproc, for example), you won't lose any rights that might have been granted since it was created.

    Writing the same thing at the same time :-D:-D

    Hi Guys, yes agree that this is a good approach!

  • Your example on checking the length of a column before altering it needs a "wrapper" to check that the column exists at all. If the column doesn't exist, then the SELECT won't return anything, and the ALTER TABLE statement will be attempted - and fail.

  • I personally like to also add a version table to the database, so that you can track which scripts that have been executed and by whom.

    When you have many scripts, you can skip the ones that already have been executed.

    If you make sure that you use the same name on the file as in the script you easily automate the databaseupgrade in an ant-script or executable to make sure all scripts have been executed.

    Create a table for database versioning

    /****************************************

    Start Database versioning S0000.

    ****************************************/

    IF NOT EXISTS( SELECT * FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_NAME = 'DatabaseVersion' )

    CREATE TABLE DatabaseVersion (

    ScriptNo nvarchar(6) NOT NULL,

    Description nvarchar(300) NOT NULL,

    ExecutedByUser nvarchar(70) NOT NULL,

    ExecutionTime datetime NOT NULL)

    GO

    INSERT INTO DatabaseVersion VALUES ('S0000', 'Start Database versioning', SUSER_NAME(), GETDATE())

    GO

    /****************************************

    End Patch S0000.

    ****************************************/

    And then use files named with a script number and use the same number in the end of each script and insert a record when the script has been executed.

    INSERT INTO [DatabaseVersion] VALUES ('S0001', 'My first script', SUSER_NAME(), GETDATE())

    and you know if the patch has been executed, when and by whom.

    /Johan

  • I also use version numbering to control which script gets run next. To allow any script to run in any order would be pure insanity (a utopia that will never exist - combinatorial explosion). Some statements must run first in a batch such as Create Schema so I don't know how you could check it before creating it, maybe thru error recovery? My upgrades generally consist of 300 scripts or more and have minimal checking since a version number assures me which objects and dependencies exist.

    Managing hundreds of databases at different versions becomes easy when I can do an "exec version" to see where they are currently at.

  • We'd use a not dissimilar approach for creating test data and removing sensitive data when copying from production to development. Each of the tables had a corresponding table in a schema called CHANGE. Each of those tables had an action column that contained either INSERT, UPDATE, DELETE.

    This made is really simple in that you knew what tables were being changed, what actions were taken and when things failed. The script was then really easy to read and follow.

    Come to think of it, I'll submit a request to write an article about this.

    Andre

  • Ed Elliott (11/17/2015)


    sqldoubleg (11/17/2015)


    Joe Garrick (11/17/2015)


    DROP and CREATE for an object works great until you run into one that has any kind of security assigned. If you drop and create, rights granted (or expressly denied) are lost.

    Instead, use this form:

    IF NOT EXISTS ( <whatever> )

    EXEC ( 'CREATE PROCEDURE dbo.Fubar AS SELECT 1')

    Go

    ALTER PROCEDURE dbo.Fubar <real stuff goes here>

    Go

    This way, if the script that contains this ever needs to be run again (to restore a previous version of the sproc, for example), you won't lose any rights that might have been granted since it was created.

    Writing the same thing at the same time :-D:-D

    Hi Guys, yes agree that this is a good approach!

    I've used this approach for years...many years.

    What I do when creating a new object is to write the drop/create so I'm building it fresh every time. I also write any permissions necessary as a part of my release script. For data population, that's also in the release script. In a nutshell, I can run the whole release script at any time whether the objects already exist or not and everything will be created fresh. This means that anything I messed up or any existing data I created is gone.

    Then, when it comes time to run it in production, everything is included, nothing gets missed and everything's new. Tables, procedures, functions, data and permissions are all included and everything in the proper sequence. Some releases get big enough that I isolate different things to their own files. Then I run each file in the proper sequence.

  • I also use a similar approach. I was surprised when working with a large ERP company that they did not use this approach and that they were surprised how well it worked. Their upgrades often took hours as they save the old table to a temp table, deleted, recreated then wrote the temp table back. Sometimes that's unavoidable but in their case it was pretty rare.

    I don't do the versioning, that looks like a good idea.

  • Thanks for the article.

  • Thank you for the post.  I always like to see what others are doing to create repeatable DDL/DML scripts.

    There are a few built in function you may want to consider:
    Consider using this as an existence test if object_id(N'schemaName.NameOfObject'[, N'TypeCode']) is null   ...rather than the more verbose... if not exists (select * from sys.objects where name = N'NameOfObject' and schema_id = schema_id('schemaName'))

    It column order isn't important when you add a column to a table then you can test for column existence using if col_length('someTable','someColumn') is null.

    One approach I use for merge statements is to use EXCEPT to eliminate duplicate/already updated values (provided the TargetTable isn't too big).
    merge into dbo.TargetTable as tgt
    using( select v.ColA, v.ColB, v.ColC
           from(values ('A1', 20, 'C it first')
                       ,  ('A2', 21, 'C it next')
                       ,  ('A3', 22, 'C it last')
                      ) v ( ColA, ColB, ColC)
            except
            select ColA, ColB, ColC
            from dbo.TargetTable
         )  src on src.ColB = tgt.ColB
    when matched then
    update set ColA = src.ColA, ColC = src.ColC, UpdatedOn = getdate(), UpdatedBy = system_user
    when not matched then
    insert ( ColA, ColC, CreatedOn, CreatedBy )
    values ( src.ColA, src.ColC, getdate(), system_user)
    output $action [Action], Inserted.*;
    You could also create a CTE and use the CTE as the source for the MERGE.  This has the advantage that you can use the CTE for a testing/validater to see what data will be returned before kicking it into a repeatable script. 

    --Paul Hunter

  • Ed Elliott - Tuesday, November 17, 2015 1:51 AM

    sqldoubleg (11/17/2015)


    Joe Garrick (11/17/2015)


    DROP and CREATE for an object works great until you run into one that has any kind of security assigned. If you drop and create, rights granted (or expressly denied) are lost. Instead, use this form: IF NOT EXISTS ( <whatever> ) EXEC ( 'CREATE PROCEDURE dbo.Fubar AS SELECT 1') Go ALTER PROCEDURE dbo.Fubar <real stuff goes here>Go This way, if the script that contains this ever needs to be run again (to restore a previous version of the sproc, for example), you won't lose any rights that might have been granted since it was created.

    Writing the same thing at the same time :-D:-D

    Hi Guys, yes agree that this is a good approach!

    +1 for Create/Alter rather than Drop/Create.  We have a client that uses replication, and Drop/Create was problematic because you can't drop sprocs that are being replicated.
    As a result, we created Create/Alter versions for all server-side code, which I include below for everyone's benefit:

    IF OBJECT_ID('[dbo].[<Name_Of_Function, , >]') IS NULL -- Check if Function Exists
        BEGIN
        EXEC('CREATE FUNCTION [dbo].[<Name_Of_Function, , >] (@i INT) RETURNS INT AS BEGIN RETURN 0 END;') -- Create dummy/empty Function
        EXEC('GRANT EXECUTE ON [<Name_Of_Function, , >] TO Public AS dbo;') -- Grant permissions to dummy/empty Function
        END
    GO

    IF OBJECT_ID('[dbo].[<Name_Of_Sproc, , >]') IS NULL -- Check if SP Exists
        BEGIN
        EXEC('CREATE PROCEDURE [dbo].[<Name_Of_Sproc, , >] AS SET NOCOUNT ON;') -- Create dummy/empty SP
        EXEC('GRANT EXECUTE ON [<Name_Of_Sproc, , >] TO Public AS dbo;') -- Grant permissions to dummy/empty SP
        END
    GO

    IF OBJECT_ID('[dbo].[<Name_Of_Function, , >]') IS NULL -- Check if Function Exists
        EXEC('CREATE FUNCTION [dbo].[<Name_Of_Function, , >] (@i INT) RETURNS @O TABLE(i INT) AS BEGIN INSERT INTO @O SELECT 0 RETURN END;') -- Create dummy/empty Function
    GO

    IF OBJECT_ID('[dbo].[<Name_Of_Trigger, , >]') IS NULL -- Check if SP Exists
        EXEC('CREATE TRIGGER [dbo].[<Name_Of_Trigger, , >] ON [<Name_Of_Table, , >] AFTER UPDATE AS SET NOCOUNT ON;') -- Create dummy/empty SP
    GO

    IF OBJECT_ID('[dbo].[<Name_Of_View, , >]') IS NULL -- Check if View Exists
        EXEC('CREATE VIEW [dbo].[<Name_Of_View, , >] AS SELECT 1 AS Column_Name;') -- Create dummy/empty View
    GO

  • hello there,

    an addition to the "drop and create" question:in newer SQLServer versions finally the CREATE OR ALTER syntax was introduced. I'm not sure if in SQL 2014, but I know about it in SQL 2016.
    Still I totally agree with Ed Wagner, having all the security stuff as part of the code. When dealing with topics like "continious delivery" this may be an important factor.
    (I'm not having it right now, but have to deal with this soon).

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply