Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
toddbkc
Starting Member
9 Posts |
 Posted - 2012-01-23 : 12:10:59
|
| My company has decided to start using SVN for source control of database objects. We have been having discussions regarding how to set up the folders that makes sense for a large IT shop.Questions are:* How do you handle keeping a view of what is currently in Production? Does the Trunk have production level objects only? Do the developers have access to the Trunk?* How do you allow multiple projects to be developed concurrently?* At what point do you merge your objects into the main development line (if they are branched off)? Who performs the merge?Any input will be valuable if you currently use SVN today with database objects. Thanks for your help!!!TODD |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-01-23 : 12:38:10
|
Well there is a lot of difference of opinion about it. So I'll just describe the environment I currently work in and what I think the strengths and weaknesses are. To give you an idea - we have multiple projects being developed at once with a team of about 20 developers, we deal with moderately sized databases with very high churn (we process about 4 billion quotes changes per week and maintain a store of about 400 million rapidly changing entities)quote:
* How do you handle keeping a view of what is currently in Production? Does the Trunk have production level objects only? Do the developers have access to the Trunk?
We have multiple different DEV environments modelled after production. So we have DEV1, DEV2, DEV3 etc each with a copy of the logical prod environment (multiple db's). Each one has it's own replication system set up the mirror's production. The databases have the same schema, tables and programmability objects. However we can't give each db all the prod data (we can't) so for certain tables we only have a few thousand rows.The dev environments are maintained via Redgate SQL Compare.quote:
* How do you allow multiple projects to be developed concurrently?
Each DEV environment is used to develop a project. SVN is handled by Redgate Source Control. Redgate Source control is OK when it works but it's nowhere near as good as SQL Compare. This, however, is down to the extreme complexity of the database environments (we are moving away from buisness logic in the db but much of it still remains  )Each dev project works in it's own BRANCH (itself taken from a branch of TRUNK we call INT).quote:
* At what point do you merge your objects into the main development line (if they are branched off)? Who performs the merge?
We merge back into INT. Not into TRUNK. The operations team decide when to promote INT into TRUNK when it has passed all tests. We generally get a release about once a month on average.The senior database developer on each project manages the merging. The process is:1) Merge changes from INT into the DEV branch.2) Check all tests, resolve any conflicts3) RE-INTEGRATE the branch into INT4) Bin the branch5) Re-merge the branch.If you can't reintegrate the branch -- you have to fix it until you can.Hope that helps./EachCharlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-23 : 13:27:56
|
"Well there is a lot of difference of opinion about it. So I'll just describe the environment I currently work in and what I think the strengths and weaknesses are"Agree 100% with that  Here's what we do with SVN:\SVN |- DEV | |- SQL | | |- Project 1 | | | |- Application (SProcs for Application) | | | |- CRUD (Mechanically generated SProcs and Triggers for CRUD) | | | |- SQL Patches (DDL mostly, plus any data-changes to get from Version-1 to Version-2) | | | \- SQL Snippets | | \- Project 2 | | \- ... folders as per Project 1 ... | |- Other non-SQL stuff 1 | \- Other non-SQL stuff 2 |- QA | |- Other folders mirror DEV subfolders | \- ... \- PRODUCTION |- Other folders mirror DEV subfolders \- ... When we rollout from DEV to QA (say) we copy all the SQL from DEV sub-folders (for the relevant project(s)) to QA sub-folder (and check-in). Then any changes on QA we make in the QA folder, and then manually merge back into DEV folder when we are ready (and from DEV they get checked in again).Other points:We *always* check-in ("COMMIT" in SVN-speak) a folder before we check-out ("UPDATE" in SVN-speak) to get latest version. Otherwise if there is a file in the folder that is locally-modified SVN will "merge" it with the latest version just checked-out form repository - which makes a complete Horlicks of the source code. When you do COMMIT then if the Repository has a newer version the COMMIT aborts. You can then use the DIFF tools to see what is different, sort out the source code and then check in again (or rename the source code file, check-out (to get a clean version) and then manually DIFF & MERGE from your renamed, preserved, file.On Check=in (COMMIT) we do a DIFF (on the COMMIT dialog box all files to be checked in are listed, you can right-click any (i.e. each-in-turn) and choose DIFF), this serves as a personal "peer review". The DIFF tool we use (you can configure SVN to use anything you have available) allows edit of either file, so we can choose to "tidy up" the new file version before it is checked in (e.g. if you see a code-comment with a typo or whatever - or choose to exclude that file from the COMMIT if you see something goofy) |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-01-23 : 14:04:08
|
I'll talk about the Redgate tools we use for a bit here because they are very useful, but they also take some time to get to know and some of them have their gotchas. Personally I find that maintaining a db without using tools is a complete PITA when you have multiple people working on the same repo. Kristen already mentioned the file level merging which really doesn't work well on db scripts without something to maintain it. Also, its really important that all your developers *do the same thing* - if you have a tool in use you should make sure everyone uses it. When people do things manually it's easy to screw things up.SQL Compare: It is brilliant. Incredibly useful -- you can compare databases to each other or to a checked out version of the database hosted locally on the filesystem. Or to a svn repository (more on that in a minute). It has a command line mode which makes deploying changes hosted in SVN (or sourcesafe) a breeze -- we use this in development to progress from DEV to DEV-TEST to DEV-SHOWCASE. In my company this is more of a DBA tool primarily used to make changes to prod servers via it's generated change scripts. However it's also brilliant when updating dev environments. SQL Source Control: Is much less brilliant. This is a program that integrates with sql server management studio and lets you check in or update from a svn repository. So, you would use it like so:I link the database to a svn repo. The tool can create a folder for the db for you and will create the relevant folder structure, or you can link it to a folder already created in this wayI do some development on my dev database in SSMS (where I'm really comfortable), lets say I change a stored proc and have added a view.I can then check in that code by comparing the repo to the db (Again in management studio). Source control gives me a pane where you can see changed code and check in directly to svn. - An important statement here is that you do not need to bring the database completely up to date with the repo, it's not like merging a branch you can manage the db objects separately. We can therefore manage much of the db development process using tools. About the only thing we can't do with Redgate tools is manage replication. We have had to come up with our own set of replication frameworks (because we do crazy custom s**t with snapshot replication that really shouldn't be done but....) which we manage ourselves. For other things we use the Redgate tools.We have had to work around some of the limitations of the redgate tools however -- SQL Soruce control does not work well with really complex db's. In particular our snapshot replication was causing us a constraint name problem (I'm not going to get into it, when I found out what was happening I was a little annoyed) which meant that every time we replicated certain tables the constraints were getting renamed via appending a GUID. THis meant that ever time you fired up source control it wanted to rebuild some bit tables..Also we are now pursuing a much more service oriented approach -- so we'll make up a new database for a particular logical service (say our geo service will now have it's own db rather than a schema in our old db structure). I think this was a good thing in and of itself but it also makes source control much happier when you have a nice small db.These tools don't help us merge repos or re-integrate. For that you need to follow good SVN etiquette and know what you are doing. If you use branches then you should never commit code to the trunk branch otherwise you are going to risk not being able to re-integrate a branch.Also (As Kristen mentioned) you have to be careful when merging and re-integrating You have to check any file level merges and if there are conflicts it can get messy. In my opinion this is a problem with the branching approach -- I'd actually rather all dev's worked on the one branch (lets call it DEV-TRUNK) and have to deal with the conflicts as and when they occur, rather than blindly going about their business in a branch and then find that they have a gazillion conflicts when they try and re-integrate.NEVER, NEVER, NEVER try and force changes from a branch into the trunk of that branch. If you can't re-integrate the branch (using SVN re-integrate) then you must*should* fix your branch, resolving all conflicts and then re-integrate again. If you don't you are just going to load up pain for future integrations.Our SVN Structure looks like this/SVN | - TRUNK/ | - INT/ | - DEV/ | - DEV1/ | - DEV2/ | - DEV3/ | - DEV4/ | - DEV5/ with each subfolder (TRUNK, INT, DEVX) having a folder for each database where the redgate formatted repo goes. Whever we re-integrate a branch we delete the folder (from svn repo-browser) and copy INT to the old file location).Oh yeah -- we also use Tortoise SVN. It's brilliant..... Integrates with Explorer -- It's also free. I can't recommend it enough.Sorry for the lengthy post.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
dvdtknsn
Starting Member
2 Posts |
Posted - 2012-01-24 : 11:31:21
|
| Hi Charlie,I'm the product manager for both SQL Compare and SQL Source Control and I'd like to understand the issues you are having so we can improve the tools. My understanding is that the primary issue with SQL Source Control is its lack of support for Snapshot Replication? SQL Source Control uses the SQL Compare engine under the hood so I'd be interested to know whether you're seeing the same issue in SQL Compare, and if not, whether it's something that is fixed by your selection of project options. If this is the case, we might be able to get a patch released to fix your issue. Please feel free to contact me directly at David.Atkinson@red-gate.comKind regards,Davidwww.red-gate.com |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-01-24 : 12:16:24
|
quote:
Originally posted by dvdtknsn
Hi Charlie,I'm the product manager for both SQL Compare and SQL Source Control and I'd like to understand the issues you are having so we can improve the tools. My understanding is that the primary issue with SQL Source Control is its lack of support for Snapshot Replication? SQL Source Control uses the SQL Compare engine under the hood so I'd be interested to know whether you're seeing the same issue in SQL Compare, and if not, whether it's something that is fixed by your selection of project options. If this is the case, we might be able to get a patch released to fix your issue. Please feel free to contact me directly at David.Atkinson@red-gate.comKind regards,Davidwww.red-gate.com
Sent you an email -- lets take it off topic though....Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
SVN for Source Control & Setup