Migrating TFS from SQL Server Enterprise to Standard can cause problems due to compression


When migrating a Team Foundation Server from SQL Server Enterprise to Standard , you might run into this error:

Restore Failed For Server ‘<Servername>’, (Microsoít.SqlServer.SmoExtended)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer ,Connectionlnlo)
Database ‘<TFS Database name> cannot be started in this edition of SQL Server because part or all of object tbl_Branch’ is enabled with data compression or vardecimal storage Format. Data compression and vardecimal storage Format are only supported on SQL Server Enterprise Edition.

Database ‘<TFS Database name>’ cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

The error message seems obvious enough, but the question is, how exactly do you proceed? For example, one of the things you would need to find out is which objects have compression enabled on them(yeah, TFS enables compression on some objects in its databases) , and how to get rid of it, so the migration can proceed. Here are the steps:

    1. Run the following query in each TFS database to determine whether there are objects which have compression enabled:
    2. select so.name,so.type,so.type_desc,sp.data_compression,sp.data_compression_desc from sys.partitions sp
      inner join sys.objects so
      on (so.object_id=sp.object_id)
      where sp.data_compression!=0

    3. If there are objects listed in the output of the query, then the next step is to disable the compression on the objects and their indexes. I actually ended up writing a small script for this(see attachment “Disable Compression on TFS DB’s.sql”). As always, this script does not come with any guarantees. Please do test it thoroughly before running on your production environment. You will need to run this script in the context of each of the TFS databases.

    After this, you should be good to proceed with the migration. If you face any issues when trying to disable the compression, please do not hesitate to call Microsoft for support.

    Hope this helps. Do let me know if you have any feedback, suggestions or comments. Thanks.

    Disable Compression on TFS DB's.sql

    Comments (18)

    1. Hi

      I am new to Widnows/SQL Server cluster, and was researching answers to my doubts for my below question.

      When we have 2 node Failover cluster, when the standby node got restarted or down something like that, do the SQL server services will be restarted on the cluster eventhough the active node is fine and serving data.

      Thank you

    2. Hi vannkl, thanks for showing interest in the blog. Though your question is not related to the blog post, let me answer it in one word – no. In case of a 2 node cluster, even if the passive node crashes, the SQL Services on the active node should not be affected.

      In the future, please post such questions on MSDN forums:

      social.msdn.microsoft.com/…/home

      Thanks.

    3. Cengiz Poyraz says:

      When I run the script, I encountered the error "Cannot find the object "ifts_comp_fragment_217767833_873" because it does not exist or you do not have permissions.".

      What may cause of it?

    4. Hi Cengiz,

      Thanks for showing interest in the blog. Please check and make sure you're logged with an account that has the sysadmin fixed server role assigned to it. If that is indeed the case, the error message may also indicate corruption of some sort in the db. Please run a checkdb against the concerned database, and revert back with the results.

      Hope this helps.

    5. Marvel Mayfield says:

      Thanks for the info- I just ran across this issue last weekend upgrading my QA environment. Does Microsoft support uncompressing TFS tables?

    6. HarshDeep_Singh says:

      Hi Marvel…thanks for appreciating. I think it would be best if you post your question on the TFS forums on MSDN. Thanks.

    7. Maciej Świętochowski says:

      I had the same issue about not being able to decompress ifts_comp_fragment_NUMBER_NUMBER, a few of them. I was sysadmin for sure, running SSMS with elevated permissions. Those objects are some internal tables and I wasn't able to decompress them. BUT that didn't matter as SQL Server standard happily restored backup created.

    8. Thank you says:

      Thank you, HarshDeep_Singh, your information was spot on.  I have spent two days trying to troubleshoot this migration issue of moving databases from SQL Ent to SQL Std.  It worked.

    9. HarshDeep_Singh says:

      Hi Maciej,

      The issue is specific to the scenario where you use SQL Enterprise evaluation and set up TFS on the same instance. Can you please confirm if that was the case?

      Thanks,

      Harsh

    10. HarshDeep_Singh says:

      Thanks a lot "Thank you". Glad to know it helped.

    11. Roman Mueller says:

      You are a super star and just saved me a couple hours of research time.

      I only just recently moved my TFS databases to a temporary SQL Server instance, before the permanent one was ready. I didn't realize that my temporary one was an Enterprise Edition and most of all, that TFS would turn on compression. The savings in table space are completely ridiculous. In one case it was 50 kilobytes……

      Anyway, thanks a lot!

    12. Hemanshu says:

      Hey , thanks it worked , good news it even Microsoft referring this page 🙂  

    13. HarshDeep_Singh says:

      Thanks a lot Roman for your kind words. I'm glad to know the blog helped.

    14. HarshDeep_Singh says:

      Thanks a lot Hemanshu…I'm happy to know it worked for you, and that Microsoft support requested you to refer to this page. Cheers.

    15. Vincent Vancalbergh says:

      Just wanted to add my thank you to the line. You helped me with our migration.

    16. HarshDeep_Singh says:

      Thanks a lot Vincent for your kind words. I am glad my you found the blog useful.

    17. Keith Cole says:

      I tried downloading your Disable Compression script, but I got page cannot be displayed. Is there something wrong with the link?

    18. smaug says:

      Hey!! I need help with the script attached. cant open it