Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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?
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.
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).
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
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
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.
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.
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
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.
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.
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).
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.
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.
- 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:
Last Update: 2014-06-05
About the author
View all my tips