How to Track Database Changes in Sql Server 2008 R2

Which Version of SQL Server Should You Use?

Last Updated June 11, 2021

Wait! Before you install that next SQL Server, hold up. Are you sure you're using the right version?

I know, management wants you to stay on an older build, and the vendor says they'll only support older versions, but now's your chance to make your case for a newer version – and I'm gonna help you do it.

I'm going to go from the dark ages forward, making a sales pitch for each newer version.

You should probably install SQL Server 2008 if…

• You're dealing with an ancient application that's no longer under support, like a third party app written by a vendor who has folded up and closed down
• You're only licensed for SQL Server 2008 because you didn't buy Software Assurance, which gets you the ability to run newer versions for free
• You only have Standard Edition, and you want support for unlimited memory (which goes away in 2008R2)
• You don't know that both SQL 2008 and SQL Server 2008 R2 (which came out about two years later) both have the same end-of-support date (and, uh, now you know that, so, that rules out that one)
• You don't know that Extended support ended July 9, 2019. Yes, if you host it in an Azure VM, Microsoft will give you extended support until 2022, but that's it.

With that end-of-support date, I'm just not a fan of new 2008 installs today. So let's move on.

You should consider SQL Server 2008 R2 if…

But I got a really good deal on this CD at a garage sale

You need these new features added since SQL 2008:

• PowerPivot for Excel (which has been replaced)
• Utility Control Point (which nobody uses)
• Master Data Services
• StreamInsight

Uh…yeah. No. Let's skip this version and move on.

You should consider SQL Server 2012 if…

• You're dealing with an application whose newest supported version is only SQL Server 2012, but not 2014 or newer.
• You are absolutely averse to installing patches (because SP4 came out in Oct 2017, and there's only been one security fix since, and that's it.)
• You're comfortable building another SQL Server in 2 years (because support ends in July 2022.)
• You either don't need robust encryption for your backups, or you're willing to buy a third party tool to get it.

2012 introduced a few other features – Availability Groups, columnstore indexes, contained databases, Data Quality Services – but they were so limited that it's hard to consider this a good starting point for those features today.

You should consider SQL Server 2014 if…

• You're dealing with an application whose newest supported version is only SQL Server 2014, but not 2016 or newer.
• You want to use Always On Availability Groups – but I'm even hesitant to put that here, because they continue to get dramatically better in subsequent versions. I'd just consider this a minimum starting point for even considering AGs (forget 2012) because starting with 2014, the secondary is readable even when the primary is down.
• You need to encrypt your backups, and you're not willing to buy a third party backup tool.
• You use log shipping as a reporting tool, and you have tricky permissions requirements (because they added new server-level roles that make this easier.)
• You need faster performance without changing the code, and you have lots of time to put into testing – 2014's Cardinality Estimator (CE) changes made for different execution plans, but they're not across-the-board better. You still have to put in time to find the queries that are gonna get slower, and figure out how to mitigate those.

2014 also introduced a few other features that don't sound like assets today: In-Memory OLTP, which wasn't production-quality at the time, Buffer Pool Extensions, data files in Azure blobs, backing up to a URL, and Delayed Durability.

You should consider SQL Server 2016 if…

• You're an independent software vendor (ISV) – because 2016 Service Pack 1 gave you a lot of Enterprise features in Standard Edition. This meant you could write one version of your application that worked at both your small clients on Standard, and your big clients on Enterprise.
• You want an extremely well-known, well-documented product – it's pretty easy to find material off the shelf and hire people who know how to use the tools in this version.
• You use Standard Edition – because it supports 128GB RAM (and can even go beyond that for some internal stuff like query plans.)
• You want to stay here until 2025-2026 – this version has more years left in its support life than SQL Server 2012/2014, so you can install it once and stick around longer.
• You have compliance needs for a new application – And I'm specifically calling out new apps here, but 2016 adds Always Encrypted, Dynamic Data Masking, Row Level Security, and temporal tables, features which make it easier for you to build things to protect and track your valuable data. It's still noteasy, it's justeasier.
• You want to use columnstore indexes – I'm going to call this the minimum version I'd start with because they were finally updatable and could have both columnstore and rowstore indexes on the same table. This grid has a great comparison of what changed with columnstore over the years.
• You need query plan monitoring, and you can't afford a third party tool – because Query Store gives you some pretty cool capabilities. People aren't using it as much as I'd like. If I took a full time DBA job again tomorrow, this (and PowerShell) would be the two skills I'd probably pick up.

You should consider SQL Server 2017 if…

• You're willing to apply patches every 30-60 days – because on newer releases like this, the patches are coming fast and furious, and they fix some pretty significant issues, and it's going to be a while before 2019 comes out and 2017's patches slow down. (Remember, there are no more Service Packs, just Cumulative Updates.)
• You have a zero-RPO goal and financial risks – because 2017 added a new minimum commit replica setting on AGs that will let you guarantee commits were received by multiple replicas
• You want easier future upgrades – because starting with 2017, you can have a Distributed Availability Group with different versions of SQL Server in it. DAGs aren't too robust or well-documented today, but I like the idea of this as a down payment on easier upgrades when you upgrade down the road. (Prior to this, AG version upgrades are absolutely terrible, and you're often better off building a new cluster and migrating over to it.)
• You need high performance columnstore queries – because we got a lot of cool stuff for batch mode execution plans.
• You're dead-set on running SQL Server on Linux – but seriously, go through the release notes and click on every Cumulative Update to read the bugs that were fixed. Some of the clustering bugs have really made my eyebrows raise.
• You're dead-set on doing machine learning & R in SQL Server – I know it's trendy for data folks to do this, but remember, you're spending $2,000 to $7,000 per core for SQL Server licensing to do this.

Yeah, I know, I didn't put "you want an extremely well-known, well-documented product" here in 2017, but it's not because the product is bad. It's just relatively new compared to 2012/2014/2016, and it's way, way harder to find great off-the-shelf training on things like Distributed Availability Groups or SQL Server on Linux, or hire people who know how to use those.

They're not bad features. They're great features – they're just earlier on the adoption curve right now, so getting great best practices is harder, that's all. Not impossible, just harder.

You should consider SQL Server 2019 if…

• You want as much support lifespan as possible – because it's supported until 2030. Hot diggety, wouldn't it be nice to not reinstall SQL Server for an entire decade?
• You're willing to apply patches every 30-60 days – because on newer releases like this, the patches are coming fast and furious, and they fix some pretty significant issues. (Remember, there are no more Service Packs, just Cumulative Updates.)
• You don't mind undocumented patches – Microsoft stopped documenting the contents of their patches, then got even worse, and as of 2021, they're even shipping updates with new undocumented features. That makes me pretty uncomfortable for mission-critical production environments.
• You're comfortable learning via experimentation, not documentation – because as you get to these cutting edge features below, your experimentation & learning time goes up, because there are WAY less established industry best practices around the below stuff.
• You're good at load & performance testing – because 2019 adds a lot of cool performance features when you enable 2019 compatibility mode, but it also makes big changes in your existing execution plans. Just to pick a number, say 99% of your queries go faster, but 1% go slower. Do you know which 1% they are, and what you're going to do to mitigate their performance reductions? You can't just test your slow queries on 2019: you've also gotta test your currently-fast queries to make sure they don't slow down unacceptably.
• You heavily rely on user-defined functions – because 2019 can dramatically speed those up, although you need to do a lot of testing there, and be aware that Microsoft has walked back a lot of the improvements.
• You rely heavily on table variables, and you can change the code – those are getting better too.

Microsoft brought some new technology bets to the table: Big Data Clusters, high availability in containers, and Java support. However, I just can't make an argument for doing a new SQL Server deployment today (December 2019) just to get those technologies. If your database environment requires (not desires) those features, then you should already be running 2019 in your test labs – but you're probably already running other versions of SQL Server in production for your other database needs.

Is it supposed to smell this bad?

So what's the right answer?

When I look at that list today, SQL Server 2017 makes a pretty compelling case for most folks. It's a good balance of new features, stability, and long shelf life. In most shops, where folks are overworked and can't upgrade every server every year, I can see installing 2017 today, and then seeing how 2019's release goes, and planning for my 2019 deployments in the year 2021.

How to Track Database Changes in Sql Server 2008 R2

Source: https://www.brentozar.com/archive/2019/01/which-version-of-sql-server-should-you-use/

Share this post