In place upgrade from SQL Server 2005 to 2008

Question

Situation - Server running windows server 2003 and SQL Server 2005. About 18 various databases on the server. We need to upgrade to Windows Server 2008R2 and SQL Server 2008. We can't afford to lose any of the databases (if I did my life as I know it would be over...)

What's the safest / simplest way - and how do I get all the users and roles done as well?

Answer 1

You can use the "upgrade in place" feature of SQL Server 2005 to SQL Server 2008. IIRC, you can also use the Windows Server 2008R2 installer to upgrade from Windows Server 2003 because Windows Server 2003 is only two versions back.

This answer focuses on SQL Server. I recommend that you do a couple of things:

• Read Microsoft's SQL Server 2008 Upgrade Technical Reference Guide. It is long, nearly 500 pages, but you can skim/skip some sections like those pertaining to side-by-side upgrades. There is a section that describes the costs and benefits of "upgrade in place" and "side by side upgrade".
• Be sure that all of your databases are supported on the versions of Windows and SQL Server that you intend to use. In the case of third-party software, that means you should ask your vendors. You may find that you need to upgrade to more recent versions of the third party software for that software to work properly with SQL Server 2008. With your own code, it is best to thoroughly test your software on a server with the new versions of Windows Server and SQL Server before you do the upgrade "for real". At the very least, run the SQL Server 2008 Upgrade Wizard, which will point out common problems.
• After the upgrade, have some sort of quick 'smoke test' that will convince your users that their software works OK. Once users start modifying data, it will be increasing difficult to go back to the old versions of stuff because you will not be able to easily move their changes backwards.
• If a problem is discovered a few days into running the new systems, you may be stuck with it because going backwards would throw too much information away or you would have to spend days or weeks finding a way to migrate your data backwards, and the situation is worse if some things work and some things don't work. Some problems might have workarounds and just aren't worth the trouble. I like to have a list of "reasons we will roll back the upgrade" agreed upon by all stakeholders before starting. I also like to have a drop-dead time for any talk of rolling back the upgrade. That might sound like "After lunchtime on the first day, we are stuck with whatever we have".
• After you upgrade to SQL Server 2008, any other SQL Server 2005 computers that you have will no longer be able to read backup files. This is important if you restore production backups to development or test machines or trade backups with a third party. There is no way to create a "2005-compatible" backup from a 2008 server. SQL Server 2008 can read older backups, so any backups that you have already taken can be read by the new SQL Server.Many developers are surprised by this.
• With in-place upgrades, you cannot go backwards, doubly so if you are replacing the OS. If you can, isolate the machine from user changes and image the drive with Ghost or something similar before doing the upgrade. If something catastrophic happens, you should be able to get back to where you were before the upgrade. Half of being a good admin is knowing what to do if something catastrophic happens, because it does with surprising frequency.
• You should make sure that you upgrade to the latest service packs for Windows Server and SQL Server while you have the server offline.
• Make sure that your hardware is compatible with Windows Server 2008 R2. That means having drivers downloaded and ready to go for any unusual hardware: RAID cards, fancy NICs, FibreChannel HBAs, etc.