Are SQL Server in-place upgrades as ill advised as they used to be?

I have been working with SQL server on and off since SQL Server 6.5, the old advice that still rings in my head was never to do an in-place upgrade.

I'm currently upgrading my 2008 R2 DEV and TEST systems to SQL Server 2012 and need to use the same hardware. The thought of not having to restore my Reporting services configuration is very attractive and i'm really up against the wall time wise. There is no analysis services involved or anything unusual or non-standard - only the database engine and reporting services are installed.

Has anyone experienced serious issues with in-place upgrades? Or should I reevaluate my position about in-place upgrades?

Really Short Answer - In Place is okay. You can review your configuration afterwards and implement the best practices for SQL Server 2012.

A Longer Answer on SQL Server Upgrades/Migrations

So this is an opinion thing and there isn't a necessarily wrong or right answer but I prefer migration style upgrades over in-place for a lot of reasons. That being said - some of my clients for various reasons have had no choice but to do an in-place and really since SQL Server 2005, in-place upgrades haven't been as bad as they used to be.

Why I Prefer A Migration to an In-Place Upgrade

• Easier Rollback - If something goes wrong you can rollback by simple saying "we aborted the upgrade.. Please change connection strings to old server while we resolve this". With an in-place you are fixing it or you are down.

• Refresh Hardware - Hardware changes rapidly. You can easily get stuck on hardware that was right for your company 4 years ago but not right for today and the next four years with an in-place upgrade. You will likely have to do a migration at some point anyway for the new hardware.

• Feel Better - Sure... This one is subjective, but it feels good knowing you are starting with a new OS installation, a new SQL installation with no cobwebs from the person on the job before you (or you before you knew what you knew today) that could possibly cause you headaches in the future.

• New OS - A migration gives you a chance to start with a new OS version if you aren't on the latest and greatest today.

• You Can Test It - Ever want to get a set of baselines on a new machine before you install SQL and cloud it up with databases and usage? You can do that now.

• Sometimes it is easier to sneak in best practices - Maybe the SQL Server service account was a local admin. Maybe Builtin Administrators is in the SA server role. Maybe things have been sort of hacked together to make it work before. You can fix that all and start fresh.

• Free test environment and extra sleep - It is a great benefit to have an environment you can work in ahead of the actual cutover day when you make this new environment live. Doing a migration to a new environment means you can build it during business hours, well ahead of your actual cutover day and test it in many ways ahead of time. You can run full regression testing on all applications and systems for days and have a great peace of mind before you actually do the final set of restores/attaches and cutover all applications and access to the new environment.

• You don't have to do it all at once - A very common situation I bump into is an environment that is trying to consolidate to just a few instances. Perhaps one per version, perhaps one per "tier" and version. A lot of these projects have different timelines for various applications and databases based on testing, project plans and vendor certification timeliness. Doing the migration means you can move those databases that are ready, when they are ready and still handle requests for those databases that can't move for one reason or another.

Mind you I am not saying you have to do this as a migration. In-Place works and it works well if you are not planning on the buying new hardware in your budget and can't do that for this upgrade. The support in the upgrade process is so much better than it was in the 6.5 days so you are not putting yourself in a bad position doing this.

If you do plan on doing in-place for dev/test but want to do a migration for production you might consider doing at least one migration before production. This way you can work out your checklist ahead of time and deal with any potential issues that you weren't thinking of.

Attach/Detach vs. Backup/Restore for Migrations

If you decide to go with the migration approach, there is still one more decision you may still have a debate over and that is how you move your database to the new environment. You can either detach your database from the old server and attach it to the new or back it up and restore it there.

I prefer backup/restore. The biggest advantage I hear about detach/attach is that it saves some time. For me backup/restore wins for a few reasons:

• Keep the old accessible - This allows you to still have an accessible database on the source server. detach/attach should do the same, but it will require a few steps and there is room for human error with detach/attach that could complicate this.

• You are guaranteeing that you have a backup - Instead of just taking a database from a detach and potentially forgetting a backup step, you've made sure that you've taken that backup.

• Human error - If you delete the wrong file, forget where you are sending something or otherwise mess up your steps, you risk much by moving the data and log files around for your database. Now you can mitigate this by copying instead of cutting (and if you do detach, you should get out of the cut and paste habit) but you could stil mess up. SQL Server is no longer locking thos

StackExchange Database Administrators Q#29328, answer score: 95