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:
Run the following query in each TFS database to determine whether there are objects which have compression enabled:
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!=0If 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
Anonymous
August 05, 2013
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 youAnonymous
August 05, 2013
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.Anonymous
September 10, 2013
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?Anonymous
September 13, 2013
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.Anonymous
February 09, 2014
Thanks for the info- I just ran across this issue last weekend upgrading my QA environment. Does Microsoft support uncompressing TFS tables?Anonymous
February 09, 2014
Hi Marvel...thanks for appreciating. I think it would be best if you post your question on the TFS forums on MSDN. Thanks.Anonymous
March 07, 2014
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.Anonymous
March 17, 2014
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.Anonymous
March 19, 2014
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, HarshAnonymous
March 19, 2014
Thanks a lot "Thank you". Glad to know it helped.Anonymous
May 13, 2014
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!Anonymous
July 24, 2014
Hey , thanks it worked , good news it even Microsoft referring this page :-)Anonymous
August 18, 2014
Thanks a lot Roman for your kind words. I'm glad to know the blog helped.Anonymous
August 18, 2014
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.Anonymous
December 28, 2014
The comment has been removedAnonymous
January 14, 2015
Thanks a lot Vincent for your kind words. I am glad my you found the blog useful.Anonymous
January 06, 2016
I tried downloading your Disable Compression script, but I got page cannot be displayed. Is there something wrong with the link?Anonymous
February 17, 2016
The comment has been removed