I currently have a machine with SQL Server 2005 SP3 installed. I would like to add a SQL Server 2008 instance, but I'm not sure if I should upgrade the 2005 or try to install a separate instance of 2008.
If I upgrade the 2005, will I run into any issues with backwards compatibility? (Probably a dumb question).
Is it possible to run separate, stand-alone instances of 2005 and 2008 on the same machine?
Best Answer
If you upgrade you will not be able to (with ease) move a database from 2008 back to 2005.
Like lynnroth, I run multiple instances (05 standard, 08 dev, and a 05 express instance) for testing specific things.
If you're worried about backwards comptible SQL you can use various compatibility level values. It's not exactly like running the SQL against a different version of SQL Server, but it makes migrating from version to version easier.
Also, to note. You can only have one default instance of SQL Server on a machine (machinename). I find it easier to manage which version I'm on by naming the instances accordingly...
machineName - default (used the most)
machineName\SQLEXPRESS (sql express' default instance name)
machineName\SQL2008 - 2008
Don't forget that running databases against multiple instances is different resource-wise than running all databases against a single instance.
Kris