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?
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
Yes, you can run seperate instances of SQL Server 2005 and 2008 on the same server. I have several servers that are doing exactly that.
Building on Kris and lynnroth's answers, it would be more advisable to install a separate instance to ensure a proper back-out rollback if something goes wrong. Backward compatibility between 2008 and 2005 has less impact than say 2005 and 2000, but there are differences.
If you like, you could install the 2008 instance like Kris suggested, and then restore your 2005 (in SQL 2005 compatibility mode) to the 2008 instance. That gives you an opportunity for testing against the new features, while keeping the DB at 2005 level. You can then change the compatibility mode to 2008 and continue testing against your applications that use the DB.
If something goes wrong, your 2005 default instance is still around.
Note: You cannot restore a 2008 DB running in 2005 compatibility mode, back to a SQL 2005 installation. It only goes up, not down.
backward compatibility:
There are some deprecated engine features in 2008. Check out the complete list.
If your going to do an in-place upgrade, then run SQL Server upgrade adviser which will warn you about any issues you may run into.
SSMS 2008 will work against SQL Server 2005, but SSMS 2005 will not work against 2008.
What exactly are your concerns with backward compatibility? Do you have an app that specifically requires 2005? If this is the case, then stick with 2005 and run multiple instances.
If you have no reason to stick with 2005, then upgrade if at least for:
