Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
I opted to do an inplace upgrade on 1 server with reporting services installation. The only issue I came across was attempting to use import export tool in SSMS with native client 11. Attempting the transformation failed with an error about unrecognized datatypes. the workaround I used was to save the package and run it in SQL Data Tools(BIDS replacement) which worked fine. I think this must be to do with the config file for SSIS 2008 not being overwritten. It occured to me later, you may simply be able to change the native client back to 10. – DamagedGoods Jan 10 '13 at 9:37

4 Answers 4

up vote 70 down vote accepted

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 those files and it's just too easy to delete a file accidentally for me to risk it.
  • It isn't really that slower - Taking a backup and copying it is a bit more time, but it isn't that much that I am willing to pay the extra risk for it. In fact - using full recovery model and log backups, you can take the downtime down even lower for cutovers as described below in "How to Make the Migration Approach Faster"

If you do decide to do the backup/restore - that means your old source database will still be online. I like to bring that database offline after taking the backup. I sometimes go a step further and take the whole SQL instance offline after I've scripted out security, jobs, linked server, certificates, database mail settings, and other instance wide information. This avoids an issue during testing where someone says "Everything looks great!" only to realize a day or two later that they have been talking to the old database on the old server. Taking those databases offline or the whole instance offline allows you to prevent those false positives and the mess they make.

How to Make the Migration Approach Faster

You can minimize the downtime required for the cutover from an old to new environment for a busy production environment with little downtime by utilizing the full recovery model. Basically - stage the environment you are migrating to by restoring the latest full backup, any differential backups and any already taken log backups specifying NORECOVERY - then all you will have to do for the final cut over is restore the log backups that were not yet restored and the final log backup you wish to restore specifying WITH RECOVERY. This way for a large database, the actual cutover downtime window can be drastically minimized by paying for the cost of the full, differential and most log restores ahead of the downtime window. Thanks to Tao for pointing this out in the comments!

How to Make the In-Place Upgrade Safer

A few things you can do to improve your experience and results when choosing the in-place approach.

  • Backup - Take appropriate backups of all user and system databases of your environment ahead of time and ensure they are good (I'm paranoid.. I actually would restore them someplace first to really know they are good.. May be wasting your time.. But you may thank yourself in the event of a disaster).. Script out any configuration information about the SQL and OS installation at that environment.
  • Test things well before you start - Verify that you have a good environment and good databases. You should be doing things like looking at the error logs and running DBCC CHECKDB on a regular basis, but before doing an in-place upgrade is a great time to start. Fix any issues ahead of time.
  • Ensure OS health - Don't just make sure that SQL is healthy, make sure your server is healthy. Any gnarly errors in your system or application error event logs? How is your free space?
  • Prepare for the worst - I had a blog post series awhile ago that went on the premise that if you aren't preparing for failure - you are actually preparing to fail.. I still believe that. So think through the issues you may have and deal with them accordingly ahead of time. Get yourself in the "failure" mindset and you will think of things that you wouldn't have otherwise.

The Importance of Upgrade or Migration Checklists

If you do decide to do an upgrade (whether in place or migration) you should seriously consider creating a checklist and using this checklist in each environment. You should include a bunch of things in this checklist, not the least of which:

  1. At the Start - Do some things like perform a test upgrade, test your applications on the latest Database Compatibility level and consider running a tool like the SQL Server Upgrade Advisor ahead of time to see what sort of tasks you need to complete before doing the SQL Server upgrade or migration.
  2. Pre Steps - Cleanup, OS tasks, patching ahead of time, preparing applications for the upgrade (clean shutdowns, connection string work), backups, etc.
  3. Upgrade/Migration Steps - Everything you have to do for the upgrade or migration to succeed and in the right order. Installation, changing (or not changing depending on your testing and approach) compatibility mode changes to databases, etc.
  4. Post Migration/Upgrade Steps - Various tests, post new version or new server configuration options, best practice implementation, security changes, etc.
  5. Rollback Steps - All along the way you should have rollback steps and milestones. If you get this far and this happens, what will you do? What are "do a full rollback" criteria? And how do you do that rollback (reverse connection string changes, change back settings, go back to the old version, reinstall if an in place, point back to the old server if a migration, etc.)

And then have the person who will be doing the production upgrade follow the checklist in some environment other than production - especially one that closes resembles production if possible ("South of prod", as I say...) and note any issues or points where they had to divert from the checklist or improvise because of a lack in the checklist. Then get the changes merged in and have fun with your production change.

I can not over stress the importance of testing thoroughly post migration or upgrade and ahead of your migration enough. Making a rollback decision in the midst of an upgrade should be an easy one - especially during a migration. If there is something uncomfortable, rollback and figure it out if you can't troubleshoot it effectively and reliably in the heat of the migration. Once you are live on this new environment and users connect - rollback becomes a difficult task. You can't restore a SQL Server database to an earlier version. That means manual work and data migrations. I always wait a couple weeks to kill the old environment but you should do all you can to avoid needing that old environment by finding all of your issues before your live users ever touch the new environment. Preferably before you even kick off the upgrade/migration.

Quick note about SQL Server Reporting Services migration/upgrade Migrating an SSRS installation isn't quite a herculean task that many think it is. This technet/books online article is actually quite handy. One of the most important admonitions in that article is "Back up the encryption keys" especially if you have a lot of saved sensitive information like scheduled report e-mail recipient e-mail addresses, connection information for a multitude of connections, etc. You can ask one of my clients from awhile back how important that is. They know because I messed that step up and spent quite a lot of time modifying report schedules and connection string permissions.

share|improve this answer
Mike's answers hit the points very well. I'd read his 'a bit more' section and use the risk analysis I mentioned to come up with a decision. – Ali Razeghi Nov 27 '12 at 1:21
+1 fantastic, thorough answer that most of us are way too lazy for... :-) – Aaron Bertrand Nov 27 '12 at 1:50
I blame too much coffee and green tea and a small pile of other things to do, Aaron. Speaking of which.. That pile is still there. – Mike Walsh Nov 27 '12 at 1:51
Procrastination, the foundation of many vote worthy answers. – Mark Storey-Smith Nov 27 '12 at 1:54
thanks Mike, I know its not strictly a question that can be answered, as is a LITTLE discussion-like. But I was preparing to flatten the box as usual and rebuild the OS, etc as usual. Then I thought it interesting to see general consensus had changed, i'm sure some others will find this a useful post too down the line and that is one of the major objectives I guess – DamagedGoods Nov 27 '12 at 2:33

In my experience, the same decision making process should be made as prior. AFAIK there hasn't been any 'world changers' with SQL Server installation, within the MS SQL Server product in itself, and the potential problems you have when rolling out software with millions of lines of code. Something bad could happen and now you're stuck with no 'ROLLBACK' option.

You do however have other alternatives in place. You could consider making a snapshot of the system, restore elsewhere, perform the upgrade and see what happens. This test should give you a lot of comfort, but it doesn't absolutely guarantee no problems will arise on the prod box. However, this is an option that was not available back in SQL 6.5 days.

I would just assume the worst case scenario. You do a in place upgrade and it fails miserably. You have to then recover from this within your RTO and RCO. Does the business understand the risks and do you have plans in place to mitigate it?

If the business is not OK with this, then don't do it would be my advice.

share|improve this answer
"I would just assume the worst case scenario." +1 - Sage advice for any DBA task. Good answer. – Mike Walsh Nov 27 '12 at 2:02

If you have your servers running in a virtual environment, you can perform a snapshot on a clone and then apply the in-place upgrade and test the instance to verify the upgrade was successful. If it works, you can apply the snapshot and make the clone the production server. If it goes poorly, you can delete the snapshot and go back to the pre-upgrade image to try again, or delete the clone and to a full migration.

share|improve this answer
Only if the storage is also virtualized and part of the snapshot. If the storage is directly attached to the VM it won't be 'rolled back' when the snapshot is restored... – Remus Rusanu Dec 29 '13 at 17:08

Nothing is wrong with either approach per se -- I've done both and both the outcomes are typically good.

If there is a problem with the migration approach, it's not technical: it's laziness. Too often I find that the reason a company hasn't gone fully to xxxx version yet is because they chose a swing migration and never got around to do the hard work to completely move. Now they have two or more sets of servers instead of one.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.