I have come across a couple of TFS installations that have been deployed using SQL Developer Edition recently. This is problematic when upgrading or migrating TFS to a “proper” production environment (SQL Developer Edition may not be run in production environments!)
Considering that TFS comes with a restricted SQL Standard Edition license, there should never be much of a reason to install TFS on Developer Edition.
Getting back to the problem… SQL Developer Edition is basically Enterprise Edition. This means that it comes with a lot of goodies that obviously are not included in Standard Edition. TFS will happily enable and use the following Enterprise Edition features:
- Online index operations
- Page compression
- Table and index partitioning
- Larger read - ahead buffering & Cube perspectives
The one that could cause problems when moving to different versions of SQL is the Page compression. Once you have page compression enabled on a database, you cannot just backup and restore the database to a Standard Edition instance. You will end up getting an error to the tune of:
Database ‘<TFS Database name> cannot be started in this edition of SQL Server because part or all of object ‘<Table Name>’ is enabled with data compression or vardecimal storage Format. Data compression and vardecimal storage Format are only supported on SQL Server Enterprise Edition.
One little trick that I have picked up is running the following stored procedure BEFORE you create the backup of the database:
EXEC [dbo].[prc_EnablePrefixCompression] @online = 1, @disable = 1
(see here for more detail)
This will disable the compression on the relevant tables and allow you to carry on with the migration. You would need to run this for each of the databases that you are intending to move across (including the Tfs_Configuration database if you are indeed doing a migration).
Hope this helps
Feel free to contact us if you need to perform any upgrades or migrations to TFS 2012
