Creating a Drupal development environment - achieving development, stage and production

What is the best you've found to setup a Drupal development environment when working with a team to achieve typical dev, stage and prod environments? These are my experiences:

How I've done it in the past:

• Create an SVN repository and do an initial import of the drupal core and add all of Drupal core files leaving settings.php and the root .htaccess file unversioned bc they vary across dev, stage and prod.
• Checkout three working copies of the repository for each environment
• Create a new database user/account for each environment
• Modify .htaccess, apache directives, and settings.php to reference the different environments
• Install Drupal on one of the environments and perform db export/import into the other three environments db's
• I now have three Drupal environments that use a single repository for the file system

Migrating changes from one environment to another:

• Run a mysqldump to save the current environment's db to /database/databse_$environment.sql
• Run a mysqldump to save the destination environment's db to /database/databse_$environment.sql
• Add both files to the repository and commit them for safe keeping.
• Do a diff of the two .sql files and then resolve the conflicts into what should represent the new fully migrated database *tedious step
• Drop the destination environments db tables entirely to ready it for import of the migrated .sql file.
• Import the migrated .sql file to the destination environment's db
• NOTE: I typically create a script for each of the environments to automate the dump and import and drop steps for that environment's db so I don't have to use phpMyAdmin or some other interface

Problems and Limitations:

• This is not single click migration. Even after all of the initial environment setup, I've found that the migration is best handled by the person who configured the environments
• Resolving the merging conflicts in the .sql files requires a lot of manual inspection and requires you to make decisions based on intimate knowledge of the previous db states
• Each of the environments is tied to an svn account so this process requires a single account holder to be responsible for
• I notice that permissions on folders like /sited/default/files get a screwed up sometimes using svn so environment directory writeability gets corrupted on svn updates sometimes

Moving Forward:

• GIT over SVN. My most recent project uses GIT and I'll never go back
  • GIT doesn't tie a single user to a working copy. Any user can migrate their file system changes to another environment. SVN required this to be done only be the user who performed the initial checkout
  • You can start a working copy from a git repo from a compressed file rather than needed direct access to the repo server. (This has been useful when working with an outside contractor who's changes you would like to keep versioned with little effort)
  • No permissions issues experienced like svn as of yet
  • Users can make local commits to their environment without committing to the repository.
• Looking for a better solution than running mysql commands for migrating the databases. DBscripts (http://drupal.org/project/DBscripts) looks promising. Perhaps Drush's (drupal.org/project/drush) sync command or Ægir (http://groups.drupal.org/aegir-hosting-system) may have some solutions as well.

Thanks,
Jerod Fritz - jerodfritz@gmail.com