Tuesday, February 26, 2013

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

6 comments:

  1. I was looking for info about integrate SQL Server 2012 Developer edition with TFS 2013. In this post, you comments that it can be possible when all is on dev environment. Will I need to disable specific features to migrate to any edition of sql server engine in order to be compatible, right?

    In MSDN it says TFS 2013 is compatible with sql server Enterprise/Standard/Express edition. Could I integrated them, right?

    Thanks in advance

    ReplyDelete
  2. Could I integrate SQL Server 2012 Developer edition with TFS 2013. I read that TFS 2013 needs sql server enterprise/standard/Express to works. In dev environment, Could I make that integration and migrate with enterprise features disabled when need production env?

    Thanks in advance

    ReplyDelete
    Replies
    1. Hi,

      SQL Dev edition is basically SQL enterprise, but with license restrictions prohibiting you from using it in any kind of production environment.
      You can use Dev edition with TFS, but only to test with and in non production scenarios.

      To move from SQL Dev to SQL enterprise should not be an issue, but if you are downgrading you will need to follow the guidance in this post.

      Hope this helps.

      Delete
    2. Hi, first of all. Thanks for a good post. This jungle of licensing and what is no-production and what is a production environment, has always been a really headache for me.

      We have a Testlab with both dev and test in one place. We're using TFS to check in, check out code, register bugs etc. This TFS solution is inside the Testlab infrastructure.

      Will you call our TFS a production or non-production than?

      BR,
      Dang

      Delete
    3. Hi,
      As a rule of thumb, if you can delete your TFS databases without any concerns you can consider it a test / dev environment.
      Outside of that - even if TFS is used for your development, it should be considered a production system! After all - it is very important IP that is contained in it.

      Delete
    4. Hi, thanks for clearing this up for me.

      Delete

Note: Only a member of this blog may post a comment.