Can I upgrade an existing instance without migrating?
This is nothing against SQL Server 2014, but I can’t stand in-place upgrades. Over the years I’ve had in-place upgrades work flawlessly on a few instances, and then had an install issue cause it to fail in the middle on other instances. Usually the critical instances, just because I’m not always lucky. And when upgrade fails, it doesn’t always roll back completely, or allow you to just re-run it. You may be down for a good long time.
But you’ve got backups, right? Really recent ones? Even so, how long does it take to restore them, if you need to do that to a different location? (And did you set it up right?)
While in-place upgrades may be fine for test and dev environments, they aren’t a good fit for your production instances where RPO and RTO are critical.
Should I raise the database compatibility level to 120 to use the new cost-based optimizer?
If you can test it for all your queries and know if it’ll be right for you, you can turn it on. Most folks can’t be sure of this so they start with it off to reduce risk from the migration.
The new cost based optimizer is very exciting, but there’s definitely a chance you can hit performance regressions. If you can’t test in advance, turning it on at the same time you migrate makes your troubleshooting more complex if you hit a problem.
Can we restore a full backup with NORECOVERY, run CHECKDB, and then later restore a differential?
You can’t run CHECKDB unless you’ve done a full restore and made the database writable. That means you can’t apply a differential backup afterwards.
You can potentially mitigate the risk by running a full CHECKDB against the database prior to running the backup. You may also run the backup with CHECKSUM (not a substitute for CHECKDB but it does apply some protection), and then run CHECKDB in a maintenance window shortly after the migration.
It’s all about your risk tolerance.
Is it still helpful to run DBCC UPDATEUSAGE after a migration or upgrade?
DBCC UPDATEUSAGE is typically no longer needed and only impacts output from sp_spaceused, anyway. Check the ‘remarks’ section on its page in books online for the full details: https://msdn.microsoft.com/en-us/library/ms188414.aspx
People got into the habit of this because it was needed to upgrade to SQL Server 2005. But it’s OK, you can let go of that habit (and it’s worth it, this command can be pretty slow).
Should I run sp_updatestats after a migration or upgrade?
This was a big deal when upgrading to 2005 because of changes they made to statistics, but it’s not needed specifically for SQL Server 2014 upgrades. Some folks like to do this to kick the tires, but don’t go crazy or think it’s magical.
What should I set ‘max server memory’ to for SQL Server 2014 if I’m running Standard Edition?
Possibly to more than you think. Max server memory in Standard Edition is limited to 128GB for the buffer pool in SQL Server 2014, but you may want to set it higher so that other parts of SQL Server can access memory above that level. Read more here.
How do I turn on the tempdb IO enhancement in SQL Server 2014?
You don’t have to, it’s just on.
Are there any known upgrade issues?
Microsoft keeps a list here, along with a pre-upgrade checklist: https://msdn.microsoft.com/en-us/library/bb933942.aspx
Which cumulative update should I use?
You should definitely use one if you care about performance, but the choice can be very complicated. Especially if you’re using Availability Groups. There is no easy answer: read all the Cumulative Update articles and test heavily before you go live.
Want more SQL Server setup help? Check out our setup guide for SQL Server.
And while you’re here, please don’t forget to think about your version of Windows.
36 Comments. Leave new
great article.
What is the best way to upgrade a failover cluster from Win 2008 / SQL 2008 R2 to Win 2012/SQL 2014?
Thanks
Great question. You must build a new cluster and migrate to it. You cannot upgrade a Windows 2008 cluster with SQL Server on it to Windows 2012.
not the answer I was expecting 🙁
but of course its not your fault… 🙂
What about upgrading a Windows 2008 R2 cluster with SQL 2012 to SQL2014 with no change to the OS? Is this ok?
One thing that got us after the fact was that statistics that were created from previous versions of SQL server eg (2008 r2) like from DTA were causing issues. I wrote a script to wipe out all the statistics and had 2014 recreate any that were needed after the fact.
Oh interesting– what kind of problems were you seeing, and how did you narrow it down to the statistics?
Also, were you using the new cardinality estimator? (Compatibility level 120)
Just curious for more info as I haven’t seen this one yet myself. But not everyone has manually created stats, so that might be part of it.
Yes we were using level 120. The statistics that DTA told us to create in 2008 r2 were no longer helping the queries that I ran through DTA. I took them out and it seemed to make those queries faster because it was changing the execution plans. Making the jump from 2008 r2 also seemed to change how SQL viewed scalar UDFs because SQL that once worked well started performing poorly.
Ah, ok! Yeah, I can see that making sense, because the new cardinality estimator deals with multi-column statistics differently.
It does also do estimates with UDFs differently too, and it’s interesting that actually caused you problems. I don’t doubt what you were saying, the hope is just that the new way of handling them would generally help code run BETTER — but then it’s just being arbitrary in a different way, not necessarily smarter.
Thanks for this info, this is interesting!
Thanks for the article.
What is the best way to upgrade existing SQL servers that are mirrored with a witness from Windows 2008/SQL 2008 R2 Standard Edition to Windows 2012 R2/SQL 2014 Standard Edition?
Thanks,
Ranchi
Hi Ranchi,
I’m not a fan of upgrading in place — I would get a new server, set it up fresh, test it, get it working well. You could then make it the mirror and do a rolling upgrade to it in a planned way. Ideally you swap in all new hardware, but if you’re comfortable with your mirror server being an older generation and with possibly degraded performance, you now have an extra server that you can use to rebuild, make it the mirror, etc.
Kendra
Thanks Kendra,
The servers that I adopted recently are about two years old. So I don’t think getting a new hardware in place is possible. Also these servers work off of local hardrive 🙁 because somebody set it up like that for PCi and other compliance reasons.
So I am going to try and do a rolling in place upgrade 🙁 and I have to upgrade both the OS and SQL server both are going to be a big in-place experiment.
I understand most of what you mentioned except I don’t see how I will have an extra server if I go the in place upgrade route.
Any advice on in place OS upgrade?
Thanks again,
Ranchi
I’ve had really bad luck with in-place upgrades. Basically in your situation you’re going to be without protection via mirroring for your RPO and RTO, possibly for a long time, and you’re not going to have any time to test the setup and configuration of the new OS, new SQL Server install, and config steps– there’s just a lot of things that can go wrong.
I wish I had some magic to make that better. My main advice would be to explain in advance what the risks and exposure are for doing this in place to your management and make sure they’re aware.
I’ve done both an in place OS upgrade and SQL upgrade on a dev vm before from windows 2003/SQL 2005 to windows 2008 R2/SQL 2008 R2 without issue. Having said that, in production if new hardware wasn’t available and I had to do an OS upgrade I’d probably backup the databases (and test backups) and do a fresh install of the OS. It will probably take less time than doing the upgrade because that tends to take quite a while (I took more than an hour just for the OS IIRC) plus you have a bunch of junk left over that the installer doesn’t clean up. This also lets you ensure the disks are all formatted with 64K allocation units which you can’t fix in an upgrade scenario unless you format them.
The total time of upgrade vs wipe and install will depend on how big your databases are as the recovery time tends to scale with size but you can tweak that by backing up to multiple files, using instant file initialization, and using the buffercount and maxtransfersize options to improve throughput. You can also save time if you have to do this in a bunch of machines by using a unattended install for the OS which will speed things along, [this post](http://www.derekseaman.com/2012/07/windows-server-2012-unattended.html) has a good walk through on doing that. Also setup your config file for SQL so you can do unattended installs and you should be able to stand up a server in less than an hour. I’d try both an in place and wipe just to compare the time and test your luck.
The total time of your upgrade will also vary depending on how many things go wrong that you haven’t accounted for.
The SQL Install /service pack/ cumulative update hung this time, it didn’t do that before.
The database is taking longer to restore than last time because I forgot to configure NIC teaming after reinstalling the OS.
X took longer because ____ SO MANY Y’S____.
Yes, things work according to plan sometimes. But just because you don’t get hit by a car when you cross the street once doesn’t mean you should stop looking both ways very very carefully.
In this scenario, we know that database mirroring with a witness and automatic failover has been configured. That means that RPO and RTO are critical, and they really need to talk super closely about risk and not just backup and restore time.
I don’t say this to jump on your comment at all, I’m just trying to point out why changing out machines in place can be really risky– and of course once you fail over to a higher version you can’t go back without losing data, too.
i dont like your article and your comments
That’s it. I quit. I can’t live with myself.
Well Jack, I really liked the article and comments, but I have to admit, your conclusion is impeccably reasoned.
One of the approaches I heard that I thought was good…-if- you have the time to get this deep with your queries and plans…was to keep the compat level at 110 or less, and then use trace flag 2312 to use the 2014 cardinality estimator in targeted ways at the statement level, at first. In practice though, we’re a bit closer to the more aggressive approach, switching to 120, and then using the 7.0 CE trace flag 9481 as needed to fix problems.
I keep forgetting about the tempdb change, that’s cool! 🙂
Nic – yeah, the gotcha there is that you have to be able to change your queries to use that trace flag. That’s a tough one for most shops.
Good points!
A couple of notes:
1. It’s possible to run DBCC CHECKDB on a database restored WITH STANDBY, although I’m not really sure why it’s important to run it between restoring full and differential backups.
2. Regarding in-place upgrades, they become much less scary if you have a failover cluster instance. There’s still the the scripts upgrade part, and you don’t have the protection of the cluster while upgrading, so it’s not completely safe — but then, moving to a new instance is also somewhat risky, as there can be always some problem or configuration issue that the testing missed.
Alex – if you do an in-place upgrade of a failover cluster, where do you run your user acceptance tests and performance tests on the new version?
Regarding point 1, you can’t use STANDBY mode when log shipping (or restoring a log in any way) from a major lower version to a higher version. The related error is “This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.”
Regarding point 2, I’ve run into issues with upgrading SQL Server on a failover cluster where the problem only occurred when failing the instance over to the upgraded node. No fun.
Ahh, you’re right about using restore for upgrading. It wasn’t clear to me that this was the case being described in the post.
And yeah, no fun at all regarding the cluster… oof.
What is the more common scenario in your experience: building another named instance on the same server and then using detach&attach and changing the application connection strings, or using mirroring/AG on new hardware and then failing over to it?
Thanks!
Just finished installing a SQL 2008 cluster on Windows 2008 r2 cluster (2 nodes Active-Passive) as a fresh install with zero user DBs. Unexpectedly we attained licensing and setup software for SQL 2014 which we now aim to have this Win 2008 cluster to be a SQL 2014 cluster instead. Am leaning towards running SQL 2014 upgrade (on Passive node first obviously), fail-over, & run SQL 2014 on 2nd node in finishing method first rather than uninstalling SQL 2008 cluster off of both nodes & then SQL 2014 cluster on both nodes. Would doing the uninstall method leave remnant left over files & registry settings which might negatively effect the SQL 2014 cluster install process?
A few days ago i upgraded a SQL Server 2008 R2 to SQL Server 2014, standard edition both In-Place.
When i finish the upgrade I notice than Visual Studio 2008 had not been updated.
The only solution is to install Visual Studio recent version manually or I miss something in upgrade process.
Thanks for reply.
Regards.
IT – for personal support, your best bet is to use Q&A sites like http://DBA.StackExchange.com.
Hi Kendra,
I am avoiding the whole upgrade path and using following approach to move from 2008R2 to 2014.
1. Generate the Script of Source DB
2. Split the script into 2 – a. Schema creation and b. Adding Constraint
3. Create a new Database on 2014 instance
4. Run the “a” script on the 2014 databse to create tables, views and other objects excluding Constraints.
5. Export The data from 2008 R2 database to 2014 database using the SSMS task wizard
6. Run the “b” script to add constraints.
What are your views?
Our views are things that join multiple tables together.
Select “poor joke” as MyReaction
Good day! How can i upgrade sql server cluster 2012 to sql server 2014? Version – Standart. We have 2 nodes and 2 instances on each node. Have you manuals for upgrade 2 instances on cluster sql server 2014
Stas – your best bet is to head over to Books Online for that one. Documentation is pretty good there. Enjoy!
in documentation i’m not found info about upgrade sql cluster with 2 instances. Maybe have you links?
Stas – it’s the same as upgrading one instance, except…you do it twice.
we have 2 nodes (node1 and node2) and 2 instances(inst1 and inst2). i started to upgrade on passive node2 instance inst1. After this sql don’t allow to upgrade instance inst2 on node2. If i try updgrae inst2 then sql show message ” upgrade instance is pending “. Thereore i try to upgrade node1 instance inst1. After this i upgrade instance inst2 on each nodes similarly. but system don’t allow and show message “upgrade instance is pending”. why? Can you explain me?
Stas – we’re not really set up to help troubleshoot cluster issues here in the comments. Try heading over to http://dba.stackexchange.com to post your questions.
Greetings all… Thought I’d share with you my experience with my SS2K8R2 to SS2K14 upgrade. All of my SS2K14 platforms are newly created boxes. This was done because I was moving from Enterprise to Standard.
My experience after performing a backup/restore was a rather dismal outcome. Overall performance was much worse when the compatibility level was moved to SS2K14. Once I changed it back to SS2K8R2 performance wasn’t so bad.
So where is what I did after my initial testing.
1) Changed the recovery setting to SIMPLE
2) Executed DBCC SHRINKFILE on the Log files
3) Changed the recovery setting to FULL
4) Changed the compatibility level back to SS2K14
5) Ran a DBCC CHECKDB with DATA_PURITY
6) Ran a DBCC UPDATEUSAGE
7) Ran a DBCC DBREINDEX on all tables
8) Ran an UPDATE STATISTICS on all tables.
This took about 2 1/2 hours to run. In testing overall performance was noticeably better, in fact about as good as the Production environment.