Conditionally apply data compression depending on edition

Question

Hi,

I have a database which is deployed across a lot of servers which are a variety of SQL Server editions (Standard and Enterprise mostly). I would like to apply data compression to some of the tables in my project but clearly Standard edition does not support this so I get an error when I try to publish to those servers. Is there a way I can apply the table to my database model but only have it applied based on the edition of the target?

I realise I can add manual steps to my post-deploy script but this will be a bit clunky.

thanks,

Answer 1

Hi Ben,

Unfortunately, SSDT doesn't allow for targeting of specific editions of SQL Server.  It's a request that we've heard a lot and it's on our radar-- thanks for letting us know that it would be valuable to you.  As you mentioned, using pre/post deployments scripts in the best workaround.

-Janet Yeilding

Answer 2

Hi Janet,

Thanks for confirming. I suppose the ideal feature would be a Publish option to 'Apply schema changes/features only where supported by target edition' although I realise that my use-case is far from a typical SSDT project in that it is deployed to many different versions and editions of SQL servers.

Ben

Answer 3

Janet, do you know when this feature could be included in SSDT? I have a similar situation with our project, that must support SQL Standard and Enterprise because of use of Compression and Partitions, and I'd like to control it via conditionals. 

As a workaround, postdeploy scripts it not useful, because it detects changes between the db and the dacpac. The only thing that came to my mind was post-processing the sqls affected with Enterprise-only sentences, and trim that code, so that a new compilation could generate a standard edition dacpac. I created this with a script used when generating the deployment packages, generating a dacpac for std and another for enterprise.

Thank you very much

Daniel Alvarez

---

Daniel Alvarez [e-mail]