How to "Check out" (lock) an SQL Server Object for editing?

surmsurm Posts: 7
edited March 24, 2015 6:29AM in SQL Source Control Previous Versions
We check-out a source file (like a c# file or SSIS Package) from TFS before editing. Our TFS does not allow multiple check-outs of the same file, so by "checking out", we prevent other users inadvertently doing parallel changes to the same file.

How do we check-out an SQL object (say, a stored proc) via "SQL Source Control 3"?

If this is not possible, how do we address the following scenario?
    User 1 opens a stored procedure in SSMS and starts making changes in the script.
    User 2 also opens the same stored procedure in SSMS, makes changes in the script and executes it (ie. update the stored procedure in the database) and commits changes to TFS.

Now both the database and TFS contain the updated version of the stored procedure while User-1 has an older version of the same stored procedure open in SSMS as a script . When User 1 eventually finishes making changes and executes this script, it will inadvertently overwrite User 2's changes. Is there a way to prevent this?

.
· Share on Twitter

Comments

  • PhilScracePhilScrace Posts: 9
    Thanks a lot for the question Surm! :)

    The answer really depends on which Source Control model you have adopted. Sql Source Control supports a. Dedicated model b. Shared model. In a. each user is connected to the source control repository with their own local version of the database and its objects . In b. each user is making changes to the same database. In the dedicated model connected to Tfs the problem you describe would typically be overcome by using TFS object locking which is partly supported in SQL Source Control. Basically the user has to separately log in to Team Explorer and lock the objects through the ui of this Microsoft tool, SQL Source Control will then respect these locks.
    Once the changes have been made by the user they release the locks through Team Explorer and commit as normal. Currently you cannot lock objects through the SQL Source Control Ui.

    If you are using the Shared Model then we are proposing that users lock the objects in SQL Server. The SQL Source Control team are currently designing this 'Safeguarding' feature which will allow you to do this through the tool. We have UI designs and some of the 'machinery' which will allow you do this and are currently exploring the direction of this feature.

    Which option would suit you best?

    Incidently SQL Source Control basically 'checks-out' the files in exactly the same way for each model. A local copy of the SQL objects are persisted to the users disk and put under the control of SQL Source Control which is using the TFS API under the hood.

    More details can be found here:
    http://documentation.red-gate.com/displ ... +overview#


    Please do come back with any further questions about this or anything else to do with our products.

    Thanks again for your interest, Surm.

    Phil
    Test Engineer, SQL Source Control
    · Share on Twitter
  • surmsurm Posts: 7
    If you are using the Shared Model then we are proposing that users lock the objects in SQL Server

    Hi Phil,

    By "..users lock the objects in SQL Server...", do you mean changing SQL Server permissions (eg. temporarily removing "modify" permission from other users)?

    Regards
    · Share on Twitter
  • PhilScracePhilScrace Posts: 9
    Hi Surm,

    We've made some progress this week in that we now have a standalone fully functioning prototype which we will initially be giving away free. It's called 'SQL Lock'. We've opted not to meddle with users security in order to lock objects, for obvious reasons. Was this a concern of yours? Did you have a particular method of locking objects in mind?

    Regards

    Phil
    · Share on Twitter
  • surmsurm Posts: 7
    PhilScrace wrote:

    We've opted not to meddle with users security in order to lock objects, for obvious reasons. Was this a concern of yours?

    Hi Phil,

    Yes - The idea of meddling with SQL Server security to mimic TFS "check out" did not go well with developers.

    There is another tool (ApexSQL Version) which seems to have "check in/check out" option on SQL Server objects. Was looking for something similar in Redgate since our organisation already has Redgate.

    regards
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    Surm,

    Are your developers developing on a single shared database, or do they each have their own development sandbox?

    David Atkinson
    Red Gate
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • surmsurm Posts: 7
    David Atkinson wrote:

    Are your developers developing on a single shared database, or do they each have their own development sandbox?

    Red Gate

    Hi David,

    We have the shared development database model.

    Regards
    · Share on Twitter
  • philw66philw66 Posts: 1
    Hi

    Is this 'SQL lock' feature available now?

    We are using a shared model and would be interested in an integrated check-out check-in via RedGate Source Control.

    Thanks,
    Phil
    · Share on Twitter
  • PhilScracePhilScrace Posts: 9
    Hi Phil,

    Sorry for not replying sooner. Following the release of SQL Lock Beta the SQL Source Control team have been looking at integrating SQL Lock into the SQL Source Control product. I suspect that this will be released around mid-late Q2.

    Would you be like to speak with the team about this feature or perhaps be involved in an Early Access Program?

    Regards

    Phil
    SQL Lock Product Team
    · Share on Twitter
Sign In or Register to comment.