how to develop, test, version, sync SQL Server 2000, 2005, 2008, 2008R2 databases?

Question

A development shop has a range of ASP.NET projects using SQL Server 2000, 2005, 2008. 2008 R2 databases.

How would you design, develop, maintain, version control, fill with test data, stress load, test, automate, maintain in sync with production such range of databases?
Does recent Visual Studio 2010 Ultimate or Database Eds support SQL Server 2000 databases?

Update: The question is not confined to VS2010 or even to MS-only products.
Even if confined, then how to organize the development infrastucture and environment.

Also, variants of cutting some of the functionalities in order to minimize/cut or optimize time and expenses are to be considered.

I was reading so far on it (with sublinks and related links):

• Different Development environment than Test & Production environments?
• Keeping testing and production server environments clean, in sync, and consistent
• How to keep track of performance testing
• Get Your Database Under Version Control
  http://www.codinghorror.com/blog/2008/02/get-your-database-under-version-control.html
• Verify database changes (version-control)
• Is Your Database Under Version Control?
  http://www.codinghorror.com/blog/2006/12/is-your-database-under-version-control.html
• How do you stress load dev database (server) locally?

Answer 1

I suggest you develop against the lowest common denominator (i.e. the SQL 2000 database).

You can then backup and restore this database to the other version of SQL Server in your testing and staging environments to give you the range of database servers you need.

Answer 2

First have your developers load client tools of all three versions on their machines. You have to start from 2000 and work out to work correctly. Then have them work in Query Analyzer for projects supporting 2000 and in SSMS for projects supporting 2005 or 2008. Insist that they always work only against the lowest version of the database the client will be using. Most things that work in 2000 will work in 2008 (not so true of the next version, so customers on 2000 should strongly be encouraged to upgrade.)

Have them do all work in scripts (even database changes and inserts to lookup type tables) and check the scripts into source control just like any other code.

If you have testers, make sure they are connected to the correct version of the database and that they do tests against that and not some higher version.

I also would have a cheat sheet made up for your developers concerning what T-SQL code will work on which version. Best way to do this is to look in Books Online for 2005 and 2008 to see what new features were added.

But it is critical that they only work in the database the particular project will support or you will have to rewrite large swaths of code when it goes to prod. Newer devs don't know 2000 and are used to using things like CTEs that are not supported. It is best they find out immediately when they write the code that it won't work not in test or worse on prod.