SQL Source Control is not trying to drop extra objects
mattcomroe
Posts: 4
Here's my scenario:
I created a baseline project in SVN using SSC.
I link it to a different database with a slightly different schema, including some extra objects which exist in the target DB and not in SVN.
The "Get Latest" tab shows no changes:
But the "Commit Changes" tab wants to commit some procs and a user:
As a last test, I used the standalone SQL Compare tool and compared the SVN folder to the same database, and it wants (correctly) to drop those extra objects:
So, why might this be happening?
I created a baseline project in SVN using SSC.
I link it to a different database with a slightly different schema, including some extra objects which exist in the target DB and not in SVN.
The "Get Latest" tab shows no changes:
But the "Commit Changes" tab wants to commit some procs and a user:
As a last test, I used the standalone SQL Compare tool and compared the SVN folder to the same database, and it wants (correctly) to drop those extra objects:
So, why might this be happening?
Comments
I'm not quite sure I'm exactly understanding your problem; what I *think* you're saying is that the second database has some objects in it that are NOT in SVN, and you're expecting the Get Latest tab to show you it wanting to drop the objects from the local database, correct?
If so, then I think what you're seeing is right.
We establish changes in the repository (such as new/removed objects) by changes in a "Transients" folder in your userprofile, alongside the working base. When you linked, the Transient was initially built from the current contents of SVN and is thus in sync, so we don't "see" this as a drop to be performed.
They do show on the commit tab though, because we detect the difference between the database and SVN via the transient, so we know those objects don't exist in SVN yet.
Compare does display the drop, because you're doing a direct compare between SVN and the database - which SQL Source Control doesn't do.
So in short, I think what you are seeing is correct - if you want to get another DB Linked up to SVN and you're happy that what's in SVN is the current state you want to use, you're better off linking a blank DB and then doing a full Get Latest into it.
Hope that helps!
Redgate Software
Thank you for your reply.
Yes, I think you're understanding my issue correctly in terms of what I'm testing out and what my expectations are.
I understand why the Transients folder works the way it does, but let me describe a workflow scenario which my company is going to encounter, and see if possibly there is an workaround.
All our developers have a seat license for SSC, but we only have one license for the SQL Toolbelt for our build machine, specifically so we can have SQL Compare 10 and SQL Data Compare 9 with their command line tools in order to have a Continuous Integration environment. A developer checks in a change, and an automated build process kicks off which gets latest from SVN, builds and deploys code, and runs a SQL Compare and DataCompare command line batch to update a couple of different staging databases.
Our production environment has quite a few customer databases, all of which are *supposed* to be the same schema, but without a great process in the previous years they have gotten crufty and are not in sync. We will get bug tickets which are data-specific, so we will pull down a copy of their particular database and fix the bug using that. My concern is that a developer will do this:
- Compare bugfix branch in SVN to copy of customer database in order to try and sync schemas. This will not show any additional objects which are in that target DB as being designated for drops.
- Dev makes fix, and on checkin inadvertently adds crufty additional schema objects into SVN.
- Extra objects make their way into the source tree.
I realize it's the responsibility of our developers to recognize which changes are actually theirs upon checking in (even though we're going with the "dedicated database" method with SSC), but I was hoping to remove potential issues.
Further, I know that the "right" answer is to pull down each production database and run SQL Compare 10 against them in order to properly sync schemas, but that requires lots of time for regression testing that I'm not allowed to have right now. We're just trying to get our Red Gate-fueled CI environment up and running, and I'm trying to prevent stumbles before they happen.
Just to clarify, your developers have a local dedicated copy of each customer database (with varying levels of 'cruft') that they are making changes to and they are committing changes made on all these customer databases into one SVN repository.
The problem is that they might accidently commit some of the cruft on any one of the customer databases. This will be committed to the shared SVN repository and the cruft will be automatically deployed to all the other customer databases.
If this is the case, I have a couple of suggestions (which you have probably tried and rejected):
1. Set-up a filter in SQL Source Controlfor each customer database that hides all the cruft. It can be committed and shared by all your developers - stopping it from appearing in anyones commit list.
2. Create a db script which drops all the cruft and pass it to each developer to run on their local databases. They will then never see the cruft in the commit list the dbs should now synch with the schema in SVN.
3. Let the developers commit all the cruft into SVN but add a filter to SQL Compare to avoid it being deployed.
I hope this helps, please let me know if I have not understood the problem.
Best regards,
Chris
Redgate Software
Thanks so much for your prompt reply.
I actually discovered a pretty great workaround.
In a situation like from my first post, where there are additional schema objects that SSC will want to try and add on the "Commit Changes" tab, I can just Ctrl+A to select them all, right-click and chose "Undo Changes...", and that will allow me to delete any new objects because SSC regards them as object changes.
Of course, our devs will have to do this as part of their initial setup and not after they've made changes in order to not lose any "real" schema changes that are made.
Problem solved! Again, thanks so much to you and James for your help on this.
Redgate Software