asked
SQL Server 2005 64 bit Enterprise edition to SQL Server 2008 R2 64 bit Standard edition
We have a Production server with SQL Server 2005 SP4 64 bit Enterprise edition. We are planning to convert that into a development server with SQL Server 2008 R2 64 bit Standard edition. There is no direct upgrade path from SQL Server 2005 64 bit Enterprise edition to SQL Server 2008 R2 64 bit Standard edition and so we will have to uninstall SQL Server 2005 Enterprise edition and then install SQL Server 2008 R2 Standard edition. I have made sure that we are not using any enterprise edition features. Has anyone tried this before? Would doing the detach-attach for user databases be a good idea in this situation? Would it be possible to do a clean uninstall of SQL Server 2005 SP4 in this scenario or would it be better to start from scratch by doing a reinstall of OS also - Windows 2008 Enterprise in this case?
I would start from scratch with a clean install of Windows 2008. Yes, you can do detach, attach for this type of upgrade, although, I would also be sure I have a good, tested, backups ready too. You can't be too paranoid. The real problem is, are you using any enterprise only functionality that won't be available to you in Standard? That could be the showstopper.
Personally on a development machine I would install Developer Edition and pay the $50 per developer that will be using it. Anytime I am moving from one environment to another and can rebuild the box from scratch I prefer to do so especially if it requires uninstalling applications. If time is a factor then an uninstall of SQL 2005 with a fresh install of SQL 2008 R2 should pose any problems. Detaching the user databases from 2005 and attaching to 2008 R2 shouldn't pose any issues as well, however I would do as @Grant Fritchey stated and have tested backups as well. Any other user objects such as users, SQL Jobs, SSIS packages, etc have scripted out to migrate to SQL 2008 R2.
