title: How do I unit test a SSDT project which uses sql server enterprise features against localdb?
body: <p>I have a SSDT project set up in VS which uses <code>SQL Server Enterprise Edition</code> features (e.g. data compression).</p>
  
  <p>I'd like to be able to deploy this project to <code>localdb</code>, to be able to run unit tests against the database locally, before deploying to the CI database. However, <code>localdb</code> is an instance of <code>SQL Server Express Edition</code> which doesn't support data compression.</p>
  
  <p>The error I get when I attempt to deploy is: </p>
  
  <blockquote>
    <p>SQL72014 .Net SqlClient Data Provider: Cannot enable compression for
    object 'FooTable'. Only SQL Server Enterprise Edition supports
    compression.</p>
  </blockquote>
  
  <p>Potential solution #1: Shift all the data compression SQL to a <code>Post Deploy Script</code>, which is not run when deploying to <code>localdb</code>. This isn't ideal as the option would be hidden in source control, and wouldn't show in schema compare etc. </p>
  
  <p>Potential solution #2: Replace the problem SQL with a <code>SQLCMD Variable</code> e.g. <code>$(WithPageCompression)</code> which will have empty value when deployed to <code>localdb</code> and the value <code>WITH (DATA_COMPRESSION = PAGE)</code> when deployed to the real database. This is an improvement on the above, but will become increasingly unwieldy as more options are set within the <code>WITH</code>.</p>
  
  <p>Potential solution #3: Install a local instance of SQL Server on each developers machine. It looks like the <a href="https://www.microsoft.com/en-us/sql-server/sql-server-editions-developers" rel="nofollow noreferrer">Developer Edition</a> would do the job (and is now free). It's looking like this might be the best solution and would come with other benefits, but the overhead of installing, configuring and maintaining this on the organisation's infrastructure makes this a pain. </p>
  
  <p>What other options do I have?</p>
  
  <p>Thanks</p>