Shared Database Processing

SteveGTR

We just installed SQL Source Control Version 3 with TFS. We're currently testing and developing procedures for moving from what we had to Red Gate / TFS Source Control.

I selected the Shared Database Model when linking the database to source control as this model mostly mirrors how we operate in development mode.

We created unique SQL login accounts.

I was doing the following test:

1) Logged into SQL Management Studio using SQL account X
2) Scripted out an existing stored procedure named uspTest
3) Made some changes and ran the script
4) Red Gate said that there was a pending change made from user X on stored procedure uspTest
5) Exited SQL Management Studio and logged in using SQL account Y
6) Scripted out the changed script and it had the uncommitted changes made by user X
7) Made some changes and ran the script
8) Now Red Gate only shows my changes and doesn't warn me that another user has made changes
9) I checked on the TFS and stored procedure uspTest isn't even checked out

My problem is that this could lead to a possible conflict given that user X scripts out stored procedure uspTest and starts to develop it as a standalone script (not stored procedure). User Y scripts the current version of the stored procedure, makes a simple (but important) change to the script, and runs it. User X completes his changes and then runs the script, overwriting user Y's change.

What can be done to prevent this from occurring? Am I missing something? Is there any other solution other than using dedicated databases?

Thanks

SteveGTR

I was thinking even with a dedicated database, what communicates to another developer that you are working on a stored procedure?

Eddie D

Thank you for your post into the forum.

You are correct, using the "Shared Database Model" you could encounter conflicts as described in your post. SQL Source Control does not lock objects, so if you have two developers working on the same object before it is committed, you will not experience a warning. So the individual that checks in their change in second, their change will win, as the shared database will only ever show the latest change.

The "Dedicated Database Development" model would help you in this case as who ever commits their change second would receive a conflict warning.

The only workaround I can offer is that the development team communicate with each other so that they do not work on the same object at the same time or move to the "Dedicated Database Development" model.

Many Thanks
Eddie

SteveGTR

Thanks for the reply. This point really scares me in that its just what we are trying to avoid by using your product.

I can even see this sort of thing happening in a dedicated server mode.

What is the rationale for not checking out the stored procedure or any existing object when the user changes it in the share database model? The same would apply to the dedicated model.

Communications of what we are working on among team members would be doom to failure... That is one of the reasons for source control, communications. When I see someone has something checked out I tread carefully or ask. If I see nothing, then I'm on my merry way.

Even an option from within your snap-in to checkout an object would be a plus. Then we could say please check the object out first when doing changing.

In any event, your product is a huge step in the right direction for our team and I hope that some of these holes can be plug to help prevent mishaps.

David Atkinson

Hi Steven,

Thanks for your suggestions. We're really keen to take further measures to improve team collaboration in SSMS.

If you're sharing a dev database currently, then there's nothing stopping users overwriting each other's changes. SQL Source Control doesn't make this more likely to happen, although equally it doesn't prevent it happening.

I'll try to answer your question about the rationale for not checking out the object. Unless we instrument SQL Server with additional DDL triggers, something that users have said would be unacceptable, there's no way we can prevent users making changes. An alternative is to enforce the locks from within SQL Source Control, but this falls over as users can make changes in a different tool, or in an instance of SSMS without SQL Source Control installed. The best we could hope for is for the lock to be advisory. Would this be good enough?

When using a dedicated model, you won't be able to overwrite another's changes unknowingly as a conflict will occur, and the tool will let you decide which object you want to take. Is this something that would work for you?

An idea we have for SQL Prompt (do you use this?) is for it to save a log of changes you've made to a database. This means that in the event that your changes are overwritten, at least this could be consulted so the work isn't lost forever. Is this something that would help?

Kind regards,

David Atkinson
Product Manager
Red Gate

SteveGTR

"Unless we instrument SQL Server with additional DDL triggers, something that users have said would be unacceptable..."

I don't understand why users would find this unacceptable. Aren't there already triggers in affect? I'd think you would just be extending the existing triggers. In any case, if users want more control of this functionality perhaps an administrative settings console where these options could be controlled would satisfy the users that find this unacceptable.

When we were evaluating your software one of the things I found amazing was that when I changed an object (via SSMS) on a machine that didn't have RedGate Source Control installed, it showed up as a pending change on the machine where I had your software installed.

"The best we could hope for is for the lock to be advisory. Would this be good enough?"

I don't understand what you mean by "advisory" in the context of the above sentence.

"When using a dedicated model, you won't be able to overwrite another's changes unknowingly as a conflict will occur, and the tool will let you decide which object you want to take. Is this something that would work for you?"

Now that's what I'm talking about! I shouldn't be able to simply overwrite a pending change made by another user in dedicated or share models.

Saving a log of changes would be very helpful when we discovered there was an overwrite. I'm hesitant to look at this as a solution because overwrites are hard to detect if the change was subtle. It might be months before you discover that a change wasn't applied. By that time the log may be way out of date. But it is helpful if you spot it in a timely manner.

If you took the stance of warning or preventing changes to objects that currently have pending changes by another user I think that would be an outstanding step in the right direction.

Thanks