So.. You are sitting there on SQL Server 2008 (yes, I’m talking to you on SQL Server 2008 R2 also!)
Maybe you find yourself wondering, “Am I missing out on much?” And How!
Here are just a few of the changes that have come out in each version of SQL Server released in the past decade since SQL Server 2008. Plus some of the announced features so far in SQL Server 2019. (We can’t list them all, because you’d not ever finish reading this post! So we’ll link to the Official “what’s new?” page for each version. )
New Features by Edition of SQL Server
Only a few per each version. The link for each will bring you to the official list from Microsoft. On second thought, we can’t link to the official Microsoft post for SQL Server 2012 or 20124, they’ve stopped providing the documentation for those online. We’ll link to posts from around the web from these versions, so your mileage may vary.
There are so many changes. Extended Events. BI improvements. New CE. Faster recovery because of AG changes. So.Many.Changes!
- Columnstore indexes first came out (but, please, don’t use them here! If you’d benefit from Columnstore – come on up to SQL Server 2016 or later!
User Defined Server Roles – lesser discussed, but fairly cool feature for improvingsecurity of your environment. Great for regulated/audited environments!- AlwaysOn Availability Groups first came out (they work here, but like Columnstore, head up a version or two higher, you’ll probably upgrade with ease to SQL Server 2016 or 2017!
- In-Memory OLTP – It was V1 here. But it was here!
- Backup encryption native in SQL! Paying for a
third party backup solution just for that feature alone? You can stop that now. - Improvement of Always On Availability Groups and Columnstore Indexes – there are a lot more features worth discussing (backing up to Azure is seamlessly supported for the first time here, for example), but Microsoft really continues to invest in these features. Even better in later versions!
- Up to 128GB of RAM in SQL Server Standard (This may have been a 2012 feature, supporting clients in all versions of SQL makes some of these trivia games harder 😉 But – either way – it’s more than you could have in Standard Edition in SQL Server 2008!)
(now we’re starting to get somewhere. All of the above features were improved here. But wait. There’s more..)
- QUERY STORE! – We love Availability Groups also, but we’re performance tuners at heart. If this were a Geocities site, we’d have this be flashing and dancing instead of “just” bolded. Store and visualize query plans, worst offending queries, plan regressions, etc. OUT OF THE BOX.
- Always Encrypted – Sort of neat. A way to work with developers to truly encrypt the data. We all know TDE is kind of neat and affords some protection, but also mostly ticks an auditors checklist. This helps you roll out some stronger encryption by only writing encrypted data to SQL. Even us DBAs can’t crack it (alone)
- Create or Alter – I mean there are SO MANY FEATURES (the installer got way better to help you from shooting yourself in the foot, columnstore improved even more, temporal data types, etc) – but typing out CREATE OR ALTER is so much better than all that IF EXISTS typing!!
- ENTERPRISE FEATURES IN STANDARD EDITION – if this were Geocities, we’d be doing that flashy thing again here. In SP1 of SQL Server 2016, Microsoft said “here you go, you can use most enterprise programmability features in SP1” Compression, Columnstore, In-Memory, etc. (Enterprise only features are things like TDE and online index rebuilds, but the list of enterprise only is smaller)
- Resumable Index Rebuilds – Online index rebuilds can be “paused or stopped” and started back up. We aren’t wading into the holy war of “to rebuild or not to rebuild” but this is cool no matter which camp you sleep in.
- Query Store was already way cool. It got way cooler. Wait stats are stored here as well now giving even more analysis. Included. No extra outside tool needed for that alone (we still love the right monitoring tools and use them! But query store is nice for correlating wait stats with queries and seeing which queries impact them the most)
- Linux support. I mean – back when you installed SQL Server 2008, did you actually think you could have an Availability Group go from windows to Linux? (Of course you didn’t… You didn’t even know what an Availability Group was, silly!)
This is just what’s been announced so far publicly.
- Accelerated database recovery – improvements to the DB recovery process may spell performance improvements for your system.
- Resumable index creates – like the rebuilds – now for creates!
- Many query performance improvements.
- Always On Availability Group improvements galore
- Just go read the link. It’s cool. The world has really changed since Database Mirroring and SQL Server Profiler days 😉