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

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

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

Postby surm » Tue Feb 25, 2014 5:09 am

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?

.
surm
 
Posts: 5
Joined: Tue Feb 25, 2014 5:02 am
Location: Australia

Postby PhilScrace » Thu Feb 27, 2014 4:07 pm

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
PhilScrace
 
Posts: 8
Joined: Fri Jul 16, 2010 2:50 pm

Postby surm » Thu Feb 27, 2014 11:46 pm

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
surm
 
Posts: 5
Joined: Tue Feb 25, 2014 5:02 am
Location: Australia

Postby PhilScrace » Wed Mar 05, 2014 12:05 pm

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
PhilScrace
 
Posts: 8
Joined: Fri Jul 16, 2010 2:50 pm

Re:

Postby surm » Wed Mar 05, 2014 12:16 pm

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
surm
 
Posts: 5
Joined: Tue Feb 25, 2014 5:02 am
Location: Australia

Postby David Atkinson » Wed Mar 05, 2014 1:19 pm

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
 
Posts: 1136
Joined: Mon Dec 05, 2005 4:54 pm
Location: Twitter: @dtabase

Re:

Postby surm » Wed Mar 05, 2014 1:23 pm

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
surm
 
Posts: 5
Joined: Tue Feb 25, 2014 5:02 am
Location: Australia

Re: How to "Check out" (lock) an SQL Server Object for editi

Postby philw66 » Mon Oct 20, 2014 5:47 pm

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
philw66
 
Posts: 1
Joined: Mon Oct 20, 2014 5:13 pm

Re: How to "Check out" (lock) an SQL Server Object for editi

Postby PhilScrace » Tue Mar 24, 2015 11:29 am

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
PhilScrace
 
Posts: 8
Joined: Fri Jul 16, 2010 2:50 pm


Return to SQL Source Control 3

Who is online

Users browsing this forum: No registered users and 0 guests