SQL Server 2019 Business cases

Reading Time: 5 minutes

In this post I want to cover SQL Server 2019 business cases. Because SQL Server 2019 is due for release later this year.

With this in mind, I’ve already started discussing upgrades and business cases with various people.

Some of you may already be planning your upgrades whereas others are just starting to think about it. So, I thought it would be good to share ten potential SQL Server 2019 business cases here.

Before I get started I should note that these business cases are based on everything that has been announced up until CTP 3. So, it does not cover anything announced after then.

For your convenience, I have also listed them below. So, you can click to go directly to one.

1. Polybase
2. Using Big Data Clusters
3. Fast Database Recovery
4. Always Encrypted improvements
5. Online Columnstore Index rebuilds
6. Secondary System Databases
7. Query Store supporting cursors
8. MDS replaces Silverlight with HTML
9. Windows Server 2008 support
10. Hello Azure Stack

1. Polybase

Currently, you might import large amounts of data from another relational database system like Oracle or MongoDB into your SQL Server database. If so, you can use Polybase instead.

Polybase has certainly been improved in SQL Server 2019. For instance, more data sources are now available. Plus, you can even install Polybase on SQL Server 2019 on Linux as well.

Instead of importing the data from the source you can use Polybase to connect to it remotely. Which means that you can run your queries against the source directly instead.

You can do this using Polybase by creating what is called external tables.

In reality, these tables are simply a link to the data at the remote source. However, you can query the table just like you would a normal database table.

As you can see there are definitely strong SQL Server 2019 business cases for using Polybase.

For example, if you import a large amount of data from an Oracle database every week for processing and it takes nearly a day this can save you a lot of time.

2. Using Big Data Clusters

Now, business cases for using Big Data Clusters can vary. For instance, take the above example with Polybase.

If you were currently importing a large amount of data every week from another database source and using it for Machine Learning or other purposes, than Big Data Clusters can help.

Especially since Big Data Clusters come with Spark integration.

Of course, it relies on kubernetes so there will be a learning curve. However, since it can potentially save you a lot of time by managing the data from one central point there are strong business cases for it.

For current learning resources for Big Data Clusters you can read a previous post in detail here.

3. Fast Database Recovery

If you currently have databases that take ages to failover or to come out of recovery, then a new SQL Server 2019 feature can help.

It’s called Accelerated Database Recovery and it uses a type of versioning in the transaction log which allows databases to recover faster.

So, if you have a VLDB that takes a long time to recover than this can help you. You can read more about it in detail here.

4. Always Encrypted improvements

Generally speaking, Always Encrypted is a very popular method to properly encrypt data in your SQL Server databases.

However, one issue with Always Encrypted now is that you cannot run set-based queries against the encrypted data directly within SQL Server.

Instead, you must get the application to decrypt the data and handle it there. As you can imagine this can introduce a performance issue. With this in mind, SQL Server 2019 introduces Always Encrypted with secure enclaves.

What this essentially does is allows you to have the encrypted data decrypted in a secure part of memory in your SQL Server. Which SQL Server can then use to process queries against.

As you can see, this security updates provides some very strong SQL Server 2019 business cases.

Now, there are some prerequisites to this that you can read in detail here.

5. Online Columnstore Index rebuilds

Currently, you might have a small maintenance window to restore columnstore indexes in because rebuilding them has caused you issues in the past.

However, you can now rebuild columnstore indexes online. In addition, you can force some indexes to do an online rebuild even if they are using an older compatibility level.

For further information about that you can read a previous post of mine in detail here.

6. Secondary System Databases

Some of you might be reading this heading and wondering what I am talking about.

Well Microsoft are intending to allow you to replicate some of the system databases using Always On Availability Groups.

As you can imagine, this can be a massive benefit if you have to failover to a secondary instance.

Because it potentially means you will no longer have to replicate various objects to your secondary instances. For example, logins and SQL Server Agent job details.

In truth, it does not appear to be available yet within the latest CTP. Just like a lot of others I am also very interested to know how they intend to do it.

In my head I’m guessing they will do this by providing the databases renamed and then swapping them over after a failover occurs.

However, this is pure speculation on my part and we will have to wait and see.

7. Query Store supporting cursors

Whenever I discuss the fact that Query Store supports certain types of cursors in SQL Server 2019 I usually get the same response. Which is that they should be converted to set based queries instead.

Of course, this is not always possible. For example, queries coming from an application provided by a third-party vendor.

However, forcing the plans for them in Query Store could indeed bring you some performance gains. Which can make your boss very happy.

8. MDS replaces Silverlight with HTML

Maybe you currently use MDS (Master Data Services). However, because the portal requires Silverlight to be installed you can’t use it. Which means you’re stuck with using the Excel plug-in instead.

However, in SQL Server 2019 the portal now uses HTML instead of Silverlight. Which means you can now use it without having to install a web browser plug-in.

You can read a previous post of mine about that in detail here.

Speaking of Master Data Services, did you know SQL Server 2019 CTP 3 now supports Azure SQL Database Managed Instances?

It makes me wonder if we will see a Platform as a Service offering for MDS eventually. You can read about the support for Azure SQL Database Managed Instances in detail here.

9. Windows Server 2008 support

Hopefully by now you know that SQL Server 2008 extended support ends on July 9th this year.

However, what some people may not realise is that extended support for Windows Server 2008 and Windows Server 2008 R2 both ends on January 14th.

With that in mind, it’s probably worth looking to migrate any SQL Servers that are running on those Operating Systems as soon as possible.

In truth, you do have various migration options. Some of which you can read in detail here.

10. Hello Azure Stack

Your company might currently be looking to introduce Azure Stack, or of course have it already. Which is something you can use to have a hybrid cloud based on Azure.

With this in mind, you might want to look to install SQL Server 2019 virtual machines on there. So that whatever you deploy in Azure Stack is supported for a long time.

However, what you need to realise is that you can also use Azure Kubernetes Service (AKS) in Azure Stack.

Which also opens the possibility of using Big Data Clusters within Azure Stack as well. Which means you can have Big Data Clusters hosted locally instead of in the cloud.

Of course, this also depends on whether you have a valid justification for using Big Data Clusters.

I once published a post about Azure Stack for the Data Professional. Which you can read about in detail here.

Final word

I hope these SQL Server 2019 business cases give you some idea of how you can look to justify an upgrade to SQL Server 2019. As well as an idea about some features you can use afterwards.

Of course, there are plenty of other business cases and you are more than welcome to comment about them.