Managing environments
I think you definitely don't want to be forced into a single database version. You've got enough developers that you will inevitably have multiple development work streams, and requirements to apply patches to the current production environment independent of development workstreams.
You can use Liquibase or a manual process to produce patch scripts to upgrade versions. I suggest starting out with a manual process and using the schema comparison tool for QA on the patches. Clean, automated, transparent synchronisation of a nontrivially complex database is a bit utopian.
Your central data model can be kept in whatever system takes your fancy. I've used everything from tedious enterprise repository tools to create table scripts. Create table scripts play nicely with ordinary source control tools such as subversion and not all repository tools do a good job of versioning.
Whatever you use as your master data model repository you need a fairly clean mechanism for deploying an environment from that model. It should be structured so that rollouts to an environment are easy. You also need a mechanism to patch from one released version to the next.
What I've done
I've done the following in the past when I was managing development environments. It's not particularly high tech, but it's amenable to version control and automated builds, so it makes it easy to roll out an environment to a specific version, and maitaining a large number of environments is quite practical.
Maintain a central repository: This could be a set of database creation scripts held in a version control systems, or a repository model in a data modelling tool. Take your pick. This model should have a build mechanism that allows an environment to be rolled out from the scripts without a lot of manual intervention.
If you have a lot of reference data you will need a load mechanism for it. Depending on how you want to do it, you could keep this in a database or in a set of files. The advantage of files is that they can also be versioned and labelled from the same version control system as your code base. A bunch of CSV files and bulk loader scripts in a source control repository can do this easily enough.
One option for deploying development environments is to take backups of the production database patched to the appropriate version and make them available for devs to restore into a development environment.
Make it easy to roll out: Like any CI build process, the database should be deployable from a single script. Set it up so that database connections can be paramaterised, or the script is location independent and can just be run through the connection.
Patch scripts: You will need roll forward and probably roll back scripts from each released version.
Build test environments from the repository model: This ensures that development on environments that are out of sync with the repository gets caught in testing.
Test the deployment process: Automated patching scripts, however they are created should be testable. Schema comparison tools are quite good for this, even if you dont't use them to generate the patch scripts.
Create a reference environment with the repository model build you tested against
Create a smoke test environment with either a backup of your production release or a build based on the current released version.
Run the patch script against the smoke test environment.
Use the schema comparison tool to compare the smoke test environment with the reference environment. The patch script should result in the two databases being identical, so you can investigate any differences.
What I like about this process
This is a bit heavyweight, and was designed for deploying into fairly bureaucratic and opaque production environments. However, it has the following strengths:
Developers can tinker where they need to.
Multiple branches and streams of development can be accommodated.
The deployment scripts themselves can be tested in a repeatable manner. This is very helpful to shut down deployment bunfights, as the repeatability can be demonstrated.
The schema comparison tools provide QA on the deployment itself.
Testing is always done against what is expected to be released, and this will catch issues arising from environments being out of sync.
Deployment based on repository models and patch scripts means that uncontrolled rubbish doesn't get accidentally migrated from development environments into production.
A lot of the process can be automated, although it is often desirable to prepare and test the deployment patch scripts manually.
Environments are cheap and easy to deploy without having to jump through hoops.
Developers are forced to make a system that is amenable to a simple build and deployment process.
Developers are forced to learn basic database administration tasks, but test and production environments are insulated from noob mistakes.
How it addresses your requirements
Developers have local copy of data to run development code against
The deployment scripts or DB images mean that they can set up an environment from any version that's available.
Able to rollback database structure to a previous changeset
Again, sorted by the deployment scripts. Either through DDL or test database backup images created through a controlled process, developers can bring up an environment to any specific version that you have.
Able to separate new feature schema changes vs schema design fix changes
Patches to a common version can be maintained in a separate fork in the svn tree. If database backups are used as the reference environments they need to be stored somewhere with the same folder structure as the branching of the source control projects.
Able to modify database structure locally for testing
The simple deployment process allows devs to tinker, and easily restore an environment to a local state, or bring up a reference environment to do comparisons and make change sets against.
I think the answer to many of your questions is that it depends. What performance problems are you having? It seems unusual that a database would have performance problems just from growing to 250GB in size.
Perhaps your queries are performing table scans on the entire fact table even when only a small portion (e.g., the last year) of the date range is needed? If there is a particular query that is most important to optimize, considering posting your schema, query, and an actual execution plan in another question to see if it can be optimized.
Do you prefer one of the solutions over the other?
I generally prefer the history database, and I think Guy describes good reasons for this in his response.
The primary disadvantage I see for a history database (as opposed to a schema) is that you can no longer use foreign keys for your archive table. This may be fine for you, but it's something to be aware of.
The disadvantage you listed for this approach is not accurate; you will be able to query across databases on the same server easily and the query optimizer generally handles cross-database queries very well.
Are there any better possibilities?
If you need to query the archive data regularly, I might consider partitioning the table by date. However, this is a big change that can come with a lot of performance implications, both positive (e.g., partition elimination, more efficient data loading) and negative (e.g., slower singleton seeks, greater potential for thread skew in parallel queries). So I wouldn't make this decision lightly if it's a heavily used database.
Would the newly created archive table also need primary / foreign keys?
Or should they just have the columns but without keys / constraints?
I would recommend having at least the primary key and unique indexes so that you can get the data integrity benefits they provide. For example, this will prevent you from accidentally inserting a year of data into the history table twice. And as a side benefit it may improve performance if you do need to query the history table.
Any other thoughts?
Since you are using Enterprise edition and planning to upgrade to SQL 2008+, you might consider data compression for this table. Compression will certainly reduce disk space, but depending on your server's disk and CPU resources it may also improve query performance for reads by reducing disk I/O and improving memory utilization (more data fits in cache at once).
Best Answer
The first thing you need to identify in your db are which tables require all their data (the seed data, sometimes called "domain" data), and which tables are suitable for bringing only recent data. You have a couple choices then.
1: Use a tool like SSDT to deploy your SCHEMA to dev & staging, keep the domain data as a bunch of INSERT statements in SSDT and apply those, and then write a custom SSIS package (or use the data transfer wizard, which is just SSIS) to pull only the useful, recent data over the wire.
2: Backup the whole database, restore it somewhere else, and run a purge script. Then backup the purged db and restore this to dev & staging.
3: Partition your big tables so that data older than a month goes to a new filegroup, and use the Piecemeal restore option to only restore your PRIMARY filegroup.
In our production environment, we have 1.5TB database, and we use option 2. I have tested option 3 with smaller db's and it does work rather well. Option 1 requires no changes to your db, but seems like the most work to me.