Best practices for SQL Server development across differing versions

Question

Expanding on this question, what is the best way to develop against both SQL Server 2005 and SQL Server 2008?

I'd like to see if I could just use Orcas technology on my current Vista 64 machine and since SQL Server 2005 wants to install a stub version of Visual Studio 2005, I'd like to avoid using it. However, most places where my technology would be deployed are on SQL Server 2005 for the foreseeable future.

So what would be the best course of action:

1. Install SQL Server 2008 only on my development machine and just be cognizant of the 2008-specific abilities
2. Install SQL Server 2008 and SQL Server 2005 on separate instances on my development machine and develop against either depending on what the production project requires
3. Install SQL Server 2008 only on my development machine and install SQL Server 2005 on a different machine (like a test server)
4. Install SQL Server 2005 only on my development machine and install SQL Server 2008 on a different machine (like a test server)

Answer 1

The referenced question suggests changing the database compatibility level, this is a short term solution that would not be automated easily.

Its important to have automated testing, and if your reading stackoverflow, you probably agree.

I would say get both MS SQL Server 2005 and 2008 running.
Then, assuming you run unit tests, always unit test your database code against both servers.

Answer 2

The safest practice is to code against the oldest database server you support. This version is the one that will be far more likely to give you trouble. By and large the new versions of the db will have backwards compatibility to support your TSQL and constructs. It is far to simple to introduce unsupported code into the mix when using a newer version db then your target.

Answer 3

(Kinda defending my down-modded answer)

The Accepted solution doesnt consider support for multiple database providers, Oracle, MSSQL, MySQL. It doesnt handle performance testing against multiple database versions, in fact the only thing it does do is say 'yep , you've got valid old t-sql' You cant test performance, you cant test multiple database servers, your very tied to supporting a single type/version of database.

Answer 4

You need to code against the oldest version of SQL Server, so that you don't start using features not available until more recent versions. Although it is not necessarily true that the newer versions will continue to support older features, the best way to make sure is to run Microsoft's own SQL Server Best Practices Analyzer which will notify you of compatibility issues.