By: Aaron Bertrand | Updated: 2014-06-05 | Comments (10) | Related: > Upgrades and Migrations
Problem
With SQL Server 2008 and SQL Server 2008 R2 both reaching end of mainstream support in mid-2014, many customers will be looking to migrate to a newer edition rather than purchase extended support or continue unsupported. Currently, their choices are SQL Server 2012 and SQL Server 2014. Which one should they pick, and why?
Solution
There are a variety of benefits to skipping 2012 and going right to 2014 - not all of them may be applicable to you today, but you should also consider that taking advantage of them later will be easier if you're already on that version. In the meantime, you will automatically be taking advantage of several under-the-covers improvements that you'll get without having to touch a line of code. You also automatically get all of the fixes from all of the service packs and cumulative updates that were published for 2012 in 2+ years, up to about March 2014 (and beyond, with SQL Server 2014 Cumulative Update #1).
Availability / Disaster Recovery Enhancements
In the AlwaysOn Availability Groups area, SQL Server 2014 now supports 8 secondary replicas instead of 4, which is great particularly for scaling out the read-only portions of your workload. There have also been enhancements to reliability - in SQL Server 2012, if the primary or quorum become unavailable, so do the read-only secondary's. In SQL Server 2014, this situation does not cause the secondary to be unavailable (a fix that is potentially going to make it to SQL Server 2012 in a forthcoming update). There are also new ways to use Azure to support your on-premises environment, including hosting Availability Group data files in Azure, hosting an entire replica in a Windows Azure VM, and backing up to a URL to store in Windows Azure Blob Storage. These features can help avoid the prohibitive costs associated with a second data center, especially if it is only being used for these specific features.
Performance Enhancements
There are many performance enhancements in SQL Server 2014 that will allow you to squeeze more performance out of the hardware you have than you could with SQL Server 2012. Some improvements are covered quite well in Daniel Farina's tip, What's new in SQL Server 2014? Is it worth the upgrade?, but I will give a quick overview of these and others:
- Standard and BI Editions now support 128 GB of memory (SQL Server 2008 R2
and 2012 only supports 64 GB). Of course, if you are coming from SQL Server
2008, Standard Edition supported the operating system maximum, so this could
be a step down. But that step down is less severe in the newer version. Enterprise
Edition now supports up to 640 cores, and 4 TB of RAM (this limit is lower in
a virtual machine, but due to operating system and Hyper-V limitations, not
SQL Server itself). The same limits on CPU, memory, and data file size still
exist for SQL Server Express.
-
In-Memory OLTP is a new lock-free, latch-free, optimistic concurrency approach
to storing your data in memory. This is nothing like DBCC PINTABLE you might
remember from years ago, which still suffered from the locking and latching
problems that are resolved in SQL Server 2014. There are many configuration
options, and this doesn't promise improvements in all or even most workloads,
but in some scenarios the improvement in performance and/or throughput can approach
40X. Kalen Delaney has written an extensive
whitepaper that describes this feature in-depth. Note: Enterprise Edition
only.
- Delayed durability is a way to defer the acknowledgement of the transaction
log records getting written to disk - which means your transaction can continue
without waiting, and assume that the log record *will* be written. This is a
good way to compensate for a reliable, but slow disk subsystem, at the risk
of data loss. I've
blogged about my observations over at sqlperformance.com. Note: All editions.
- In SQL Server 2012,
columnstore
indexes were introduced as a great benefit to certain data warehouse workloads,
but they had several limitations which made them practical only in very limited
scenarios: they were non-clustered, supported a limited number of data types
and, most importantly, were read only after creation. In SQL Server 2014, this
feature set has been expanded to support clustered columnstore indexes, many
of the data type limitations have been removed, and the index is writable. They've
also introduced a new archival compression algorithm, with a much greater compression
rate (at a slightly higher CPU cost). So, with the newer version, you have a
much better opportunity to use this feature to your advantage. Microsoft's Jimmy
May has
a great blog post and PowerPoint deck on the new technology. Note: Enterprise
Edition only.
-
sys.dm_exec_query_profiles is a new DMV that allows you to monitor the progress
of the I/O activity in every operator within a query that is currently executing.
Note that the DMV is only populated when the query has been issued while certain
execution plan settings are in effect (e.g. SET STATISTICS PROFILE ON).
- New Resource Governor
settings allow you to throttle I/O in terms of MAX_OUTSTANDING_IO_PER_VOLUME
at the instance level - controlling the number of I/O operations sent to the
disk subsystem. At the resource pool level, you can use MIN/MAX_IOPS_PER_VOLUME
to set the minimum number of IOPS to reserve, and the maximum number to allow,
per resource pool. In both cases, this is measured in number of operations,
regardless of size. Note: Enterprise Edition only.
-
Buffer Pool Extension is a way to use fast SSD drives to substitute for
memory. This is great for cases where your database size exceeds the amount
of RAM, especially if the server does not support adding more RAM (even though
RAM is cheap, it is not always possible to just add more memory). This technology
stores only clean pages on the SSD drive, allowing for faster access through
the buffer pool to data that would otherwise have been pushed back through standard
I/O access methods - which would be slower even in the case where the data and
log files are also on SSD. Since only clean pages are stored, this does not
impact durability whatsoever. Note: Standard, BI and Enterprise Editions
only - however Enterprise Edition supports a buffer pool extension up to 32X
the size of physical memory, while the other editions only support 4X.
- Mainly a transparent improvement, the
new cardinality estimator is great at fixing a lot of heinous queries and
generating better plans without having to re-write the queries themselves; see
this blog post by Kendra Little for a great example. In essence, the new
cardinality estimator has received several enhancements that allow it to make
better decisions based on table and index cardinality. This works well in *most*
cases; the only concern I would have would be a case where a lot of queries
introduce regressions in performance due to the new plans. You can always control
this behavior using the database compatibility level (the new estimator will
be used by default under 120, and the old under 110). This is not the way I
would have chosen to control this behavior; I would rather have a database property
for that. However, you do not have to resort to wholesale implementation one
way or the other; you can use trace flags to force the new or old behavior at
the query level, too. So, if you have a handful of queries that perform worse
under the new estimator, you can set the compatibility level to 120 and add
OPTION (QUERYTRACEON 9481) on those individual queries. Likewise, if most queries
are worse under the new compatibility level, you can revert to 110 and use OPTION
(QUERYTRACEON 2312) to set the outliers to the new model. Note: All editions.
- Another transparent improvement that you may not notice unless you're looking for it is the reduction in eager writes to tempdb. In SQL Server 2014, the engine will try to defer or completely avoid any physical writes of data that it assumes will be transient and short-lived anyway. This enhancements is described in detail over on the SQL Server team's blog. Admittedly, this improvement has been back-ported to SQL Server 2012, if you updated to Service Pack 1 Cumulative Update #10 - and will eventually appear in Service Pack 2 as well. Note: All editions.
Security Enhancements
Backup Encryption
SQL Server 2014 now supports native backup encryption in Standard, BI and Enterprise Editions. This can eliminate your reliance on 3rd party products for this functionality, and can also help in situations where you are currently using Transparent Data Encryption to achieve encryption at rest (when really you don't need the active data to be encrypted). I've blogged about this functionality and performance observations over at sqlperformance.com.
New Permissions
Several new permissions have been added in SQL Server 2014:
- CONNECT ANY DATABASE : Using this permission you can allow a login to connect
to any database (existing or future) without having to configure connect privileges
on every database (and doing it again every time a new database is created).
This can be very useful for auditing scenarios.
- IMPERSONATE ANY LOGIN : This allows a login to impersonate any other login,
without giving them sysadmin privileges. Conversely, you can use it to deny
impersonation even for logins that inherently have the right to do so (just
remember that you can't really prevent a sysadmin or Windows Administrator from
doing anything - assume they own the box and everything on it).
- SELECT ALL USER SECURABLES : Essentially allows SELECT, but not INSERT/UPDATE/DELETE/MERGE
permissions on all user tables in all user databases. Useful for auditors or
anyone who needs a read only role across all databases, without having to configure
things like db_datareader or individual object-level permissions in every database,
present and future.
Other Thoughts and Considerations
Licensing
For the most part, SQL Server 2014 licensing costs carry the same sticker shock as SQL Server 2012. For those licensing by CPU rather than by CAL (and CPU is your only real choice today for Enterprise Edition, with the exception of those grandfathered in by SA*), the switch from socket to core can be quite expensive, if you have Intel processors and more than 4 cores per socket. The per-core price is roughly one quarter of the previous per-socket price (and cheaper for AMD processors), so for most people who weren't early adopters for high-core CPUs, the costs should be about the same. For those who need Enterprise features, but don't need core-based licensing, well, those people are going to be holding on to their 2005, 2008 and 2008 R2 instances for as long as they possibly can.
* Customers who were under Software Assurance (SA) when 2012 was released, and who took advantage of the limited-time-only offer for Enterprise + CAL licensing model (where you could use up to 20 cores under CAL, instead of paying per core), can continue to purchase 2014 Enterprise Edition licenses under that model, as long as that same SA agreement is still current and in good standing.
There are a couple of minor changes that may make 2014 a more expensive proposition for some customers - for example, to get a "free" standby license for DR, you have to pony up for SA, which wasn't a requirement in SQL Server 2012. I am not a lawyer, and I do not work for Microsoft's legal or compliance departments, so I strongly urge you to make any licensing-based decisions with as much knowledge as you can get from the 2012 Licensing Datasheet and the 2014 Licensing Datasheet and, when you can, your local Microsoft licensing representative.
Support
Your end-of-life dates for mainstream support, service packs, and security fixes are - by definition - extended by over two years. Unless you run and maintain SQL Server instances well after their sunset date, you really should go for the most recent version available, since end of mainstream support is driven almost exclusively by release date. If you choose to upgrade to SQL Server 2012, it just means you'll hit the end of mainstream support (and stop receiving performance and functional fixes through service packs and cumulative updates) that much quicker.
Operating System
If you are going to go through an upgrade process for SQL Server, please consider moving the underlying operating system to a newer version as well (particularly when you are doing a side-by-side installation, where you can fully regression test everything before making the switch). Supporting a brand new version of SQL Server on a server operating system that is either already out of support, or will be soon, leads to the same types of issues that keeping an unsupported version of SQL Server does. Plus you'll be missing out on performance enhancements made in the meantime - many are just generic improvements, but some have been specifically made for SQL Server. One is the ability to use Cluster Shared Volumes for a Failover Cluster Instance, introduced in Windows Server 2012 (Michael Otey rhymes off a few others over at SQL Server Pro).
Cumulative Updates
SQL Server 2014 has already had its first cumulative update published. The SQL Server 2012 RTM branch was retired after Cumulative Update #11. And the Service Pack 1 branch is going strong, currently at Cumulative Update #10. In spite of the doomsday warnings on the Knowledge Base articles, I am a big fan of staying completely up to date on these updates, particularly since there are often transparent performance fixes and other corrections that may fix problems you don't have yet (or you do but don't know it). Of course, like all software updates, you need to be sure to completely regression test your entire system before deploying these updates to production.
Conclusion
I hope I've made a compelling case - for anyone debating SQL Server 2012 vs. SQL Server 2014 - with all else being equal, that SQL Server 2014 is the better choice. I've also posted my slide deck on what's new in SQL Server 2014 - the deck itself won't give you much more detail than I've given above, but several slides have links to blog posts, official documentation and other resources. You can download it from my blog post about SQL Saturday #308 in Houston, TX.
Next Steps
- Please be sure to fully understand the licensing model, regardless of which version you plan to use. Licensing Datasheets:
- If you haven't already, please be sure to grab a trial edition from the Evaluation Center (SQL Server 2012 | SQL Server 2014) or from MSDN if you're a subscriber, and test, test, test.
- Review the following tips and other resources:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-06-05
