How to "Check out" (lock) an SQL Server Object for editing?
surm
Posts: 7
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?
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?
.
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?
.
Comments
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
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
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
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
Are your developers developing on a single shared database, or do they each have their own development sandbox?
David Atkinson
Red Gate
Product Manager
Redgate Software
Hi David,
We have the shared development database model.
Regards
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
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