T-SQL Tuesday #90 - The need for database feature detection during deployment
For this T-SQL Tuesday, I’d like to hear about your thoughts or experiences with database deployments. […] join in by publishing a short post about database deployments. If you develop or deploy database changes, I want to hear about it.
I’m going to blog about why you need to care about feature detection in SQL Server.
Let’s imagine that you are developing a product and that you use SQL Server as the database backend. You support multiple versions of SQL Server; if you have sold your product to customers over the last few years, you probably support the versions still in mainstream support (currently 2012, 2014 and 2016) as well as Azure SQL Database. Your customers typically run Standard edition, with an occasional enterprise edition customer, so you stick with using only features that are available in SQL Server 2012 standard edition.
But now your junior DBA comes back from a SQL Saturday conference, where she learned about the Temporal feature in SQL Server 2016 (and Azure SQL database), and you realize that you could use this to build a killer-feature in the product. Or she tells you that columnstore indexes are available in standard edition starting from SQL Server 2016 service pack 1 (and Azure SQL database), and that you could use this feature to drastically improve performance on the analytical part of the product.
There are at least two problems you need to solve here: a product marketing problem and a technical deployment problem.
From a product marketing problem point of view, how do you deal with the fact that using a certain database feature (eg. Temporal) to deliver a killer feature in the product only works if the customer is running on a database supporting said database feature? You definitely don’t want to implement temporal support on SQL Server 2012 and 2014 yourself. But it is also a shame to wait until the feature is available in all supported versions (2012, 2014 and 2016). Personally, I try to mimic what web developers do regarding available functionality in web browsers, where you try to “degrade gracefully”. If a given feature is available, the user gets a richer experience. To be able to do this on the database level, the application must be able to do (database) feature detection at runtime (or startup time). If the database feature does not change the application base-schema (like utilizing non-clustered columnstore indexes or table compression), then you can live with only doing (database) feature detection at install/upgrade time. And in this case, you do degrade gracefully in case the database feature is not available – things just run faster if you are on a newer version of the database.
From a technical deployment point of view, you will need to be able to do (database) feature detection at install/upgrade time. Dependent on the deployment tool/method you use, you can now deploy the new version of the database schema.
So, how do you do feature detection on SQL Server? Well, it depends. For most features, you need to use the function SERVERPROPERTY with the parameter ProductVersion and then parse the result. Then you need to create the logic to map (version, edition, service pack) to features.
I created a few examples on GitHub to illustrate how:
For a few newer features like in-memory OLTP, you can query the function SERVERPROPERTY with the parameter IsXTPSupported directly:
1= Server supports In-Memory OLTP.
0= Server does not supports In-Memory OLTP.
NULL = Input is not valid, an error, or not applicable.
Starting from SQL Server 2016 service pack 1, most features are available in standard (and even express) edition. Therefore, the issue around feature detection (both install/upgrade-time and runtime) is getting more and more important.
That’s it for this months T-SQL Tuesday. Thanks for reading along. KTHXBYE
Read more about T-SQL Tuesday #090 here:
Check out the documentation for the SERVERPROPERTY function here: