locked
TFS 2010 Migration Issue - SQL Server Enterprise Required RRS feed

  • Question

  • We are in the process of moving the TFS environment to new hardware and we are running into a SQL version issue. We have a current TFS 2010 environment that is running on SQL Enterprise 2008. The new environment has a SQL 2008 Standard instance. When I backup the Project collection database from the enterprise instance and try to restore it on the Standard instance, I get the following error:

    Database 'TFS_ProjectCollection1' cannot be started in this edition of SQL Server because part or all of object 'tbl_PropertyValue' 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_ProjectCollection1' cannot be started because some of the database functionality is not available in the current edition of SQL Server. (Microsoft SQL Server, Error: 909)

    I don't want to install SQL enterprise for liscensing reasons. 

    Any ideas?

    Thanks in advance

    Monday, December 6, 2010 3:24 PM

Answers

  • Thanks Mattias for your kindly help.

    Hello nalthoff,

    Thanks for your post.

    We can’t restore a backup of a database which has compressed objects in sql2008 Standard edition because data compression is only supported in Enterprise Edition.

    For more information about the Features Supported by the Edition of SQL Server 2008, please see the following article:

    http://technet.microsoft.com/en-us/library/cc645993(SQL.100).aspx

    To deal with your issue, please follow the steps below:

    1). On an Enterprise Edition, we first need to identify and then remove the objects which have compression on the database.

    2). Run the script below on the database which is suspected to have compressed objects to get the details of the objects enabled for data compression:

    SELECT 

    SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName] 

    ,OBJECT_NAME(sys.objects.object_id) AS [ObjectName] 

    ,[rows] 

    ,[data_compression_desc] 

    ,[index_id] as [IndexID_on_Table]

    FROM sys.partitions 

    INNER JOIN sys.objects 

    ON sys.partitions.object_id = sys.objects.object_id 

    WHERE data_compression > 0 

    AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS' 

    ORDER BY SchemaName, ObjectName

    3). To check for vardecimalstorage format compression run the following command:

    SELECT OBJECTPROPERTY(OBJECT_ID(<object name(s) from above command output>),

          'TableHasVarDecimalStorageFormat') ;

    GO

    4). For each object that is reported by the above select query you need to disable the compression by using the following command

    ALTER INDEX ALL ON <TABLE NAME>

    REBUILD WITH (DATA_COMPRESSION = None);

    5). Re-run the script to check if any compression is still there

    6). If not back up the database in SQL2008 Enterprise and now the restore will succeed on a lower edition.

    Thanks,


    Vicky Song [MSFT]
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, December 7, 2010 9:30 AM
    Moderator

All replies

  • It seems this is an issue with SQL 2008 Enterprise features that were automatically enabled. Anyone had any luck downgrading SQL DB's from 2008 Enterprise to Standard?
    Tuesday, December 7, 2010 3:08 AM
  • You can switch of compression on tables with

    ALTER TABLE PartitionTable1 
    REBUILD PARTITION = ALL 
    WITH (DATA_COMPRESSION =NONE) ;
    GO
    
    
    That would hopefully solve your problem.

    Tuesday, December 7, 2010 4:00 AM
  • Thanks Mattias for your kindly help.

    Hello nalthoff,

    Thanks for your post.

    We can’t restore a backup of a database which has compressed objects in sql2008 Standard edition because data compression is only supported in Enterprise Edition.

    For more information about the Features Supported by the Edition of SQL Server 2008, please see the following article:

    http://technet.microsoft.com/en-us/library/cc645993(SQL.100).aspx

    To deal with your issue, please follow the steps below:

    1). On an Enterprise Edition, we first need to identify and then remove the objects which have compression on the database.

    2). Run the script below on the database which is suspected to have compressed objects to get the details of the objects enabled for data compression:

    SELECT 

    SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName] 

    ,OBJECT_NAME(sys.objects.object_id) AS [ObjectName] 

    ,[rows] 

    ,[data_compression_desc] 

    ,[index_id] as [IndexID_on_Table]

    FROM sys.partitions 

    INNER JOIN sys.objects 

    ON sys.partitions.object_id = sys.objects.object_id 

    WHERE data_compression > 0 

    AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS' 

    ORDER BY SchemaName, ObjectName

    3). To check for vardecimalstorage format compression run the following command:

    SELECT OBJECTPROPERTY(OBJECT_ID(<object name(s) from above command output>),

          'TableHasVarDecimalStorageFormat') ;

    GO

    4). For each object that is reported by the above select query you need to disable the compression by using the following command

    ALTER INDEX ALL ON <TABLE NAME>

    REBUILD WITH (DATA_COMPRESSION = None);

    5). Re-run the script to check if any compression is still there

    6). If not back up the database in SQL2008 Enterprise and now the restore will succeed on a lower edition.

    Thanks,


    Vicky Song [MSFT]
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, December 7, 2010 9:30 AM
    Moderator
  • Vicky Song - Thank you very much. This worked for me...

     

    Cheers - DV

    Tuesday, November 29, 2011 11:54 AM
  • Hi Vicky song,

    I came across this post when i encountered the same problem when trying to restore the DB from SQL server Developer edition to Sql Server standard edition. But the query that you have given does not return me any data.

    I ran this on the Source server (SQL Server DEV edition) from where i have backed up the data.

    SELECT 

    SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName] 

    ,OBJECT_NAME(sys.objects.object_id) AS [ObjectName] 

    ,[rows] 

    ,[data_compression_desc] 

    ,[index_id] as [IndexID_on_Table]

    FROM sys.partitions 

    INNER JOIN sys.objects 

    ON sys.partitions.object_id = sys.objects.object_id 

    WHERE data_compression > 0 

    AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS' 

    ORDER BY SchemaName, ObjectName

    Please let me know what could be wrong


    Renu

    Monday, February 13, 2012 10:44 AM
  • This helped me disable compression on two tables, but not Tfs_Configuration.dbo.tbl_PropertyValue so I still can't migrate.

    UPDATE: I finally discovered through more googling and investigating that it was an index causing my problems and I COULD NOT disable compression on it. I had to drop and recreate the index to get rid of compression. Feels like a bug to me. For the record, the index was [Tfs_Configuration].[dbo].[tbl_PropertyValue].[IX_tbl_PropertyValue_InternalKindId_PropertyId]

    • Edited by Michael_R. _ Thursday, June 7, 2012 6:05 AM New info
    Thursday, June 7, 2012 5:46 AM
  • Vicky Song,

    Parabéns pelo post, felizmente existem membros ativos na comunidade que compartilham estas informações.

    Atenciosamente.

    Tuesday, July 17, 2012 1:24 PM
  • how about partitioning buddy?

    I'm having one heck of a time figuring out the easiest way to remove it, in the middle of a restore, or else I'd talk more :)

    Friday, July 20, 2012 4:31 PM
  • cannot alter table, because table does not exist.  Restore will not allow creation of database for first time use, due to compression error 909.  I am using SQL Server 2008 R2, ver(10.50.25)

    Is there an uncompressed version of this sample database:

    (ContosoBIdemoBAK.exe)

    Friday, December 6, 2013 6:46 PM
  • Hi,

    you need to execute prc_EnablePrefixCompression @online = 0, @disable = 1 on all databases before taking  backups. Here is a nice article about this:http://nakedalm.com/restore-tfs-backups-from-sql-enterprise-to-sql-express/

    This command can be executed on a live server - you will need to change @online parameter to 1 in this case.

    Keep in mind that collection database size can increase significantly when you disable page compression (up to 3x) - make sure you have a plenty of disk space.

    Thanks,

    --Vladimir


    Monday, December 9, 2013 12:00 AM
    Moderator