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.