Moving TFS from SQL Enterprise to Standard

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.

SQL Developer 버전은 기본적으로 Enterprise Edition과 동일하다는 의미임 (사용상의 제약만 있는 라이선스만 다를뿐....)

때문에 다음과 같은 Enterprise Edition 만이 제공하는 기능때문에 Standard 버전으로는 다운그레이드에 제약이 생김

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.

그중 한가지 기능이 다음과 같은 에러로 인해 다른 버전으로 migration이 안됨, 그것이 바로 page compression임

"개체 <Table Name>’ 의 일부 또는 전체가 데이터 압축 또는 vardecimal 저장소 형식으로 활성화되어 있어 이 버전의 SQL Server에서 데이터베이스 '<TFS Database name>'을(를) 시작할 수 없습니다. 데이터 압축 및 vardecimal 저장소 형식은 SQL Server Enterprise Edition에서만 지원됩니다.
데이터베이스 '<TFS Database name>' 기능 중 일부를 현재 버전의 SQL Server에서 사용할 수 없으므로 해당 데이터베이스를 시작할 수 없습니다."

One little trick that I have picked up is running the following stored procedure BEFORE you create the backup of the database:

다음의 stored procedure 를 데이터베이스 백업전에 수행함으로써 해결할 수 있음

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).

위 명령어를 각각의 관련된 데이터베이스와 테이블에 수행함으로써 compression 기능을 off 할 수 있다

Hope this helps

Disabling SQL Server Data Compression in TFS Databases

Summary

Team Foundation Server has been designed to make use of SQL Enterprise Edition features such as page compression which are not available in other editions of SQL Server. When moving one or more Team Foundation Server databases from an Enterprise Edition of SQL Server to a non-Enterprise Edition of SQL Server (as part of a collection detach/attach operation, for example) it is necessary to disable that compression.

To disable compression on a Team Foundation Server database you can execute [dbo].[prc_EnablePrefixCompression] against it. This stored procedure has a parameter, @online, which should be set to true if you want to disable compression while you continue using the collection database through your Team Foundation Server deployment, but can be set to false otherwise in order to speed up the operation. In either case, the steps to execute this stored procedure will be:


1. Launch SQL Server Management Studio

2. Locate the Team Foundation Server database which will be moved. Right click on the database and select New Query.

3. Type either: 

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

or 

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

Note Depending on whether you plan to continue using the database while disabling compression or not.


4. Run (!Execute) the query and verify success under messages

5. Repeat steps 1 through 4 for all required databases which will be moved.



Disabling compression will require additional disk space. The below query, which can be executed using the same steps as above, will provide you an estimate about the amount of additional disk space that will be required after disabling compression.
 

select sum(used_page_count) * 8 * 2 /1024.0
from sys.partitions p
join sys.dm_db_partition_stats s
on s.partition_id = p.partition_id
and s.object_id = p.object_id
and s.index_id = p.index_id 
where p.data_compression_desc = 'page'

Note:
1. The size returned by the above query is in Megabytes(MB).
2. It is advisable to run this query against each Team Foundation Server database before disabling data compression, and then to ensure that enough disk space will be available before actually disabling compression.