How to disable SQL Server Enterprise Edition's data compression in preparation for migrating to a lower version of SQL

  • Article

This has been probably one of the most common issues I have come across in the past as it relates to Tfs data migration and or upgrade. 

Scenario #1: You may be upgrading Tfs on new hardware and don't have the same version of SQL Server Enterprise in production.
Scenario #2: You have done your proof of concept in a testing environment with a Enterprise edition SQL Server, and now need to move to production where you only have SQL Server Standard Edition

Whichever the scenario, here is a quick solution that MUST be executed prior to the database backup, copy, restore steps.

Run the following command against the SQL Server Enterprise instance.

 EXEC [dbo].[prc_EnablePrefixCompression] @online = 0, @disable = 1

 

Please do not hesitate to share you experience.

Cheers!